Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re: Oracle nvl function

by kanwisch (Sexton)
on May 22, 2002 at 20:30 UTC ( [id://168576]=note: print w/replies, xml ) Need Help??


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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://168576]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others chilling in the Monastery: (1)
As of 2024-04-25 01:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found