Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Oracle nvl function

by kanwisch (Sexton)
on May 22, 2002 at 19:39 UTC ( #168560=perlquestion: print w/ replies, xml ) Need Help??
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};

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."); }
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};

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.
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};
    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).
      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);
      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);
      Update: I should point out that both of these pieces of advice are found in the documentation for the DBI module

      buckaduck

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://168560]
Approved by virtualsue
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (13)
As of 2015-09-02 16:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred temperature scale is:










    Results (85 votes), past polls