Page 1 of 1
Stored procedure returning records with null
Posted: Sat Dec 15, 2007 4:28 pm
by ag_ram
Hello
I have no data in tables.
It is still returning 1 row with all the null values in the columns.
If table has 4 rows then it is returning 5 rows with one extra record having all values as null.
Can any one has clue?
Posted: Sat Dec 15, 2007 4:32 pm
by ag_ram
Adding to this...
If execute the same select query in toad it is returning 0 records.
Re: Stored procedure returning records with null
Posted: Sat Dec 15, 2007 5:12 pm
by chulett
ag_ram wrote:It is still returning 1 row with all the null values in the columns. If table has 4 rows then it is returning 5 rows with one extra record having all values as null. Can any one has clue?
Assuming this "it" is a stored procedure, then no, with no code posted - no clue. That really should not be a DataStage issue but something with how the SP is coded. And comparing procedural PL/SQL code to a stand-alone select in Toad isn't really valid. IMHO.
Take your issue up with whomever wrote the proc, see what they have to say.
Posted: Sun Dec 16, 2007 1:10 am
by ag_ram
Hi chulett,
You are right.
I forgot to mention...datastage extracts data from SQL server through stored procedure stage. When I execute the same stored proc in toad, it is returning no records but in datastage, it is extracting 1 record with null values for all the columns.
Let me have a talk with DBA. If anyone has any clue, I welcome all the pointers.
kamal
Posted: Sun Dec 16, 2007 9:20 am
by chulett
Still think that clues will only come from this side of the glass if you post the proc code - and that from someone that speaks SQL Server. Me, I assumed you meant an Oracle procedure.
Interesting, the 'O' in 'TOAD' stood for 'Oracle' but now that it's 'Toad' and they have versions for other databases, I'm going to have to be careful about making that particular assumption.
Posted: Thu Dec 20, 2007 4:34 pm
by ag_ram
Hi
I am not able to fix in datastage stored proc stage or in the code (Prepared by DBA). I have made only workaround such as I have put one constraint in the transformer for 3 key columns.(If key columns <> 0 processing the records.)
But if anyone has resolution for it...i will be more happier.
thanks
Posted: Thu Dec 20, 2007 7:35 pm
by ray.wurlod
Best solution is to demand that the author of the stored procedure fix it so that it returns zero rows if there are zero rows selected.