ProC problem

[janet]

hi,
i am facing a problem while fetching a null value.
i.e
i have declared one structure and one cursor.
now from that cursor i want to fetch 100 records at a time for some processing .
i am fetching 100 recs but there are some fields which has a null value in it . so its giving me problem as---> Fetch across null fields.
so, can anyone tell me how to handle this in proc.

[Johnny101]

If you are using SQL Server, then you could use a the ISNULL function on the column(s) that are returning nulls:

'stored proc code - SELECT to return the cursor

SELECT
'FirstField' = ISNULL(ActualFieldName,' '), --the second parameter is the value to return instead of the null value
SecondField, ThirdField,
'FourthField' = ISNULL(ActualFieldName,' ')
FROM
tables...
WHERE...




Hope this helps,

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[janet]

Hi John ,

what you have said is right , but the problem is .

after selecting all the records into cursor , i am fetching 100 records at a time . and while i fetch i get this error msg.
by using your tech the problem will be solved , but i again need the actual value of that coulmn and not the ' ' value.

thanks..... i will be waiting for your reply (i am using pro *C and oracle)

[Johnny101]

I dont understand what you mean. If the field has a null in it - you get an error, but you want the null value be there. You can specify anything for the return value instead of the null value. You could then check the value to see if it's that special return value and then decide what to do.
BTW the syntax i gave you was for SQL Server, this is the syntax for Oracle:

SELECT
'FirstCol' = NVL(FirstActualFieldName, NULLReplacementValue),
'SecondCol' = NVL(SecondActualFieldName, NULLReplacementValue)
FROM
table1
WHERE
...




If you were to use this function on every column - that would ensure 100% that there are no null values in the cursor.

Let me know if I'm not reading your question right.

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org