Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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 browsing the Monastery: (13)
As of 2015-08-03 15:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The oldest computer book still on my shelves (or on my digital media) is ...













    Results (45 votes), past polls