Re: SQL Problem

Thanks for your reply

They are stored as dates.

If you take to to_date() conversion off it works.

CHRIS

> -----Original Message-----
> From:	Elango Velayutham [SMTP:[email protected]]
> Sent:	18 February 2003 13:11
> To:	Oracle
> Subject:	[oracle] Re: SQL Problem
> Importance:	High
> 
> Hi,
> 
> Some clarification regarding how the voidstart and end dates are stored?
> as
> dates or as characters??
> 
> Depending on that and the nls_date_format results may vary?
>  
> 
> TIA
> Elango 
> 
> 
> 
> 
> -----Original Message-----
> From: Ravinder S Dhillon [mailto:[email protected]]
> Sent: 18 February 2003 16:37
> To: Oracle
> Subject: [oracle] Re: SQL Problem
> 
> 
> As the it will be excluding these two dates i.e os from date and to date
> .......
> 
> (In between these 2 days)
> 
> ----- Original Message -----
> From: "Chris Sol" <[email protected]>
> To: "Oracle" <[email protected]>
> Sent: Tuesday, February 18, 2003 5:51 PM
> Subject: [oracle] SQL Problem
> 
> 
> > SQL> --void record check
> > SQL> select *
> >   2  from zz_voidrecordst
> >   3  where ref = '1394';
> >
> > REF   VOIDSTART VOIDEND   PATH
> > ----- --------- --------- --------------------
> > 1394  08-FEB-99 07-APR-02 PIPE
> >
> > SQL> --actual transactions
> > SQL> select vtran_pro_propref,vtran_effective_date,vtran_dr
> >   2  from   void_transactions
> >   3  where  vtran_effective_date >= '01-APR-2002'
> >   4  and vtran_pro_propref = '1394';
> >
> > VTRAN_PRO_PROPREF         VTRAN_EFF  VTRAN_DR
> > ------------------------- --------- ---------
> > 1394                      01-APR-02        58
> >
> > SQL> -- LINK:
> > SQL> select sum(V.vtran_dr) as Total,
> >   2      Z.REF,
> >   3      Z.Path
> >   4  from   void_transactions v, zz_voidrecordst z
> >   5  where  v.vtran_pro_propref = z.ref and
> >   6      (v.vtran_effective_date between to_date(z.voidstart) and
> > to_date(z.voidend))
> >   7        and v.vtran_effective_date >= '01-APR-2002'
> >   8      and z.ref = '1394'
> >   9  group by Z.REF, Z.Path;
> >
> > no rows selected
> >
> > Why is the bottom query return no rows?
> >
> > Chris Sol
> > Analyst Programmer
> > IT Development
> > Genesis Housing Group
> > Telephone 02085374169
> >
> >
> >
> **************************************************************************
> **
> > The Genesis Housing Group - Email Disclaimer
> > --------------------------------------------
> > The information in this email and any attachments is strictly
> > confidential and may be legally privileged.
> > It is intended solely for the use of the addressee(s) only. Access to
> > this email by anyone else is unauthorised.  If you have received this
> > email in error please notify the sender immediately by return email or
> > telephone.  Please note that if you are not the intended recipient, you
> > must not copy, distribute or otherwise act on any part of this email or
> > it's attachments.
> > Although we have taken precautions to ensure that this email and
> > attachments are free from any virus, we would advise you that it is your
> > responsibility to ensure that this is true when reading or actioning
> > this email.
> >
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or
> > to unsubscribe send a blank email to %%email.unsub%%.
> 
> 
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or 
> to unsubscribe send a blank email to %%email.unsub%%.
> 
> 
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or 
> to unsubscribe send a blank email to %%email.unsub%%.
>  
****************************************************************************
The Genesis Housing Group - Email Disclaimer
--------------------------------------------
The information in this email and any attachments is strictly
confidential and may be legally privileged.
It is intended solely for the use of the addressee(s) only. Access to
this email by anyone else is unauthorised.  If you have received this
email in error please notify the sender immediately by return email or
telephone.  Please note that if you are not the intended recipient, you
must not copy, distribute or otherwise act on any part of this email or
it's attachments.
Although we have taken precautions to ensure that this email and
attachments are free from any virus, we would advise you that it is your
responsibility to ensure that this is true when reading or actioning
this email.

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].