null values populated by last non null value while fetching data from informix

[sbish7]

HI All,

We are facing  very unusual issue while fetching data from informix database. All the null values are populated by last non null value. For ex. if the email for any customer is blank then while fetching it gets populated by the last non null email id. Is there any property that we need to set in sas management console for this.

Regards

[TomKari]

I don't usually say this, but your post is so bizarre I'm wondering if something else is happening.

Can I suggest you create a query that demonstrates this behaviour with the simplest possible code, and then post that code?

I've never heard of anything even remotely resembling this before.

Tom

[sbish7]

please find attached the snapshots of the code and the output. this is the first time i have seen this issue. Could it be a driver issue.

[TomKari]

I would contact SAS Tech Support on this one. Point them at exactly this conversation; you have provided an excellent example for them to see. I can't conceive of any explanation for this where the software is working correctly.

As a sanity check, I ran the following code and it returned exactly the expected results (second row email and mobile were null).

Tom

data Caddresses;

length serno 8 email $32 mobile 8;

serno = 165717; email = "md@gmail.com"; mobile =9523; output;

serno = 165718; call missing(email, mobile); output;

run;

proc sql;

select serno, email, mobile from Caddresses where serno in(165717, 165718);

quit;

[Tom]

Check into what you are pulling from.  

Perhaps it is a view and the WHERE clause is getting applied in an unexpected way.

Run it using explicit pass through mode.

Turn on logging options to see the exact SQL that SAS generate to perform the implicit pass thru.

[sbish7 · Accepted solution]

Issue is now resolved . We upgrade the informix odbc driver to version 4.10.fc9. now data is coming as expected.