Re: SQL help

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/ Received on Fri Jan 19 2001 - 07:56:44 CST