IBM Mainframe Forum

Hi All,

I see the use of cursor in most of the Easytrieve - DB2 programs. I have a requirement to read a key from a file and use it to fetch rows from DB2 table and if it has multiple entry in the table (-811) then I should write it in a separate file.

As cursor will not return -811, can you please help me to know how to achieve this using Easytrieve.

Thank you in advance!

Regards,
rskmfguy

not at clear what you are saying! try posting your db2 code.

Reason of code -811 :THE RESULT OF AN EMBEDDED SELECT STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF THE SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE

a cursor will never return that reason code, because the cursor expects multiple rows to be returned .

one of the possible approaches could be

1) use a SELECT COUNT to find out if there are duplicates

2) process using a cursor those keys which return a COUNT greater than 1

Hi All,

Sorry if I'm not clear. I will try to explain my req in a better way (forgive me if I fail again )

FileA
1234
1333

Table STORET :
Store Div
1234 999
1234 888
1333 111

My Easytrive-DB2 program should read the store number from file and check the table and if it had more than 1 row for that store then it should write the store in error file. Sorry, I cannot use count(*) because if the store had single entry then I should do some validations with other fields.

I tried achieve this with below code

JOB INPUT FILEA

WS-STORE-NBR = STORE

SQL +
SELECT STORE, DIV +
FROM STORET +
WHERE STORE = :WS-STORE-NBR +
INTO :WS-STR, :WS-DIV
CASE SQLCODE
WHEN 0
WHEN -811


But while compiling the above code is giving an error msg like

'INTO CAN BE USED ONLY WITH SQLSYNTAX NONE'

All the easytrive-db2 programs are using cursor in our shop. So I couldn't get a sample for this req. My desktop is secured so I couldn't copy the actual code here.

Kindly help.

Regards,
rskmfguy

Hello,

Last time i looked, INTO goes with the SELECT, Not the WHERE?

Hi Dick,

I tried both

SQL +
SELECT STORE, DIV +
FROM STORET +
WHERE STORE = :WS-STORE-NBR +
INTO :WS-STR, :WS-DIV
CASE SQLCODE

SQL ERROR, SQLCODE IS -199
DSNT408I SQLCODE = -199, ILLEGAL USE OF KEYWORD INTO. TOKEN MICROSECONDS
MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS EXPECTED.
DSNT418I SQLSTATE - 42601 SQLSTATE RETURN CODE
"SELECT INTO" IS VALID ONLY FOR SQLSYNTAX OF NONE AND STATIC-ONLY BIND

SQL +
SELECT STORE, DIV +
FROM STORET +
INTO :WS-STR, :WS-DIV
WHERE STORE = :WS-STORE-NBR
CASE SQLCODE

SQL ERROR, SQLCODE IS -104
DSNT408I SQLCODE = -104, ILLEGAL SYMBOL '?'. SOME SYSMBOL THAT MIGHT BE LEGAL ARE
ARE : <IDENTIFIER> :
MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS EXPECTED.
DSNT418I SQLSTATE - 42601 SQLSTATE RETURN CODE
"SELECT INTO" IS VALID ONLY FOR SQLSYNTAX OF NONE AND STATIC-ONLY BIND


CA-Easytrive Language Reference Guide says 'The INTO: clause must be the last clause coded on the SELECT statement'. So I mentioned them at last after the where clause.

Regards,
rskmfguy

last on select statement but before the from statement, perhaps? Have you checked in the SQL manual?

Hello,

The INTO is Not with the WHERE in either bit of posted code. . .

At the top of the page (and the bottom) there is a link to IBM Manuals. The SQL reference shows this rather clearly. In that manual seach for SELECT INTO and try the first "hit".

Hi All,

In the above post I made a typo

SQL +
SELECT STORE, DIV +
INTO :WS-STR, :WS-DIV +
FROM STORET +
WHERE STORE = :WS-STORE-NBR
CASE SQLCODE

Actually I gave the INTO clause just before FROM but I misplaced it when typing in the forum. The -104 error is for the above query

Syntax I found in CA Easytrieve Language Reference Manual:

{ {* }
[DISTINCT] { {expression }
SELECT [ ] { { } +
[ALL ] { {table-name.* }
{ {correlation-name.*}
}
[ {expression } ] }
[, {tabLe-name.* } . ..] } +
[ {correlation-name.*} ] }
}
FROM table-name [correlation-name] +
[,table-name [correlation-name] ...] +
[WHERE search-condition] +
[GROUP BY column-name + ] +
[ [, column-name ...] ]
[HAVING search-condition] +
[ { {* } ]
[ [DISTINCT] { {expression } ]
[UNION SELECT [ ] { { } + ]
[ [ALL ] { {table-name.* } ]
[ { {correlation-name.*} ]
[ ]
[ } ]
[ [ {expression } ] } ]
[ [, {table-name.* } ...] } + ]
[ [ {correlation-name.*} ] } ]
[ } ]
[ ]
[ FROM table-name [correlation-name] + ]
[ [,table-name [correlation-name] ...] + ] +
[ ]
[ [WHERE search-condition] + ]
[ ]
[ [GROUP BY column-name + ] + ]
[ [ [, column-name ...] ] ]
[ ]
[ [HAVING search-condition] ]
[ {column-name} [ASC ] ]
[ORDER BY { } [ ] + ]
[ {integer } [DESC] ]
[ ]
[ [ {column-name} [ASC ] ] ]
[ [, { } [ ] ...] ] +
[ [ {integer } [DESC] ] ]
INTO :host-variable [, :host-variable...]

INTO :host-variable [, :host-variable...] INTO identifies where the column values are to be placed. The INTO clause must be the last clause coded on the SELECT statement.

Regards,
rskmfguy

Do not retype - cut and paste. You then avoid the errors that you made and save everyones's time. And you should have used the code tags when quoting from the manual as that helps to retain formatting. If you do not know how to use the code tags there are loads of mentions on the forum and you should be able to work it out if you use the PostReply button instead of the QuickReply button