When I run the stored procedure in SQLPlus

  EXECUTE usp_RPT_TransactionList
@i_nodes = '10001 ',
@i_user = 'mradmin@external',
@i_user_type = 'E',
@i_clearcard = 'N',
@i_scheduler = 'N',
@i_basedate='2011/03/18'
,@i_rlsdate_token = 'FROMTO|20080101|20110303|'
,@i_paymenttype = 'MC'
,@i_currency = 'USD'
,@i_L2 = 'LAXAP / 33718';

It returns 153 rows as expected. And running the report in WebFOCUS, I get 0 rows. Is there an issue with WebFOCUS having 'LAXAP / 33718'
as an input parameter?

 
TABLE FILE USP_RPT_TRANSACTIONLIST
PRINT
TRANSACTION_ID
MERCHANT_ID
SITE_ID
WHERE ( @I_NODES EQ '10001 ' )
AND ( @I_USER EQ 'mradmin@external' )
AND ( @I_USER_TYPE EQ 'E' )
AND ( @I_SCHEDULER EQ  'N' )
IF @I_CLEARCARD EQ N
IF @I_BASEDATE EQ 2011/03/18
IF @I_RLSDATE_TOKEN EQ FROMTO|20080101|20110303|
IF @I_CURRENCY EQ USD
IF @I_L2 EQ 'LAXAP / 33718'
IF @I_PAYMENTTYPE EQ MC
ON TABLE HOLD AS TRANSLIST1 FORMAT ALPHA
 

This message has been edited. Last edited by: Kerry,

no.

but i would close the WHERE statement with a ";"

and try putting quotes around
FROMTO|20080101|20110303|

i would change all the IF statements to WHERE...

and start the code with

-SET &ECHO=ALL;

so you see what is done...errors maybe...

and end the code with

END

All tests against a string should have quotes around the test variable.

Turn on the client side SQL traces and look at what WebFOCUS is actually trying to pass to the DB engine.

It should show you exactly where the mistake is in your code or could point to an incorrect translation.

There is essentially no difference in what should be generated by using WHERE or IF.

The rules for using them however do differ .

A text string in a where MUST always be quoted otherwise it treats it as a column.
A text string in an IF only requires quoting if it contains a space.

With a compounded WHERE you should allways terminate with a ;

FROMTO|20080101|20110303| is not any type of WebFOCUS syntax that I've ever seen, it's an SQL call.

Code it using the correct WebFOCUS syntax FROM '20080101' TO '20110303' and check the traces to make sure it is translated correctly

quote:

FROMTO|20080101|20110303|

I would hazard a guess that WF is treating the pipe characters in the only way it knows how, and that is a soft concatenation symbol. Therefore the SQL would probably have @i_rlsdate_token being equal to FROMTO2008010120110303.

JG has already suggested where I would go next.

T

Tony because it's an IF not a WHERE I would suspect that it's actually passing it as a QUOTED string.

Not tested but in an IF I would expect it to be passed as is. In a WHERE I would expect to have to escape the
| by using || or even |||, have to try and remember how I've done it in the past.

IF does not handle expressions which is what a concaternation is. WHERE does.

However I expect the parser and translator have got their knickers well and truly in a twist over this request.