|
| |
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
 |
NULL values - 3/31/2008 18:44:46
The following snip of VB code pulls data from 3 tables for an events log. Since some details have no officer assigned, this necessarily generates a NULL value for officer_name as it cannot make the match from cc_data's reporting_officer1 to officer_number's badge. It all works fine, except it leaves a big blank on the printout for those events where there was no officer assigned. We would like to test in VB for when the value is NULL and recast the variable to "None Assigned" but after much effort we have found no operation able to test if a value is either is or is not NULL and allow a replacement. Before spending another afternoon digging, is this even possible to do?
fp_sQry="SELECT c.primary_key, c.occ_date, c.occ_time, c.final_case_type," _
& " c.zone, ct.translation, c.address, c.report_flag, c.remarks, c.clear_remarks," _
& " o.officer_name" _
& " FROM case_type ct, cc_data c LEFT OUTER JOIN officer_number o" _
& " ON c.reporting_officer1 = o.badge" _
& " WHERE (c.occ_date='03/31/2008') AND c.final_case_type=ct.code"
|
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 10:04:42
Good morning and thank you for the post. I will try that out in just a sec and let you know what it does..
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 10:15:24
Uh-oh... did it, and I get this:
Database Results Error
Description: [Informix][Informix ODBC Driver][Informix]Routine (isnull) can not be resolved.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
Makes we wonder what that value is to begin with. On those events with no officer, it is blank, yet it defies any logical test so I can't think of any other possibility.
I will keep at this and report any new findings.
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 10:44:59
Quickly running out of ideas and options, I tried yet another trick to append some value, any value, to the SQL return so it would always be something. "officer_name" is the derived value from the officer_number table, when table cc_data, column, "reporting_officer1" matches officer_number table, column "officer_name" Every once in awhile, there is no value of reporting_officer1 in the table to match, and the DBA tells us when no officer is assigned, it is a NULL.
So here is what we put: "x"||o.officer name, (also single quoted and doubledouble'd it) I assume that is the right syntax to append a constant value???? It worked for us in other applications.
and it crashed:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/daily_bulletin/_fpclass/fpdblib.inc, line 48
Same problem if I call for the variable c.reporting_officer1. Seems as if there are any NULLS there if fouls up the whole unload.
| |
|
|
|
rdouglass
Posts: 9187
From: Biddeford, ME USA
Status: offline
|
RE: NULL values - 4/1/2008 11:18:06
quote:
Uh-oh... did it, and I get this:
Can you post exactly what you used when you did the IsNull thing? That's pretty 'standard fare' for databases and if there's a null there, there should be an IsNull statement to fix it. But remember Nulls are not the same as an empty string.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 13:02:22
Ok gimme justa sec and I will provide what is there now and works and what does not.
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 13:12:37
This is what gets the general database error or the unable to resolve the isnull command. tried all possible delimiters around the substitute variable. Informix database.
fp_sQry="SELECT c.primary_key, c.occ_date, c.occ_time, c.final_case_type," _
& " c.zone, ct.translation, c.address, c.report_flag, c.remarks, c.clear_remarks," _
& " ISNULL(o.officer_name,'x')" _
& " FROM case_type ct, cc_data c LEFT OUTER JOIN officer_number o" _
& " ON c.reporting_officer1 = o.badge" _
& " WHERE (c.occ_date>='::start_date::' AND c.occ_date<='::end_date::') AND c.final_case_type=ct.code" _
& " ORDER BY c.occ_date ASC, c.occ_time ASC"
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 13:28:11
NVL?? I will give it a try and report back. I had not seen that option before, maybe Informix will like it better.
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 13:37:06
No easy task. NVL as structured gave the following results:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/daily_bulletin/_fpclass/fpdblib.inc, line 48
with single quotes, double-double quotes.
I might add, I got one record before it abruptly stopped. It acts sort of like the name of the data element gets changed, not its value, because we get that error usually from typos in calling a variable, or not including it in SELECT.
Will keep digging and experimenting and report back any success. Thanks so much for your time and energy on this. In this case we can live with a blank but we may need to know how to do this in the future.
| |
|
|
|
rdouglass
Posts: 9187
From: Biddeford, ME USA
Status: offline
|
RE: NULL values - 4/1/2008 14:16:27
quote:
& " ISNULL(o.officer_name,'x')" _
I have no idea if this makes a diff in Informix but I know SQL Server would want to see this something like this instead:
& " ISNULL(o.officer_name,'x') AS officer_name" _
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 14:18:44
you may be on to something. Justa sec and I test.....
| |
|
|
|
pd_it_guy
Posts: 139
Joined: 3/4/2008
Status: offline
|
RE: NULL values - 4/1/2008 14:29:45
SUCCESS!!!!!
It was finally the "NVL" operator for the Informix DB, replacement single quoted, and the addition of the AS statement recasting the variable. Works Perfectly.
You guys are great...
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 