OraFAQ Forum: SQL & PL/SQL » Less than X Date?

Home » SQL & PL/SQL » SQL & PL/SQL » Less than X Date?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Less than X Date? [message #7394]

Wed, 11 June 2003 06:57

Andrew
Messages: 144
Registered: March 1999

Senior Member

Hi,

I'm trying to pull data where one of the criteria is less than X date.

Here is what I've tried: -

AND TR.TRANS_DATE >= TO_DATE('11/06/2003','DD.MM.YYYY')

This only gives me one record, when there is actualy 3. The other 2 have a date of 29.05.2003.

Why aren't these returned?

Regards,

Andrew

Report message to a moderator

Re: Less than X Date? [message #7396 is a reply to message #7394]

Wed, 11 June 2003 07:06

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Andrew,

Two things spring to mind:<ol>[*]You say you're trying to pull data where one of the criteria is less than X date, but in your code snippet you have ">=" (greater than or equal to);[*]The date string in your TO_DATE function has slashes, but your date masks has periods.</ol>

HTH,

A

Report message to a moderator

Re: Less than X Date? [message #7398 is a reply to message #7396]

Wed, 11 June 2003 07:43

Andrew
Messages: 144
Registered: March 1999

Senior Member

That's the strange part.

I tried it with "<=" and got nothing, switched it and it returned records. Strange!

Here's the complete code: -

    '* Loop through for each Till, checking last Close Date (End Of Day) and only writting to Sage
    '* transactions where an end of day has been run.
    '* Create instance of Oracle objects.
    Set adoTills = New ADODB.Recordset
    adoTills.CursorLocation = adUseClient
    
    adoTills.Open "SELECT ID, CODE, DESCRIPTION FROM TB_CASHREGS", adoCnn, adOpenStatic, adLockOptimistic
    
    '* Loop through Till by Till.
    For intTill = 1 To adoTills.RecordCount
        '* Create instance of Oracle objects.
        Set adoEndOfDay = New ADODB.Recordset
        adoEndOfDay.CursorLocation = adUseClient
        
        '* Get the Tills last closing date.
        strQuery = "SELECT      CL.CLOSING_DATE " _
                    & "FROM     TB_CLOSINGS CL " _
                    & "WHERE    CL.CASHREG_ID = '" & adoTills!ID & "' " _
                    & "AND      TRUNC(CL.CLOSING_DATE) < TO_DATE('" & Format(Now, "DD-MMM-YYYY HH:MM:SS") & "','dd-mm-yyyy hh24:mi:ss') " _
                    & "AND      CL.CLOSING_DATE =   (SELECT     MAX(CL2.CLOSING_DATE) " _
                                                    & "FROM     TB_CLOSINGS CL2 " _
                                                    & "WHERE    CL2.CASHREG_ID = '" & adoTills!ID & "' " _
                                                    & "AND      TRUNC(CL2.CLOSING_DATE) < TO_DATE('" & Format(Now, "DD-MMM-YYYY HH:MM:SS") & "','dd-mm-yyyy hh24:mi:ss'))"
        
        adoEndOfDay.Open strQuery, adoCnn, adOpenStatic, adLockOptimistic
        
        '* Check that Till has been closed i.e. Records returned.
        If adoEndOfDay.EOF = True Then
            '* No closing date exists for Till.
            '* Write details to Log File and get out.
        Else
            '* Till has been closed.
        
            '* Check against TB_SAGE_TRANS to see if any Transactions made have already been passed to Sage.
            '* Create instance of Oracle objects.
            Set adoTrans = New ADODB.Recordset
            adoTrans.CursorLocation = adUseClient
    
            adoTrans.Open "SELECT TR.ID, TR.CASHREG_ID, TR.TRANS_NUM, TR.TRANS_DATE, TR.CASH_PAYMENT_ID, " _
                            & "TR.CREDITCARD_PAYMENT_ID, TR.CHEQUE_PAYMENT_ID, " _
                            & "TR.CASH_AMOUNT, TR.CREDITCARD_AMOUNT, TR.CHEQUE_AMOUNT, TR.CARD_NUM " _
                            & "FROM TB_TRANSACTIONS TR, TB_SAGE_TRANS ST " _
                            & "WHERE TR.ID = ST.TRANS_ID (+) " _
                            & "AND NVL(ST.TRANS_ID, -1) = -1 " _
                            & "AND TR.CASHREG_ID = '" & adoTills!ID & "' " _
                            & "AND TRUNC(TR.TRANS_DATE) <= TRUNC(TO_DATE('" & Format(adoEndOfDay!CLOSING_DATE, "dd-mmm-yy") & "','DD.MM.YYYY'))", adoCnn, adOpenStatic, adLockOptimistic

Just the SQL: -

SELECT TR.ID, TR.CASHREG_ID, TR.TRANS_NUM, to_char(TR.TRANS_DATE, 'dd.mm.yyyy hh24:mi:ss'),
TR.CASH_PAYMENT_ID, TR.CREDITCARD_PAYMENT_ID, TR.CHEQUE_PAYMENT_ID, TR.CASH_AMOUNT,
TR.CREDITCARD_AMOUNT, TR.CHEQUE_AMOUNT, TR.CARD_NUM
FROM TB_TRANSACTIONS TR, TB_SAGE_TRANS ST
WHERE TR.ID = ST.TRANS_ID (+)
AND NVL(ST.TRANS_ID, -1) = -1
AND TR.CASHREG_ID = 1
AND TRUNC(TR.TRANS_DATE) <= TRUNC(to_date('11-jun-03','DD.MM.YYYY'))
/

Prehaps it's me and I've got myself into a knot?

Any help would be appreciated.

Regards,

Andrew

Report message to a moderator

Re: Less than X Date? [message #7399 is a reply to message #7398]

Wed, 11 June 2003 08:03

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Andrew,

Although it's probably not the source of your problem, can you match up the format of the date and date format in your last line?

So have either TO_CHAR('11-JUN-03','YY-MON-RR') or TO_CHAR('11.06.2003','DD.MM.YYYY').

Since you're outer joining from tb_transactions to tb_sage_trans, you'll also need an outer join on your NVL line, like this:

AND    NVL(ST.TRANS_ID <B><font color=red>(+)</font></B>, -1) = -1

For why this additional outer join is needed, click here.

If that still doesn't fix it, I would issue this sequence of SQL statements in order and see which step I lost rows in:

SELECT COUNT(*)
FROM   tb_transactions    tr
WHERE  tr.cashreg_id = 1
/
  
SELECT COUNT(*)
FROM   tb_transactions    tr
,      tb_sage_trans      st
WHERE  tr.cashreg_id = 1
AND    tr.id = st.trans_id (+)
/
  
SELECT COUNT(*)
FROM   tb_transactions    tr
,      tb_sage_trans      st
WHERE  tr.cashreg_id = 1
AND    tr.id = st.trans_id (+)
AND    NVL(st.trans_id (+), -1) = -1
/
  
SELECT COUNT(*)
FROM   tb_transactions    tr
,      tb_sage_trans      st
WHERE  tr.cashreg_id = 1
AND    tr.id = st.trans_id (+)
AND    NVL(st.trans_id (+), -1) = -1
AND    TRUNC(tr.trans_date) <= TO_DATE('11-JUN-2003','DD-MON-YYYY')
/

Good luck, Andrew.

Art

Report message to a moderator

Previous Topic:	Out of present range
Next Topic:	output BLOB to HTML page.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:39:02 CDT 2024