[sqr-users] RE: sqr-users Digest, Vol 36, Issue 16

["Barilleaux, Rebecca" <Rebecca.Barilleaux@anthem.com>]

I can confirm that CASE is a valid sql statement, and that you can
include parenthesis around the CASE statement, because I use both
successfully. I'm using Hyperion SQR Server 8.3.1, running against
Sybase 12.5.  The following example works just fine for me:

begin-select
isnull(ltrim(rtrim(STAT_BUSINESS_DIV)),'Missing Information') (+1,1)
isnull(ltrim(rtrim(STAT_HSA)),'Missing Information') (,10)
isnull(ltrim(rtrim(ALL_FSU_MCTR_DESC)),REPT.MB_MCTR_DESC) (,20)
GRGR_ID (,60)
GRGR_NAME (,70)
(Case HSA_LKUP_DESCRIPTION
      When 'Cincinnati'        then 'Ohio'
      When 'Cleveland'         then 'Ohio'
      When 'Columbus'          then 'Ohio'
      When 'Dayton'            then 'Ohio'
      When 'Evansville'        then 'Indiana'
      When 'Ft. Wayne'         then 'Indiana'
      When 'Indianapolis'      then 'Indiana'
      When 'Northwest Indiana' then 'Indiana'
      When 'Lexington'         then 'Kentucky'
      When 'Louisville'        then 'Kentucky'
      When 'Western Kentucky'  then 'Kentucky'
      When 'Missouri Southeast' then 'Missouri'
      When 'Missouri Central'   then 'Missouri'
      When 'Missouri Southwest' then 'Missouri'
      When 'North East Wisconsin' then 'Wisconsin'
      When 'North West Wisconsin' then 'Wisconsin'
      When 'South East Wisconsin' then 'Wisconsin'
      When 'South West Wisconsin' then 'Wisconsin'
      Else 'Unspecified State'
      End) (,120)
FROM lkpenv02..LVT_3605_REPT_DESCR
WHERE GRGR_ID < '00000200'
end-select


However, at least in Sybase, the CASE statement doesn't support LIKE --
you must match on literal values.  Also, I think you've got your CASE
syntax wrong.  You need to specify the field you are evaluating just
once, immediately after the CASE, and then list the possible values on
each WHEN.  

Sybase 12.5 Syntax for CASE:
CASE value-expression
... WHEN [ constant | NULL ] THEN statement-list ...
... [ WHEN [ constant | NULL ] THEN statement-list ] ...
... ELSE statement-list
... END CASE

Oracle 9i Syntax for CASE:
CASE expression
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result END


Thanks,
Rebecca

>>> kane.marc@gmail.com 2/23/2007 12:39 PM >>>
When debugging the following procedure via SQR Workbench, I am
receiving the
following error.
My assumption is that in place of a CASE statement I should use an
"IF-Then-Else" statement.
However I am not sure of the correct syntax, any help would be
appreciated.

Here is the code segment:

!*****************************
Begin-Procedure Get_REBILL_METRICS
!*****************************


Begin-SELECT
COUNT(*)
CC_CL_CD
CC_TYPE_CD
 (CASE
   WHEN DESCR254 LIKE '%C%' THEN 'CS REQ'
   WHEN DESCR254 LIKE '%B%' THEN 'BILL SEG'
   WHEN DESCR254 LIKE '%R%' THEN 'REREAD'
   WHEN DESCR254 LIKE '%A%' THEN 'AMR' ELSE 'Other' END)
 (CASE
   WHEN DESCR254 LIKE '%FF%'     THEN 'FF Malfunction'
   WHEN DESCR254 LIKE '%thrs%'   THEN 'Threshold'
   WHEN DESCR254 LIKE '%thresh%' THEN 'Threshold'
   WHEN DESCR254 LIKE '%th%'     THEN 'Threshold'
   WHEN DESCR254 LIKE '%stuck%'  THEN 'Meter Stuck'
   WHEN DESCR254 LIKE '%stck%'   THEN 'Meter Stuck'
   WHEN DESCR254 LIKE '%ovr%'    THEN 'Over Estimate'
   WHEN DESCR254 LIKE '%tover%'  THEN 'Over Estimate'
   WHEN DESCR254 LIKE '%O/R%'    THEN 'Over Estimate'
   WHEN DESCR254 LIKE '%oest%'   THEN 'Over Estimate'
   WHEN DESCR254 LIKE '%und%'    THEN 'Under Estimate'
   WHEN DESCR254 LIKE '%under%'  THEN 'Under Estimate'
   WHEN DESCR254 LIKE '%U/R%'    THEN 'Under Estimate'
   WHEN DESCR254 LIKE '%uest%'   THEN 'Under Estimate' ELSE 'Other'
END)

                SHOW 'IN Get_REBILL_METRICS PROCEDURE'
                DO Get_PRINT_OUTPUT    !Programmed

FROM PS_CI_CC
WHERE CC_DT BETWEEN TO_DATE($start_dt, 'MM/DD/YYYY') AND
TO_DATE($end_dt,
'MM/DD/YYYY')
AND CC_CL_CD = 'BLNG'
AND CC_TYPE_CD IN ('OVR/UND ADJ' , 'THRES ADJ')
GROUP BY CC_CL_CD, CC_TYPE_CD, DESCR254, DESCR254, PER_ID
ORDER BY PER_ID ASC, CC_CL_CD ASC, CC_TYPE_CD ASC, DESCR254 ASC,
DESCR254
ASC


End-SELECT


!********************************
End-Procedure  !Get_BILL_CORRECTIONS_INFO    !Programed
!********************************
The error is
   (SQR 4105) Placement parameter incorrect.
(CASE

-- 
Thank you,
M. Kane
Reports Developer (Crystal Reports/SQR)
PeopleSoft CIS Production Support
GES/New York, NY
702.349.5107

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users



CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, 
is for the sole use of the intended recipient(s) and may contain confidential
and privileged information or otherwise protected by law.  Any unauthorized
review, use, disclosure or distribution is prohibited.  If you are not the
intended recipient, please contact the sender by reply e-mail and destroy
all copies of the original message.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users