Adding records to workfile via SQL 1

[TracyV]

Ok, this is probably a really dumb question, but I cannot find any examples of reading one file via SQL and adding those records to a physical file in QTEMP. I am using these statements within an RPGIV program.

The SQL statement I am using to read the file is:

SELECT nnam, nadd1, nadd2, ncity, nstat, nsid FROM NAME1P WHERE nnam LIKE '%JOHNSON%' ORDER BY nnam

I want the results from the above SQL statement to be written to a temporary file called SEARCHPF in QTEMP.

Any suggestions would be greatly appreciated.

 

[Mercury2]

If QTEMP/SEARCHPF already exists, simply type
Insert into QTEMP/SEARCHPF
SELECT nnam, nadd1, nadd2, ncity, nstat, nsid FROM NAME1P WHERE nnam LIKE '%JOHNSON%' ORDER BY nnam

If QTEMP/SEARCHPF file does not exist, create QTEMP/SEARCHPF and copy selected data with only one single statement, i.e. CREATE TABLE QTEMP/SEARCHPF AS SELECT nnam, nadd1, nadd2, ncity, nstat, nsid FROM NAME1P WHERE nnam LIKE '%JOHNSON%' ORDER BY nnam

 

[TracyV]

I tried the insert into yesterday and kept gettig errors. This is the error I receive when trying to update the SEARCHPF file in QTEMP:


Insert into QTEMP/SEARCHPF
SELECT nnam, nadd1, nadd2, ncity, nstat, nsid FROM NAME1P WHERE nnam
LIKE '%JOHNSON%' ORDER BY nnam
Token '%JOHNSON%' was not valid. Valid tokens: < > = <> <= !< !> !=

 

[TracyV]

I also get the following error:

insert into qtemp/searchpf
SELECT NNAM FROM NAME1P WHERE NNAM LIKE '%JOHNSON%' ORDER BY NNAM
Value for column or variable NSID not compatible.

Prior to starting interactive SQL, i did a crtdupobj to create SEARCHPF from the NAME1P file

 

[Mercury2]

AFAIK your SQL statements seem wrong.
Could you pls show us your RPG IV source program or the SQL part ?

 

[TracyV]

I am trying to display a selected list of names from a name file. Say the user enters "%JOHNSON%". I then build the SQL statement and display all records that contain "JOHNSON". Simple enough. Now the user wants to do a position to WITHIN THE ORIGINAL SQL VIEW and position to with the "JOHNSON" records, not the entire name file. This is where I run into problems. How can I save the original view and position to within that original list. I've tried Create View, to create a table to access later in the program, but cannot get the syntax correct. I've tried to create a temporary file in QTEMP and insert the records into this file, again, problems with the syntax. Seems I cannot do a SELECT within the INSERT or CREATE VIEW.

Any help would be greatly appreciated!!!!!!!!!!!!!!

Below is the Screen.


NAME________________________________ ______ And/Or
________________________________ ______ And/Or
________________________________ ______ And/Or
________________________________ ______ And/Or




