IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Return SQL code 305


IBM Mainframe Forums -> ABENDS & Debugging
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
karthik_p

New User


Joined: 24 Apr 2007
Posts: 7
Location: chennai

PostPosted: Thu Apr 26, 2007 2:55 pm
Reply with quote

I am get return SQL code 305 when i trying to retrieve a data from a table with card no in the where clause. The card no is zeros can any one say what this SQL-code means.
Back to top
View user's profile Send private message
kanak

Moderator


Joined: 12 Mar 2005
Posts: 252
Location: India

PostPosted: Thu Apr 26, 2007 2:58 pm
Reply with quote

as per sqlcode list: SQLCODE -305 Null indicator needed

can you please post the query ?
Back to top
View user's profile Send private message
karthik_p

New User


Joined: 24 Apr 2007
Posts: 7
Location: chennai

PostPosted: Thu Apr 26, 2007 3:04 pm
Reply with quote

Kanak is this error due to the fact that we are giving a field which doesnot have any value in the where clause the query is as given below

Code:
EXEC SQL                                                 
  SELECT MAX(PYMT_POST_DTE)                               
  INTO :HIST-PYMT-POST-DTE                               
  FROM PYMT_HIST_INFO                                     
  WHERE CARD_NBR  = :HIST-CARD-NBR     AND               
        (PYMT_STATUS_CD = 'H ' OR PYMT_STATUS_CD = 'CR') 
  FETCH FIRST 1 ROW ONLY                                 
  WITH UR                                                 
END-EXEC.
Back to top
View user's profile Send private message
karthik_p

New User


Joined: 24 Apr 2007
Posts: 7
Location: chennai

PostPosted: Thu Apr 26, 2007 3:11 pm
Reply with quote

in the above query CARD_NBR has values Zero
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Thu Apr 26, 2007 4:24 pm
Reply with quote

Hi,

Their are no records as per your selection process and as the PYMT_POST_DTE is not null column so its getting the -305.

Possible solutions are

1) Better to insert a record with the selection criteria.

2) or

modify your query as

Code:
EXEC SQL
SELECT MAX(PYMT_POST_DTE)
INTO :HIST-PYMT-POST-DTE
         WS-NULL
FROM PYMT_HIST_INFO
WHERE CARD_NBR = :HIST-CARD-NBR AND
(PYMT_STATUS_CD = 'H ' OR PYMT_STATUS_CD = 'CR')
FETCH FIRST 1 ROW ONLY
WITH UR
END-EXEC.


check the value in the variable WS-NULL when it is 0 then use the value as a valid.

Cheers
Ashwin
Back to top
View user's profile Send private message
karthik_p

New User


Joined: 24 Apr 2007
Posts: 7
Location: chennai

PostPosted: Thu Apr 26, 2007 6:19 pm
Reply with quote

Thank you for ur reply i will try out with the changes and let u know
Back to top
View user's profile Send private message
suresh.bethanasamy

New User


Joined: 06 Jan 2007
Posts: 1
Location: chennai

PostPosted: Mon Apr 30, 2007 7:10 am
Reply with quote

Hi,

modify your query as

Code:
EXEC SQL
SELECT MAX(PYMT_POST_DTE)
INTO :HIST-PYMT-POST-DTE [b]:WS-NULL [/b]
FROM PYMT_HIST_INFO
WHERE CARD_NBR = :HIST-CARD-NBR AND
(PYMT_STATUS_CD = 'H ' OR PYMT_STATUS_CD = 'CR')
FETCH FIRST 1 ROW ONLY
WITH UR
END-EXEC.


This will Solve ur 305 sql code in your program.


Thanks
Suresh
Back to top
View user's profile Send private message
marimuthu_G

New User


Joined: 25 Feb 2008
Posts: 8
Location: India

PostPosted: Mon Mar 10, 2008 2:45 pm
Reply with quote

hi, im getting a -305 error while fetching columns having how to handle this......
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Mon Mar 10, 2008 4:04 pm
Reply with quote

Code:
-305: THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER BECAUSE NO INDICATOR VARIABLE IS SPECIFIED.
 and search for INDICATOR VARIABLE.


read this whole topic, suresh.bethanasamy has given an answer, and search for INDICATOR variable in the db2 manuals.
Back to top
View user's profile Send private message
pleonard

New User


Joined: 20 Sep 2006
Posts: 19

PostPosted: Sun Sep 21, 2008 2:36 am
Reply with quote

In my case I have a dynamic DB2 SQL (mainframe) where my Declare cursor with SELECT resides - in the working-storage. I use a PREPARE statement and Open Cursor passing some parameters that will override the parameter marker (?) in a SELECT. When I run a FETCH I got -305 in a field that has no value (I think that is NULL).

What should I do to solve this ?

Thanks
Paulo
Back to top
View user's profile Send private message
pleonard

New User


Joined: 20 Sep 2006
Posts: 19

PostPosted: Sun Sep 21, 2008 2:40 am
Reply with quote

Also I saw that are some workaround using WS-NULL as Indicator variable but I didn't understand yet which value this variable should contains and how use it in the FETCH statement.

Thanks
Paulo
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun Sep 21, 2008 2:59 am
Reply with quote

Hello,

When a "nullable" column is NULL, the NULL Indicator contains -1 (minus 1) which equals x'FF's.
Back to top
View user's profile Send private message
pleonard

New User


Joined: 20 Sep 2006
Posts: 19

PostPosted: Sun Sep 21, 2008 8:12 pm
Reply with quote

Thanks. I saw that the indicator variable (in Cobol PIC S9(04) comp) should be put beside the host variable. So when, for example, the FETCH is executed and the contents of the column is null, the indicator variable will be negative. That way the contents of the column, now in the host variable, can be discarded if you will.

Great
Thanks again
Paulo
Back to top
View user's profile Send private message
dcshnier

New User


Joined: 28 Dec 2006
Posts: 27
Location: Baltimore, MD 21215

PostPosted: Tue Dec 16, 2008 11:00 pm
Reply with quote

Here are some further comments to this -305 SQLCODE issue.

On a SELECT MAX operation, may developers forget to include the null-indicator because they erroneously assume that if the column itself is not nullable, then they therefore do not need to include the null-indicator on any SELECT INTO statements for that column. That may be true on simple SELECTS against that column. However, once that column is used within a SELECT MAX operation, or other similar scalar functions, it becomes nullable. (Or more precisely, even if the column itself is not nullable, the 'result' of the SELECT MAX on that column can contain a null - specifically, when the WHERE clause is not satisfied).
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> ABENDS & Debugging

 


Similar Topics
Topic Forum Replies
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Compile rexx code with jcl CLIST & REXX 6
No new posts Return codes-Normal & Abnormal te... JCL & VSAM 7
No new posts REXX code to expand copybook in a cob... CLIST & REXX 2
No new posts VSAM return code 23 - for a Random read COBOL Programming 4
Search our Forums:

Back to Top