Re: SQL help

From: <jwaterh_at_my-deja.com>
Date: Fri, 19 Jan 2001 14:03:38 GMT
Message-ID: <949hfk$cfv$1@nnrp1.deja.com>

In article <949h2o$c2d$1_at_nnrp1.deja.com>, Matthew Fuller <matthewlf_at_my-deja.com> wrote:
> In article <949gk7$bj4$1_at_nnrp1.deja.com>,
> jwaterh_at_my-deja.com wrote:
> > Sorry if this is a re-post
> >
> > Could someone please offer me some help. i am trying to write a
script,
> > of which I am about 90% of the way there. Basically, the date poeple
> > enter data is stored in either one of either two columns (depends on
> > how that data was entered into the system). The script I am writting
> > pulls out a report based upon whether either b.bt_iw_date or
> > b.bt_entry_date falls within the entered range , which is no problem
> > when I have to deal with just one column. This is what I have
written
> > so far (the important bits anyway):
> >
> > ACCEPT start_date DATE FORMAT 'DD/MM/YYYY' -
> > PROMPT 'Please enter the start date of this report ("DD/MM/YYYY"): '
> >
> > ACCEPT end_date DATE FORMAT 'DD/MM/YYYY' -
> > PROMPT 'Please enter the end date of this report ("DD/MM/YYYY"): '
> >
> > SELECT TO_CHAR (sysdate, 'DD/MM/YYYY') TODAY,
> > b.bt_authorised_by "POSTED BY", b.bt_ref "BATCH
> > NUMBER",b.bt_entry_date "DATE", b.bt_iw_date "DATE", (count
(distinct
> > i.il_po_no)) "INVOICES" , (count (i.il_po_no)) "LINES"
> > FROM batches b, invoice_lines i
> > WHERE b.bt_ref = i.il_batch_ref
> > AND b.bt_sub_ledger = 'SL1'
> > (AND b.bt_iw_date BETWEEN TO_DATE ('&start_date', 'DD/MM/YYYY')
> > AND TO_DATE ('&end_date', 'DD/MM/YYYY')
> > OR b.bt_entry_date BETWEEN TO_DATE ('&start_date', 'DD/MM/YYYY')
> > AND TO_DATE ('&end_date', 'DD/MM/YYYY'))
> > GROUP BY b.bt_authorised_by, b.bt_ref, b.bt_iw_date,b.bt_entry_date,
> > i.il_po_no
> > /
> >
> > Which does not work because of the 'OR' bit. I am sure a solution is
> > staring me in the face, but I can not find it.
> >
> > Thanks for your time.
> >
> > Regards
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >

> There's something a little funny in your syntax. Specifically, it's
> this part:

> AND b.bt_sub_ledger = 'SL1'
> (AND b.bt_iw_date BETWEEN TO_DATE ('&start_date', 'DD/MM/YYYY')

> Shouldn't this be:

> AND b.bt_sub_ledger = 'SL1'
> AND (b.bt_iw_date BETWEEN TO_DATE ('&start_date', 'DD/MM/YYYY')

> Note the second line has the AND outside the parenthesis. I actually
> can't even imagine that syntax working without trying it myself.

> Anyway, HTH.

> Matt.

> Sent via Deja.com
> http://www.deja.com/

>

I definately could not see the wood for the trees, thanks Matt it is sorted now.

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 19 2001 - 08:03:38 CST