FCM3001FM cf e workstn
F sfile(sfl1:rrn1)
F infds(info)
FCobasl13 IF E K DISK
D NNAM S 1200A VARYING
D Select1 S 40A
D Select2 S 40A
D Select3 S 40A
D Select4 S 40A
* Parameter data structure
D cm3001ds ds
D Sql1 1 40
D Sql2 41 80
D Sql3 81 120
D Sql4 121 160
D AndOr1 161 163
D AndOr2 164 166
D AndOr3 167 169
D S1Sid 170 179 0
D S1Ssn 180 187 0
D S1Fid 188 196 0
D Cm3001cmd03 197 197 0
D SelectId 198 206 0
D Cm3001cmd11 207 207 0
D Cm3001cmd12 208 208 0
* CM3010 Data Structure
D CM3010ds DS
D Cm3010id 1 9 0 Inz
D Cmd01 10 10 0 Inz
D Cmd02 11 11 0 Inz
D Cmd03 12 12 0 Inz
D Cmd11 13 13 0 Inz
D Cmd12 14 14 0 Inz
*
* CM1016 Data Structure
D CM1016ds DS
D Pdesc 1 35
D Puser 36 45
D PositionTo ds
D Pos1 1 39
D Pos2 40 40
Dinfo ds
D cfkey 369 369
*
D Xidg UDS
D Xid 1 9
D Xnam 10 49
D Xpgmn 50 57
D SDS
D Pgm 1 10
D Dspst 244 253
D Libs 81 90
D Wuser 254 261
D Wuser2 254 263
* Constants for attention indicator byte
*
D AddName C const(X'31')
D Exit C const(X'33')
D Prompt C const(X'34')
D Previous C const(X'3C')
D Enter C const(X'F1')
D PageDown C const(X'F5')
D SubfilePage C Const(8)
D Quote C Const('''')
D Percent C Const('%')
D Space C Const(' ')
D SelectOne S 500A INZ('SELECT nnam,-
D nadd1, nadd2, nstat,-
D ncity, nstat, nsid-
D FROM NAME1P -
D WHERE')
D SelectLIKE S 4A INZ('LIKE')
D SelectEQUAL S 10A INZ('=')
D SelectGT S 10A INZ('>=')
D Search1 S 4A INZ(' ')
D Search2 S 4A INZ(' ')
D Search3 S 4A INZ(' ')
D Search4 S 4A INZ(' ')
D SelectTwo S 10A INZ('ORDER BY')
D OrderBy S 10A INZ('nnam')
D Statement S 500A INZ(' ')
D Lastrrn S Like(rrn1)
********************************************
* MAINLINE
********************************************
*
ExSr @Clear
ExSr Bldsql
ExSr prep
ExSr sflbld
*
DoU (cfkey = Exit)
Write fkey1
ExFmt sf1ctl
*
If (*In32 = *Off)
ExSr sflsel
Endif
C Select
*
C When Selectid <> *Zeros
C ExSr clean
C Return
*
C When (cfkey = AddName)
C Eval CM3010id = *Zeros
C Eval Cmd01 = 1
C Eval Cmd02 = 0
C Exsr TestUser
C If Pdesc = 'NO'
C Eval Errmsg = 'Not authorized
C Eval *IN99 = *On
C Write fkey1
C Endif
C Call 'CM3010'
C Parm CM3010ds
C Eval S1sid = Cm3010id
C Eval Selectid = Cm3010id
C Eval Cm3001cmd11 = Cmd11
C Eval Cm3001cmd12 = Cmd12
C If Cmd11 = 1
C ExSr clean
C Return
C Endif
*
C When cfkey = prompt
C ExSr sort
C ExSr clean
C ExSr prep
C ExSr sflbld
*
C When cfkey = Exit
C Eval Cm3001cmd03 = 1
C ExSr clean
C Eval *inlr = *on
C Return
*
C When cfkey = Previous
C Eval Cm3001cmd12 = 1
C ExSr clean
C Eval *inlr = *on
C Return
*
C When cfkey = PageDown
C ExSr Sflbld
*
C When PosName <> *Blanks
C Eval Pos1 = PosName
C Eval Pos2 = '%'
C Eval Sql1 = %Trimr(Pos1) + (Pos2)
C ExSr Clean
C ExSr @Clear
C ExSr Bldsql
C ExSr prep
C ExSr sflbld
*
C EndSl
C EndDo
*************************************************
* Prepare SQL cursor
*************************************************
*
C prep BegSr
*
* Clear the subfile
*
C Eval *in31 = *on
C Write sf1ctl
C Eval *in31 = *off
C Eval rrn1 = 0
C Eval Lastrrn = 0
C Eval Statement = *BLANKS
C
C Eval Statement = %TRIMR(SelectOne) + (Space) +
C %TRIMR(Select1) + (Space) +
C (AndOr1) + (Space) +
C %TRIMR(Select2) + (Space) +
C (AndOr2) + (Space) +
C %TRIMR(Select3) + (Space) +
C (AndOr3) + (Space) +
C %TRIMR(Select4) + (Space) +
C %TRIM(SelectTwo) + (Space) +
C %TRIM(OrderBy)
*
C/EXEC SQL
C+ PREPARE sel FROM :Statement
C/END-EXEC
*
C/EXEC SQL
C+ DECLARE MYCSR SCROLL CURSOR FOR SEL
C/END-EXEC
*
* Open the SQL cursor.
C
C/EXEC SQL
C+ OPEN MYCSR
C/END-EXEC
*
C EndSr
*********************************************************
* Clean up before exiting
*********************************************************
*
C Clean BegSr
*
* Close the SQL cursor after all processing is complete.
*
C/EXEC SQL
C+ CLOSE mycsr
C/END-EXEC
*
C Eval PositionTo = *Blanks
C Eval PosName = *Blanks
C EndSr
*
***************************************************
* Build the subfile
***************************************************
*
C Sflbld BegSr
C Eval Rrn1 = Lastrrn
C Do SubfilePage
*
C/EXEC SQL
C+ FETCH NEXT FROM mycsr
C+ INTO :nnam40, :nadd1, :nadd2,
C+ :ncity, :nstat, :nsid
C/END-EXEC
*
* *IN60 = *ON record is active, display in white.
* *IN60 = *OFF record is enactive, display in red.
C Nsid Chain Cobasl13
C If %Found
C Eval *IN60 = *ON
C Else
C Eval *IN60 = *OFF
C End
*
C If sqlcod = 0
C Eval rrn1 = rrn1 + 1
C Write sfl1
C Else
C Leave
C EndIf
*
C EndDo
*
C If rrn1 = 0
C Eval *in32 = *on
C Else
C Eval Lastrrn = rrn1
C EndIf
*
* A code of 100 means end of file.
*
C If Sqlcod = 100
C Eval *in90 = *on
C EndIf
*
C EndSr

**************************************************
* Selection in the subfile
**************************************************
*
C Sflsel BegSr
C
C Eval Selectid = *Zeros
C
C ReadC Sfl1
C Dow *in91 = *Off
C
C If Select <> *Blank
C Eval Selectid = Nsid
C Endif
C
C Clear Select
C Update Sfl1
C
C ReadC Sfl1
C
C Enddo
C EndSr
**************************************************
* SORT - prompt to select sort criteria
**************************************************
*
C Sort BegSr
*
C ExFmt window1
*
C Select
*
C When Tab1 <> *Blank
C MoveL(p) 'NSID' OrderBy

C Clear Tab1
*
C When Tab2 <> *Blank
C MoveL(p) 'NNAM' OrderBy
C Clear Tab2
C EndSl
*
C EndSr
***************************************************
* BLDSQL - Build SQL statements
***************************************************
C Bldsql BegSr
C*
C If PositionTo = *Blanks
C*
C Percent Scan Sql1
C If %Found

C Eval Search1 = SelectLIKE
C Else
C Eval Search1 = SelectEQUAL
C EndIf
C*
C Percent Scan Sql2
C If %Found
C Eval Search2 = SelectLIKE
C Else
C Eval Search2 = SelectEQUAL
C EndIf
C*
C Percent Scan Sql3
C If %Found
C Eval Search3 = SelectLIKE
C Else
C Eval Search3 = SelectEQUAL
C EndIf
C*
C Percent Scan Sql4
C If %Found
C Eval Search4 = SelectLIKE
C Else
C Eval Search4 = SelectEQUAL
C EndIf
C*
C Else
C Eval Search1 = SelectGT
C EndIf
C*
C If Sql1 <> *Blanks
C Eval Select1 = %TRIMR(Orderby) +
C (Space) + %TRIMR(Search1) +
C (Space) + %TRIMR(Quote) +
C %TRIMR(Sql1) + %TRIMR(Quote)
C EndIf
C*
C If Sql2 <> *Blanks
C Eval Select2 = %TRIMR(Orderby) +
C (Space) + %TRIMR(Search2) +
C (Space) + %TRIMR(Quote) +
C %TRIMR(Sql2) + %TRIMR(Quote)
C EndIf
C If Sql3 <> *Blanks
C Eval Select3 = %TRIMR(Orderby) +
C (Space) + %TRIMR(Search3) +
C (Space) + %TRIMR(Quote) +
C %TRIMR(Sql3) + %TRIMR(Quote)
C EndIf
C*
C If Sql4 <> *Blanks
C Eval Select4 = %TRIMR(Orderby) +
C (Space) + %TRIMR(Search4) +
C (Space) + %TRIMR(Quote) +
C %TRIMR(Sql4) + %TRIMR(Quote)
C EndIf
C*
C*
C EndSr
************************************************
* @CLEAR - Clear SCRN01 Entries and workfields
************************************************
C @Clear BegSr
C Eval Selectid = *Zeros
C Eval Statement = *Blanks
C Eval Search1 = *Blanks
C Eval Search2 = *Blanks
C Eval Search3 = *Blanks
C Eval Search4 = *Blanks
C Eval Select1 = *Blanks
C Eval Select2 = *Blanks
C Eval Select3 = *Blanks
C Eval Select4 = *Blanks
C EndSr
***************************************************
* TestUser - Test if user authorized to update/chan
***************************************************
C TestUser BegSr
C Eval Pdesc = *Blanks
C Eval Puser = Wuser2
C Call 'CM1016'
C Parm Cm1016ds
C EndSr
***************************************************
* INZSR - Initialize Routine
***************************************************
C *INZSR BegSr
C *Entry Plist
C Parm CM3001ds
C *Dtaara Define *LDA Xidg
C Out Xidg
C EndSr

 

[Mercury2]

I'd share my unique SQL query in two parts :

1/ Create view to select records only by name (%JOHNSON%) with an Execute Immediate request :
Execute immediate Create View Qtemp/MyView As Select ... Where NNAM Like '%JOHNSON%'

2/ Fetch within the view just created to select records to write subfile data:
- Statement = "Select * From MyView Where (... And/Or ...)"
- Prepare SEL from Statement
- Declare Cursor from SEL
- Fetch From Cursor
- Write SF data
etc.

IMHO This should work better.