RE: [sqr-users] Using Case Statements in SQR

["Alexander, Steven" <Steven.Alexander@sanjoseca.gov>]

Will this work when you indent the line for the case statement?  Won't SQR
interpret the code as being outside the SQL statement?

-----Original Message-----
From: sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org
[mailto:sqr-users-bounces+steven.alexander=sanjoseca.gov@sqrug.org] On
Behalf Of Larry Roux
Sent: Friday, February 23, 2007 9:43 AM
To: kane.marc@gmail.com; sqr-users@sqrug.org
Subject: Re: [sqr-users] Using Case Statements in SQR

you need to add the vairable names to those types of statements...ie:

Begin-SELECT
COUNT(*)   &MYCOUNT
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) 
&CASERESULTS1
 (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)   &CASERESULTS2




Larry Roux
Information Systems
Syracuse University
lroux@syr.edu


>>> 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

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

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