Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Problem with SQL in formula

Hi,

a colleague is trying to add a bit of SQL to an existing report which runs fine, and has sent me 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 (shouldn't make a difference but it is an AS/400!). 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 other ideas?

Former Member
Former Member replied

I think you're just using a SQL Command here: is that correct? Or are you trying to mix a SQL Command and the Record Selection Formula?

Will it let you go to the Database menu and Show SQL Query, or does it error before then? If it will, I'll be interested to hear if it's sending the same query you typed in.

Do you have an alternate method of connecting to the database, such as switching from ODBC to OLEDB, or are you stuck with the way you're doing it now?

Edited by: Garrett Fitzgerald on May 5, 2009 11:11 AM - rephrased question

Edited by: Garrett Fitzgerald on May 5, 2009 11:13 AM - added a followup

Edited by: Garrett Fitzgerald on May 5, 2009 11:14 AM - another followup

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question