0

I have a table called room_table

room_no room_status  room_type
-------|-----------|----------
1001   | A         |single
1002   | A         |single
1003   | B         |single

I am using following code in CICS COBOL program but I am getting SQL error code -811

    select room_no 
    from room_table 
    where room_status='A' 
      and room_type ='single'
    fetch first 1 row only

I have used cursor concept too but it returns error code -311

Any idea?

karthi
  • 1,059
  • 3
  • 10
  • 21
  • Apart from the fact that your statement is syntactically incorrect (the `select first...` clause is invalid), it could not possibly result in either SQLCODE -811 or -311. – mustaccio Apr 26 '14 at 17:17
  • Agree, run it through SPUFI or QMF to check. -311 was from the cursor (which we do not see here). Q's been edited after the comment above. Are you testing the SQLCODE or a variable it has been moved to ? Did the move happen ? You should not get a -811 with first row only. What are you selecting INTO ? Has this query been paraphrased ? – mckenzm Mar 29 '15 at 17:32

3 Answers3

1

This would of been answered before but:

  1. Use a cursor and only fetch the first record, see Cursor
  2. Use group by
       
     Select min(room_no) from  room_table 
      where room_status='A' and room_type ='single'
     group by room_status
Bruce Martin
  • 10,358
  • 1
  • 27
  • 38
0

Did you try using DB2 Fetch-first-clause?

replace select first 1 row only with fetch first row only

MrSimpleMind
  • 7,890
  • 3
  • 40
  • 45
0

-311 is not -811 : Something is wrong with a host variable.

For -811 we would "fetch first row only".

Now,

prior to this feature being available, many coders ignored IBM's warnings and simply allowed -811 as OK, as a value was somehow always returned (albeit without guarantee). I would hope that any such code has long been touched up.

mckenzm
  • 1,545
  • 1
  • 12
  • 19