on 04-30-2009 2:36 PM
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?
I just rearranged the code to take out some of the redundancy - does it fail in the same way? I can't think of any reason it should fail in the first place...
You say above that the query "works in series" - do you mean that if you include just the clause with the LIKE operator, it returns what you'd expect?
SELECT BI.InxFld01, BI.EvntDatTim
, BI.StEvDate, BI.StEvTime
, BI.StStatCd, BI.EventCode
, BI.UnitCd, BI.StatCd
, BI.SuspFlag, BI.UserId
, BI.StWrkType, BI.StQueueCd
, BI.StUnitCd,BI.crDatTim
, W08.CategoryCd
FROM FPWFI.AWDFPPWH.BI0002 BI
, FPWFI.AWDBSPDB.W08U999S W08
WHERE BI.UnitCd = W08.UnitCd
AND BI.WrkType = W08.WrkType
AND BI.EvntDate = current date
AND BI.UnitCd IN ('FPILHKG', 'FPILIOM')
AND NOT BI.StatCd IN ('ENDED', 'RETURNHK', 'RETURNIOM')
AND ((BI.StStatCd <> BI.StatCd
AND BI.EventCode = 'BIUPDATEW')
OR ((BI.EventCode = 'BIUPDATEW'
OR BI.EventCode = 'BISUSPND')
AND BI.SuspFlag = 'Y'
AND ((BI.QueueCd like 'IOM%'
and BI.SuspFlag = 'Y')
OR (BI.StStatCd = BI.StatCd
AND ((BI.QueueCd = 'PROCESS'
and BI.StStatCd = 'PROCESS')
OR (BI.QueueCd = 'AUTH'
and BI.StStatCd = 'AUTHREQD')
OR (BI.QueueCd = 'IOMREFER'
and BI.StStatCd = 'IOMREFER')
OR (BI.QueueCd = 'TRUST'
and BI.StStatCd = 'TRUSTCASE')
)
)
)
)
)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
no, I'm referring to 'iseries' an app we have where we can run SQL directly against the database.
Basically we're just looking to add a piece of code to the existing SQL which asks for anything where QUEUECD starts with IOM and is followed by another three or four letters.
Shouldn't be too much to ask should it, but Crystal gets in a huff.
Hi
Yes the results were the same. I think the problem lies with the AS/400 only recognising certain functions within the commands in crystal, quite frustrating that it doesn't like LIKE! If only I could use the startswith function available within the main selection criteria!
Thanks for your help again.
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
Given upper cased and lower cased convention of the AND\OR [versus and\or], it appears there may be a right parenthesis missing before the portion of suspect text, as denoted by the first asterisk. Seems more likely however, that the casing consistency is just not being maintained, since the count of parentheses pair out.
and I see that the *s I used on the original code have been interpreted as formatting in the thread - so it's the code in bold that I'm referring to now.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.