Problem with SQL in formula

[Former Member]

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 · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

re: iseries -- headdesk Sorry, thought that was a typo.

Does my version work in iseries? If so, does Crystal like it?

(And you missed by one line with the asterisks: should be the LIKE line and the one below it, not above it )

Edited by: Garrett Fitzgerald on Apr 30, 2009 10:59 AM

[Former Member · Accepted solution]

no problem.

I can't test the code unfortunately as I'm working from home and can't connect to that database, and just found my colleague is off til next week. Will try to get someone else to have a go with it, if not will have to wait til then.

cheers

Keith

[Former Member · Accepted solution]

Hi

I am Keith's colleague with the SQL syntax problem. Thanks for your suggestion, unfortunately this still doesn't work.

Thanks again.

[Former Member · Accepted solution]

Does my version give the same results in iseries that yours does? If so, I can keep theorizing about that version -- if not, I need to go back and stare at the original some more.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[former_member584790 · Accepted solution]

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.

[Former Member]

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.