Passing parms to STRQMQRY in CL

[DaleJanus]

I have lots of old query 400 stuff that I am converting to QMQRY and / or sql. I am having trouble passing parms to the SQL I have created with QMQRY.


I am trying to select between two dates. The dates are 6 digits mmddyy. The following sql was created by converting a new QMQRY I created using the convert to SQL option 10.
It works correctly.

************************** Beginning of Data *********************************
SELECT
-- Columns
A.CBLID, A.CBLAD, a.cdate as realdate,
A.CLINE, A.CCV, A.CCUST, A.CTPC, A.CWGT
-- Tables
FROM "QS36F"/"LIBSBL" A
-- Row Selection
Where Right(Digits(CDATE), 2) concat Left(Digits(CDATE), 4) between
&datein and &dateout
AND (CRTYP = 1)
AND (CCV = 'V')
-- Sort Columns
ORDER BY A.CCUST, A.CBLAD
***************************** End of Data ************************************

When I run it from Query Manager, it asks me for the dates. I can enter them either as numbers 181001 or with quotes around the number '181001'
and it accepts both and runs correctly.


In my CL, I create &datein and &dateout by *tcat a 1 character parm containing a single quote before and after the date. DEBUG shows the string to be as I expect it .
For &datestr, I show '181001' and for &dateend I show '181231'. &datestr and &dateend are defined as *char, 8 long.


But when the CL runs this statement, it stops and asks to key in the date parameters.

STRQMQRY QMQRY(RBAUDIT2) OUTPUT(*PRINT) +
QMFORM(RBAUDIT1) SETVAR((DATEIN &DATESTR) +
(DATEOUT &DATEEND))


I have tried making the SETVAR names in the SQL both the same DATEIn &DATEIN but it still wants me to key in the date.


I could make good use of converting old query400 stuff if I could just figure out how to pass parms.

[Rocky]

Have you tried calling it without adding the quotes in the variable?

[DaleJanus]

yes, it still stops and asks to enter the parms. Here is the job log with no quotes added
************************************************** ********************************
6 > CALL RB_VNDBOL2 '123118'
7200 - STRQMQRY QMQRY(RBAUDIT2) OUTPUT(*PRINT) QMFORM(RBAUDIT1)
SETVAR((DATEIN '180901') (DATEOUT '181231'))
Type a value for variable "datein" and press Enter.
? 181001
Type a value for variable "dateout" and press Enter.
? 181231
************************************************** **********************************


here is the original job log with single quote as part of the parm
************************************************** ***********************************
6 > CALL RB_VNDBOLS '123118'
7200 - STRQMQRY QMQRY(RBAUDIT2) OUTPUT(*PRINT) QMFORM(RBAUDIT1)
SETVAR((DATEIN '''180901''') (DATEOUT '''181231'''))
Type a value for variable "datein" and press Enter.
? 181001
Type a value for variable "dateout" and press Enter.
? 181231
************************************************** **************************************

[Brian Rusch]

This is just a guess, but if the variable names are case sensitive then try making the variable names upper case in the SQL statement (... between &DATEIN and &DATEOUT ...).

[DaleJanus]

OMG!!! case sensitive!

changed the SQL to upper case parm names and it worked.

Thank you thank you thank you!

[Jim_IT]

Dale,

I will give you the same advice which I give all of our developers.

Unless there is an index which exactly matches the expression used in the where predicate, i.e.

create index LIBSBLIDX on QS36F.LIBSBL (Right(Digits(CDATE), 2) concat Left(Digits(CDATE), 4))

The query optimizer cannot use an index to access the table. It is better to conform the literal value of the parameter to match the format of the comparison field. This might not matter much for some files but the tables in our applications can have billions of rows and all indexes are carefully reviewed for their need and usage.

Select
*
FROM "QS36F"/"LIBSBL" A
Where CDATE between &datein and &dateout

where variables are formatted in MMDDYY format instead of YYMMDD format.

Jim