Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
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
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

Re: Oracle nvl function
by NodeReaper (Curate) on May 22, 2002 at 21:48 UTC

    Reason: (rbc) looks like trolling to me (delete)

    For more information on this node visit: this

      Oracle NVL Function replace NULL values with a given value in the result of a query. Oracle NVL Function takes two parameters. If expression of first parameter is not null then NVL returns the expression of first parameter, otherwise returns the expression of second parameter. See details at: http://www.rahinur.com/oracle-functions/oracle-nvl-function.html
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.

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: (5)
As of 2014-12-20 11:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (95 votes), past polls