RE: SCO

We are trying to add a bit of SQL to an existing report which runs fine, the following code. The added bits, which stop the code from running properly are the two lines starting and ending with *s (obviously they are not there in the Crystal report, I've added them so you can see the lines better).
 
SELECT "BI0002"."INXFLD01", "BI0002"."EVNTDATTIM", "BI0002"."STEVDATE", "BI0002"."STEVTIME", "BI0002"."STSTATCD", "BI0002"."EVENTCODE", "BI0002"."UNITCD", "BI0002"."STATCD", "BI0002"."SUSPFLAG", "BI0002"."USERID", "BI0002"."STWRKTYPE", "BI0002"."STQUEUECD", "BI0002"."STUNITCD","BI0002"."CRDATTIM" ,"W08U999S"."CATEGORYCD" FROM "FPWFI"."AWDFPPWH"."BI0002" "BI0002" , "FPWFI"."AWDBSPDB"."W08U999S" "W08U999S" WHERE "BI0002"."UNITCD" = "W08U999S"."UNITCD" AND "BI0002"."WRKTYPE" = "W08U999S"."WRKTYPE" AND ("BI0002"."UNITCD"='FPILHKG' OR "BI0002"."UNITCD"='FPILIOM') AND ("BI0002"."EVNTDATE"=current date ) AND NOT ("BI0002"."STATCD"='ENDED' OR "BI0002"."STATCD"='RETURNHK' OR "BI0002"."STATCD"='RETURNIOM') AND (("BI0002"."STSTATCD" "BI0002"."STATCD" AND "BI0002"."EVENTCODE"='BIUPDATEW' ) OR (("BI0002"."QUEUECD" = 'PROCESS' and "BI0002"."STSTATCD" = 'PROCESS' and "BI0002"."STATCD" = 'PROCESS' and "BI0002"."SUSPFLAG"='Y') *AND ("BI0002"."EVENTCODE"='BIUPDATEW' OR
"BI0002"."EVENTCODE"='BISUSPND'))* *OR (("BI0002"."QUEUECD" like 'IOM%' and "BI0002"."SUSPFLAG"='Y')* AND ("BI0002"."EVENTCODE"='BIUPDATEW' OR "BI0002"."EVENTCODE"='BISUSPND')) OR (("BI0002"."QUEUECD" = 'AUTH' and "BI0002"."STSTATCD" = 'AUTHREQD' and "BI0002"."STATCD" = 'AUTHREQD' and "BI0002"."SUSPFLAG"='Y') AND ("BI0002"."EVENTCODE"='BIUPDATEW' OR "BI0002"."EVENTCODE"='BISUSPND')) OR (("BI0002"."QUEUECD" = 'IOMREFER' and "BI0002"."STSTATCD" = 'IOMREFER' and "BI0002"."STATCD" = 'IOMREFER' and "BI0002"."SUSPFLAG"='Y') AND ("BI0002"."EVENTCODE"='BIUPDATEW' OR "BI0002"."EVENTCODE"='BISUSPND')) OR (("BI0002"."QUEUECD" = 'TRUST' and "BI0002"."STSTATCD" = 'TRUSTCASE' and "BI0002"."STATCD" = 'TRUSTCASE' and "BI0002"."SUSPFLAG"='Y') AND ("BI0002"."EVENTCODE"='BIUPDATEW' OR "BI0002"."EVENTCODE"='BISUSPND'))) This code is running against tables on an AS400 server, and runs fine in iseries, as do the variations ((SUBSTR("BI0002"."QUEUECD",1,3) = 'IOM' or
((LEFT("BI0002"."QUEUECD",3) = 'IOM%' and we have tried LIKE in upper case 
 
 When pasted back into the command, Crystal accepts it. Then when you try to refresh the report it tries for about 5 mins then comes up with Invalid Argument, click on ok, then Cannot retrieve data from database. Anyone have any ideas?

--- On Sun, 5/17/09, Neill Harper <neill.harper@xxxxxxxx> wrote:


From: Neill Harper <neill.harper@xxxxxxxx>
Subject: RE: SCO
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Date: Sunday, May 17, 2009, 6:36 PM


I'd be willing to try and help you out but I'd need to see some code.

Neill

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of sjl
Sent: 17 May 2009 19:14
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SCO

Batman (I mean Adam) -

No offense intended, but this is not an IBM Midrange-specific question.
I believe that you are more likely to get a response if you post your
question in a .NET forum.

- sjl


Adam West wrote:
  This all works but for a small problem -
  when I run the report I first get prompted for the "SCO" parameter
  before it loads the main parameter lists.
  Then I get prompted again for the Company parameter
  to filter in the record selection.
  Is there any way that I can use the value of the parameter
  obtained at sql command level?
  Hope i've made sense.