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

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

Larry,

Just out of curiousity, I tested it out this morning -- even without the
print offsets, the program still works fine:

begin-select
isnull(ltrim(rtrim(REPT.STAT_BUSINESS_DIV)),'Missing Information') &BD
isnull(ltrim(rtrim(REPT.STAT_HSA)),'Missing Information') &HSA_ID
isnull(ltrim(rtrim(ALL_FSU_MCTR_DESC)),REPT.MB_MCTR_DESC) &FSU
REPT.GRGR_ID &GROUP_ID
REPT.GRGR_NAME &GROUP_NAME
(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) &HSA_DESC
  print &BD (+1,1)
  print &HSA_ID (,10)
  print &FSU (,20)
  print &GROUP_ID (,60)
  print &GROUP_NAME (,70)
  print &HSA_DESC (,120)
FROM lkpenv02..LVT_3605_REPT_DESCR REPT
WHERE REPT.GRGR_ID < '00000200'
end-select

Thanks,
Rebecca

-----Original Message-----
Date: Mon, 26 Feb 2007 09:35:36 -0500
From: "Larry Roux" <LRoux@syr.edu>
Subject: [sqr-users] RE: sqr-users Digest, Vol 36, Issue 16
To: <sqr-users@sqrug.org>
Message-ID: <s5e2aa07.080@gwia203.syr.edu>
Content-Type: text/plain; charset=US-ASCII

Interesting!  The difference here is that you have the print offsets
included in the select.  So, perhaps it knows the case is the next
statement rather than print offsets.  I wonder if this would work if you
removed those - I think SQR would then thing the (Case statement is the
print offsers for GRGR_NAME



Larry Roux
Information Systems
Syracuse University
lroux@syr.edu


>>> Rebecca.Barilleaux@anthem.com 2/26/2007 9:30 AM >>>
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



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