Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

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?

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


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://168560]
Approved by virtualsue
[ambrus]: Corion: some of these stupid syntax highlighters assume that too. just look at the table in http://perldoc. functions/pack. html for example.
[haukex]: ..."yet" ;-) I haven't had to deal with Dist::Zilla yet but I've heard about how it's a big setup
[ambrus]: I really don't like automagic stuff. I'm happy when computers do exactly what I tell them, even if that means they sometimes do the wrong thing.
[ambrus]: And I don't much like syntax highlighters. If you need a syntax highlighter to understand your code, then your code is written unclear.
[ambrus]: And if you need a syntax highlighter to color parenthesis green and numbers black and letters blue, then you're using the wrong font.
[ambrus]: I have to tolerate syntax highlighters when other people use them, but I don't use them myself. And sorry for the rant.

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (14)
As of 2017-02-27 12:45 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (385 votes). Check out past polls.