Oracle nvl function

kanwisch has asked for the wisdom of the Perl Monks concerning the following question:

Ok, I've got a script where I'm doing a simple SELECT out of a table. There're two date columns I want subtracted, and to deal with the mathematical NULL problem of getting NULL back, I've tried using the NVL function. Here's the SQL code:

$sSQL = qq{SELECT distinct (snickname), saccountnum, dtacctcreateddate
+, (nvl(dtacctenableddate,to_date('01-jan-1900')))-(nvl(dtacctdisabled
+date,to_date('01-jan-1900'))) as disableddiff, laccountid, sacctdisab
+led
FROM $gsAccountTable 
WHERE SAPP = '$gsApp' AND SOWNERID = '$sOwnerID' and DTACCTCREATEDDATE
+ IS not NULL and SACCTDISABLED <> 'DELETED'
ORDER BY SACCTDISABLED};
[download]

However, after executing, it my cursor is apparently empty, because when I try to loop over the results (by hashref), it skips the loop. Here's the loop for curiosity's sake:

while ($sRow = $sCursor->fetchrow_hashref) {
    $iCount++;
    $gsaInput{'LACCOUNTID'.$iCount} = $sRow->{LACCOUNTID};
    $gsaInput{'SACCTNAME'.$iCount} = $sRow->{SNICKNAME};
    $gsaInput{'SACCTNUM'.$iCount} = $sRow->{SACCOUNTNUM};
    $gsaInput{'SACCTDATE'.$iCount} = $sRow->{DTACCTCREATEDDATE};
    $gsaInput{'SACCTDISABLED'.$iCount} = $sRow->{DISABLEDDIFF};
    &TestLog ("Assigning $sRow->{DISABLEDDIFF} to $iCount.");
}
[download]

However, if I removed the NVL so that the SQL looks like this:

$sSQL = qq{SELECT distinct (snickname), saccountnum, dtacctcreateddate
+, dtacctenableddate-dtacctdisableddate as disableddiff, laccountid, s
+acctdisabled
FROM $gsAccountTable 
WHERE SAPP = '$gsApp' AND SOWNERID = '$sOwnerID' and DTACCTCREATEDDATE
+ IS not NULL and SACCTDISABLED <> 'DELETED'
ORDER BY SACCTDISABLED};
[download]

it seems to work fine (except those items with NULLS have no mathematical result). But at least the cursor's getting the data. Any thoughts on how I can get my cursor to receive data while still using the NVL function?

Comment on Oracle nvl function Select or Download Code

Replies are listed 'Best First'.
Re: Oracle nvl function by samgold (Scribe) on May 23, 2002 at 03:10 UTC
Check the oracle documentation, but I think you could use another function to get the disableddiff. I think there is a function called months_between( date_1, date_2). That may work out better. There are some other date functions also that may suit your needs, but I can't remember what they are right now. again check the docs at http://technet.oracle.com/docs/content.html I hope that helps.	[reply]
Re: Oracle nvl function by kanwisch (Sexton) on May 22, 2002 at 20:30 UTC
Well, with some more messing around with other options, I've figured out a workaround, regardless. Here's the new SQL: `$sSQL = qq{SELECT distinct(snickname), saccountnum, dtacctcreateddate, + nvl(dtacctenableddate-dtacctdisableddate, dtacctdisableddate-sysdate +) as disableddiff, laccountid, sacctdisabled FROM $gsAccountTable WHERE SAPP = '$gsApp' AND SOWNERID = '$sOwnerID' and DTACCTCREATEDDATE + IS not NULL and SACCTDISABLED <> 'DELETED' ORDER BY SACCTDISABLED};` [download] And in this way, I'll always receive a negative if an account's actually disabled. But it bugs me that the NVL didn't appear to be cooperating with a TO_DATE function that was nested using DBI. The SQL works just fine from my database tool (with the same substitutions).	[reply] [d/l]
Re: Re: Oracle nvl function by buckaduck (Chaplain) on May 22, 2002 at 21:38 UTC
Offtopic, but it's a good idea to use placeholders instead of those embedded variables. Otherwise, you'll get clobbered someday when your variable contains an apostrophe (e.g. "Smith's Corp."): `$sSQL = qq{ ... WHERE SAPP = ? AND SOWNERID = ? ... }; ... $sCursor->execute($gsApp, $sOwnerID);` [download] Also, if you're still getting NULL values for these variables that break the SQL, you could always make the SQL code more complex to handle it: `$sSQL = qq{ ... WHERE (SAPP = ? OR (? is NULL AND SAPP is NULL)) AND . +.. }; ... $sCursor->execute($gsApp, $gsApp, $sOwnerID, $sOwnerID);` [download] Update: I should point out that both of these pieces of advice are found in the documentation for the DBI module buckaduck	[reply] [d/l] [select]


Clear questions and runnable code get the best and fastest answer
	PerlMonks