Stored procedure returning records with null

ag_ram · Post by **ag_ram** » Sat Dec 15, 2007 4:28 pm

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?

ag_ram · Post by **ag_ram** » Sat Dec 15, 2007 4:32 pm

Adding to this...

If execute the same select query in toad it is returning 0 records.

chulett · Post by **chulett** » Sat Dec 15, 2007 5:12 pm

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.

ag_ram · Post by **ag_ram** » Sun Dec 16, 2007 1:10 am

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

chulett · Post by **chulett** » Sun Dec 16, 2007 9:20 am

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.

ag_ram · Post by **ag_ram** » Thu Dec 20, 2007 4:34 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Dec 20, 2007 7:35 pm

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.

DSXchange

Stored procedure returning records with null

Stored procedure returning records with null

Re: Stored procedure returning records with null