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.
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.
Comment