How to find -811 entry in Easytrieve DB2 program?



Unicenter CA-Easytrieve Plus Report Generator: CA's information retrieval and data management tool

How to find -811 entry in Easytrieve DB2 program?

Postby rskmfguy » Mon May 27, 2013 9:34 am

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
rskmfguy
 
Posts: 10
Joined: Mon Oct 22, 2012 11:07 pm
Has thanked: 8 times
Been thanked: 0 time

Re: How to find -811 entry in Easytrieve DB2 program?

Postby NicC » Mon May 27, 2013 5:38 pm

not at clear what you are saying! try posting your db2 code.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3026
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: How to find -811 entry in Easytrieve DB2 program?

Postby enrico-sorichetti » Mon May 27, 2013 6:00 pm

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
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort

These users thanked the author enrico-sorichetti for the post:
rskmfguy (Mon May 27, 2013 10:27 pm)
enrico-sorichetti
Global moderator
 
Posts: 2935
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 156 times

Re: How to find -811 entry in Easytrieve DB2 program?

Postby rskmfguy » Mon May 27, 2013 10:26 pm

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
rskmfguy
 
Posts: 10
Joined: Mon Oct 22, 2012 11:07 pm
Has thanked: 8 times
Been thanked: 0 time

Re: How to find -811 entry in Easytrieve DB2 program?

Postby dick scherrer » Tue May 28, 2013 11:48 pm

Hello,

Last time i looked, INTO goes with the SELECT, Not the WHERE?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6269
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: How to find -811 entry in Easytrieve DB2 program?

Postby rskmfguy » Wed May 29, 2013 11:50 am

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
rskmfguy
 
Posts: 10
Joined: Mon Oct 22, 2012 11:07 pm
Has thanked: 8 times
Been thanked: 0 time

Re: How to find -811 entry in Easytrieve DB2 program?

Postby NicC » Wed May 29, 2013 5:38 pm

last on select statement but before the from statement, perhaps? Have you checked in the SQL manual?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3026
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: How to find -811 entry in Easytrieve DB2 program?

Postby dick scherrer » Wed May 29, 2013 7:25 pm

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".
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6269
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: How to find -811 entry in Easytrieve DB2 program?

Postby rskmfguy » Thu May 30, 2013 3:32 pm

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
rskmfguy
 
Posts: 10
Joined: Mon Oct 22, 2012 11:07 pm
Has thanked: 8 times
Been thanked: 0 time

Re: How to find -811 entry in Easytrieve DB2 program?

Postby NicC » Thu May 30, 2013 5:51 pm

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
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3026
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Next

Return to CA-Easytrieve

 


  • Related topics
    Replies
    Views
    Last post