I have this OLD code which will convert any query to QSQLSRC in whichever library you choose.
You'll have to create QSQLSRC in a library (QGPL).
Perhaps be a bit more specific on the DSPOBJD OBJ parameter.
Then run the program and copy the source member of the query you are working with.
You may have to adjust for lib/file to lib.file. You'll get the gist when you copy out the Source member.
Run the SQL via either STRSQL (lib/file) or ACS "Run SQL Scripts" (lib.file).
/*******************************************************************/
200 /* Build Query References File */
300 /* Queries In *ALL Libraries to output file QRYLIST/QGPL */
500 /*******************************************************************/
525 PGM
600 DCLF FILE(QADSPOBJ)
700 /*******************************************************************/
800 /* PRODUCE OUTPUT FILE OF ALL QUERY DEFINITIONS */
900 DSPOBJD OBJ(*ALL/*ALL) OBJTYPE(*QRYDFN *QMQRY) +
1000 OUTPUT(*OUTFILE) OUTFILE(QGPL/QRYLIST)
1100 /*******************************************************************/
1200 /* OVERRIDE QADSPOBJ - SYSTEM SUPPLIED OUTFILE FOR DSPOBJD COMMAND */
1300 /* TO QRY MGMT QUERY SOURCE */
1400 /*******************************************************************/
1500 OVRDBF FILE(QADSPOBJ) TOFILE(QGPL/QRYLIST)
1600 LOOP1:
1700 RCVF
1800 MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(LOOP2))
1900 /*******************************************************************/
2000 /* FOR EACH QUERY IN LIST (OUTFILE), RETRIEVE DEFINITION AS QUERY */
2100 /* MGMT QUERY SOURCE */
2200 /*******************************************************************/
2300 RTVQMQRY QMQRY(&ODLBNM/&ODOBNM) +
2400 SRCFILE(QGPL/QSQLSRC) ALWQRYDFN(*YES)
2500 GOTO CMDLBL(LOOP1)
2600 LOOP2:
2700 DLTOVR FILE(*ALL)
2800 ENDPGM
2900 /*******************************************************************/
Very Respectfully,
Michael Mayer
IBM i Support / System Admin.
IT Operations.
The Florida Bar
651 E. Jefferson St
Tallahassee, Florida 32399-2300
mmayer@xxxxxxxxxxxxxx
https://www.floridabar.org
Office: 850.561.5761
Cell: 518.641.8906
------------------------------------------------------------------------------------------------------
Today's Topics:
1. wrkqry vs sql result difference (Smith, Mike)
----------------------------------------------------------------------
message: 1
date: Tue, 22 Sep 2020 13:03:43 +0000
from: "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
subject: wrkqry vs sql result difference
I have a query that I'm trying to convert to sql.
The query has a join file(2) matched records with primary file Sorted by area and account
There are a dozen or so records not in the secondary file however, when the results display I get something like this
AREA Account
' ' 1
' ' 2
' ' 3
AREA1 10
AREA1 11
When I convert this to SQL I don't' get the same results, because of the NULL values, but I can't figure out how to get the null converted to a space in the query My results look like this
AREA Account
' ' 2
AREA1 10
AREA1 11
- 1
- 3
I have tried using select ifnull(ara, ' ') as area
And coalesce(ara, ' ') as area
But no luck
My query looks like this
Select ara, acct, name
From file1 left outer join file2
On acct = f2acct
Order by ara, acct
Thanks
MIke
________________________________
Please note: Florida has very broad public records laws. Many written communications to or from The Florida Bar regarding Bar business may be considered public records, which must be made available to anyone upon request. Your e-mail communications may therefore be subject to public disclosure.
midrange.com
