in reply to Oracle nvl function

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).

Replies are listed 'Best First'.
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]

In Section Seekers of Perl Wisdom