-
Hiya, quick question on how to do IF... Then type queries.
Variables are REF_NO, LINE_OBJECTS and LINE_ACTION. There are 2 types of data that I want returned:
a) where REF_NO<>0 and LINE_OBJECT in (11, 27) and LINE_ACTION =603
and
b)where REF_NO=0 and LINE_OBJECT=25 and LINE_ACTION=650
What I want to avoid is to return rows where it only has LINE_ACTION= 11 and 25 against it. I want either 11, 27 and 25 or just 11 and 27.
Probelm is, I have no idea how to go about putting such conditions in my SQL (which is simple SELECT statement)
Any help will be welcome!
Fiona
-
we need more information in order to help you.
Jeff Hunter
-
Full syntax is (you asked for it)
SELECT c.store_no, b.store_name, c.entry_date_time, c.transaction_no, a.reference_no, SUM(a.gross_line_amount - a.pos_discount_amount)*a.db_cr_none) as SUM1,a.line_object, SUM(a.gross_line_amount * a.db_cr_none) as SUM2, a.line_action
FROM AUDITWORKS.sv_av_transaction_line a, AUDITWORKS.sv_store b,
AUDITWORKS.sv_av_transaction_header c
WHERE c.transaction_id=a.transaction_id
AND c.store_no=b.store_no
AND c.transaction_date >= '17/09/02')
AND 1=1
AND a.line_action not in 246
GROUP BY c.store_no,b.store_name,c.transaction_date,c.entry_date_time,c.transaction_no,a.reference_no,a.line_ object,a.line_action
I want to add the condtions I described inthe first post to this above syntax.
-
SELECT c.store_no, b.store_name, c.entry_date_time, c.transaction_no, a.reference_no, SUM(a.gross_line_amount - a.pos_discount_amount)*a.db_cr_none) as SUM1,a.line_object, SUM(a.gross_line_amount * a.db_cr_none) as SUM2, a.line_action
FROM AUDITWORKS.sv_av_transaction_line a, AUDITWORKS.sv_store b,
AUDITWORKS.sv_av_transaction_header c
WHERE c.transaction_id=a.transaction_id
AND c.store_no=b.store_no
AND c.transaction_date >= '17/09/02')
AND 1=1
AND a.line_action not in 246
/* MY ADD-ON */
AND ( CASE when REF_NO<>0 and LINE_OBJECT in (11, 27) and LINE_ACTION =603 then 1
when REF_NO=0 and LINE_OBJECT=25 and LINE_ACTION=650
then 1
else 0 ) = 1
/* END ADDON */
GROUP BY c.store_no,b.store_name,c.transaction_date,c.entry_date_time,c.transaction_no,a.reference_no,a.line_ object,a.line_action
Aleš
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|