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

[download]

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

[download]
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};

[download]

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 http://technet.oracle.com/docs/content.html I hope that helps.
[reply]
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};

    [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).
[reply]
[d/l]
      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]

Back to Seekers of Perl Wisdom