What's wrong with my SQL

[thelearner]

01.00 F$POMST UF E K DISK
02.00
03.00 /free
05.00 C/Exec SQL
06.00 c+ Select *
07.00 c+ From $pomst
08.00 c+ Where podvcd = '21'
09.00 c/End-Exec
10.00 READ $POMST;
11.00 DOW NOT %EOF($POMST);
....
....
What's wrong with my CODE here? When I compile I got msg saying that line 6 position 11 missing clause INTO.

 

[Pekka]

Hi,
I think you have to declare cursor for your select statement.

 

[thelearner]

Hi,
Sorry to ask silly question, but what do you mean by that.
This is my first SQL coding.

 

[Pekka]

Ooh, long story and maybe I'm not the best one to answer to that. But I try. In your sql statement you are selecting records for nothing. By declaring cursor you can fetch those records to something.

Declare myCursor for
Select cuno, cunm
From MyCustomers
For update of cunm

Exec Sql
Open MyCursor
End/Exec

Exec Sql
Fetch mycursor
Into :cun, :CUNMM
End-Exec

And so on..
Hope this help a little bit... Try to find redbookB2 UDB for iSeries Programming Concepts

 

[iSeriesCodePoet]

Well that really depends. If you are only going to be getting one record back that will work, but you have to tell SQL where you want the data to go into. Pekka's post has some good information on that. Let me know if you need more help than that.

BTW, if you are getting multiple records, you really should use a cursor as mentioned above. Really basic example:

* declare your cursor
* open the cursor
* check to make sure SQLCOD is 0 if it isn't you have some sort of error or there is no data. No data would have an error code of either -100 or 100 (I can't remember)
* fetch from cursor
* dow SQLCOD = 0 (or however you like to code it)
* process record
* fetch new record
* enddo

This is the basic of what I do. I hope this helps.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Thank for both of you. I will give it a try.

 

[thelearner]

Hi guys,

This is exacly how I coded. It compiled with no error. But it did not work. when I debugged at line# 19 to see values of HTS# and F2, they were both blanks. Please help!!

