View previous topic :: View next topic
|
Author |
Message |
karthik_p
New User
Joined: 24 Apr 2007 Posts: 7 Location: chennai
|
|
|
|
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 |
|
|
kanak
Moderator
Joined: 12 Mar 2005 Posts: 252 Location: India
|
|
|
|
as per sqlcode list: SQLCODE -305 Null indicator needed
can you please post the query ? |
|
Back to top |
|
|
karthik_p
New User
Joined: 24 Apr 2007 Posts: 7 Location: chennai
|
|
|
|
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 |
|
|
karthik_p
New User
Joined: 24 Apr 2007 Posts: 7 Location: chennai
|
|
|
|
in the above query CARD_NBR has values Zero |
|
Back to top |
|
|
ashwinreddy
Active User
Joined: 16 Sep 2004 Posts: 106 Location: Hyderabad
|
|
|
|
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 |
|
|
karthik_p
New User
Joined: 24 Apr 2007 Posts: 7 Location: chennai
|
|
|
|
Thank you for ur reply i will try out with the changes and let u know |
|
Back to top |
|
|
suresh.bethanasamy
New User
Joined: 06 Jan 2007 Posts: 1 Location: chennai
|
|
|
|
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 |
|
|
marimuthu_G
New User
Joined: 25 Feb 2008 Posts: 8 Location: India
|
|
|
|
hi, im getting a -305 error while fetching columns having how to handle this...... |
|
Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
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 |
|
|
pleonard
New User
Joined: 20 Sep 2006 Posts: 19
|
|
|
|
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 |
|
|
pleonard
New User
Joined: 20 Sep 2006 Posts: 19
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
When a "nullable" column is NULL, the NULL Indicator contains -1 (minus 1) which equals x'FF's. |
|
Back to top |
|
|
pleonard
New User
Joined: 20 Sep 2006 Posts: 19
|
|
|
|
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 |
|
|
dcshnier
New User
Joined: 28 Dec 2006 Posts: 27 Location: Baltimore, MD 21215
|
|
|
|
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 |
|
|
|