01.00 F$ULPART UF E K DISK RENAME(#ULPART:#PFILE)
02.00 DPART# S 25A
03.00 DHTS# S 15A
05.00 C/EXEC SQL
06.00 C+ DECLARE MYCURSOR DYNAMIC SCROLL CURSOR FOR
07.00 C+ SELECT F1, F2
08.00 C+ FROM $ULPART
09.00 C+ WHERE F2 = '6203.42.40.10 '
10.00 C/END-EXEC
11.00 C/EXEC SQL
12.00 C+ OPEN MYCURSOR
12.00 C/END-EXEC
14.00 C SQLCOD DOWEQ 0
15.00 C/EXEC SQL
16.00 C+ FETCH CURRENT FROM MYCURSOR

17.00 C+ INTO ART#,:HTS#
18.00 C/END-EXEC
19.00 C EVAL HTS# =%SUBST(HTS#:1:4)
20.00 C + %SUBST(HTS#:6:2)

21.00 + %SUBST(HTS#:9:2)

22.00 C + %SUBST(HTS#:12:2)
23.00 C UPDATE #PFILE
24.00 C ENDDO
25.00 C EVAL *INLR = '1'

 

[iSeriesCodePoet]

What is in SQLCOD?

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi,

SQLCOD = 000000000
Here is my joblog
Unable to retrieve query options file.
Unable to retrieve query options file.
Unable to retrieve query options file.
CONNECT to relational database S100385D completed.
Current connection is to relational database S100385D.
Unable to retrieve query options file.
**** Starting optimizer debug message for query .
Unable to retrieve query options file.
Arrival sequence access was used for file $ULPART.
Access path suggestion for file $ULPART.
**** Ending debug message for query .
ODP created.
Blocking used for query.
Cursor MYCURSOR opened.
Position of cursor MYCURSOR not valid for FETCH of current row.
Operation sequence for member $ULPART not valid. (C I)

 

[thelearner]

and here is the detail msg for 1st msg,
Unable to retrieve query options file.

Message ID . . . . . . : CPI433A
Date sent . . . . . . : 01/18/04 Time sent . . . . . . : 07:34:55

Message . . . . : Unable to retrieve query options file.

Cause . . . . . : Unable to retrieve the query options from member QAQQINI
in file QAQQINI in library QUSRSYS for reason code 2. The reason codes an
their meanings follow:
1 - Library QUSRSYS was not found.
2 - File QAQQINI in library QUSRSYS was not found.
Recovery . . . : Default query options will be used, unless one one of the
following actions are taken, based on the reason code above.
1 - Either create the library (CRTLIB command) or correct the library nam
and then try the request again.
2 - Either specify the library name that contains the query options file
or create a duplicate object (CRTDUPOBJ command) of file QAQQINI from
library QSYS into the specified library.

 

[thelearner]

Hi,

I have obj QAQQINI in QSYS. Do I have to have one in QUSRSYS like the first msg said?

 

[iSeriesCodePoet]

Do you have SQL installed on your system?

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Yes, I do have it. I can use command STRSQL to run some querry interactively.

 

[thelearner]

Hi,
Forgot to ask you this, do I need to do any thing special, like when I compile the program. Do I need to change any parameter?

 

[iSeriesCodePoet]

What is the details of this message:
Operation sequence for member $ULPART not valid. (C I)

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi,
Here is the detail msg.

User . . . . . . . . . . : RAPEEK
Additional Message Information
Message ID . . . . . . : CPF501B
Date sent . . . . . . : 01/20/04 Time sent . . . . . . : 13:35:35
Message . . . . : Operation sequence for member $ULPART not valid. (C I)
Cause . . . . . : An update or delete was requested before a get for update
for member $ULPART file $ULPART in library CISDTALIB.
Recovery . . . : Either change the operation sequence in the program or
delete the existing program and compile it again. Then try the request
again.
Possible choices for replying to message . . . . . . . . . . . . . . . :
C -- The request is canceled.
I -- Ignore the request and continue processing.

 

[iSeriesCodePoet]

Okay, so you are doing an update or delete? You must do a select on that record before you delete it.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[thelearner]

Hi,

Ok, I will try that. But I still wondering. When I did the debug on line 19, shouldn't I see some data? I was trying to see the value of field names 'F2' and 'HTS#', they were both blanks.

 

[PeteJohnston]

If you haven't "SELECT"ed the record before you try to do the update, the fields F2 and HTS# won't have any values unless you explicitly populate them elsewhere in your code. This is exactly the same as using standard RPG file access methods - the fields from the file don't contain any values until you do a READx or a CHAIN.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[thelearner]

Hi,

Here is my new program, which still not working. SQLCOD was -501. The msg was file was not journaled or no authority to the journa. How can I fix this without journaling the file. Thank you for all your help.

D DS
DPART# 25A
DHTS# 40A

C/EXEC SQL
C+ DECLARE MYCURSOR DYNAMIC SCROLL CURSOR FOR
C+ SELECT F1, F2
C+ FROM $ULPART
C+ WHERE F2 = '6203.42.40.10 '
C+ FOR UPDATE OF F2
C/END-EXEC
C/EXEC SQL
C+ OPEN MYCURSOR
C/END-EXEC

C/EXEC SQL
C+ FETCH FROM MYCURSOR
C+ INTO ART#, :HTS#
C/END-EXEC
C SQLCOD DOWEQ 0
EVAL HTS# = %SUBST(HTS#:1:4)
+ %SUBST(HTS#:6:2)
+ %SUBST(HTS#:9:2)
+ %SUBST(HTS#:12:2)
C/EXEC SQL
C+ UPDATE $ULPART
C+ SET F2 = :HTS#
C+ WHERE CURRENT OF MYCURSOR
C/END-EXEC

C/EXEC SQL
C+ FETCH FROM MYCURSOR
C+ INTO ART#, :HTS#
C/END-EXEC

C ENDDO
C/EXEC SQL
C+ close mycursor
C/END-EXEC