Home » SQL & PL/SQL » SQL & PL/SQL » Select Query - Case When? (Oracle 10)
Select Query - Case When? [message #394829] Mon, 30 March 2009 08:11 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I'm not sure how to pull out the following data for a query I need.

I have the following tables: -

Transactions (Transaction Header)
Trans_Article (Transaction Detail)
Articles (Article/Product Detail)
Groups (Group Detail)

The Transaction table holds: -

ID
Article_ID
Bookkeeping_date

Trans_Articles: -

Transaction_ID
Article_ID
Price

Articles: -

ID
Code
Description
Group_A_ID

Now what I want to show is the SUM of the price where a transaction has been made where the Trans_articles for the transaction contains both articles from 'Group' 10 & 20

What should I use in my SQL?

I tried: -

SELECT      TR.trans_num, ar.code, ar.description,
            SUM(CASE WHEN GR1.CODE = '10' AND GR1.CODE = '20' THEN NVL(TA.PRICE, 0) + NVL(TA.DISCOUNT,0) + NVL(TA.PROMOTION_DISCOUNT,0) ELSE 0 END)
FROM        TRANSACTIONS TR, TRANS_ARTICLES TA, ARTICLES AR, GROUPS GR1, GROUPS GR2
WHERE       TR.id = TA.transaction_id
AND         TA.article_id = AR.id (+)
AND         AR.group_a_id = GR1.ID (+)
AND         AR.group_B_id = GR2.ID (+)
AND         TR.shop_id = (SELECT ID FROM SHOPS WHERE CODE = '110')
AND         TR.BOOKKEEPING_DATE BETWEEN TO_DATE('01/03/2009','DD/MM/YYYY') AND TO_DATE('01/03/2009','DD/MM/YYYY')
GROUP BY    TR.TRANS_NUM, ar.code, ar.description


But the returned Nothing, which I can see why.

If I try an 'OR' this returns everything, again I can see why.

Any ideas on how I can pull out just the transaction where both 10 & 20 have been sold?

Many Thanks & Regards
Re: Select Query - Case When? [message #394831 is a reply to message #394829] Mon, 30 March 2009 08:17 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Please post a Test Case.

Rajy
Re: Select Query - Case When? [message #394832 is a reply to message #394829] Mon, 30 March 2009 08:23 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
You may try to use
case when ... then sum (...) else 0 end 


Rajy
Re: Select Query - Case When? [message #394833 is a reply to message #394829] Mon, 30 March 2009 08:24 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
To give a definitive answer a proper test case is required - it's very hard to know how to join the tables without knowing what the unique keys are for starters.

However this bit looks wrong:
SUM(CASE WHEN GR1.CODE = '10' AND GR1.CODE = '20' 


doubt you're going to find a record where the code is 10 and 20 simultaneously.
Since you've declared a second instance of the groups table (GR2) you might want to consider actually using it.
Re: Select Query - Case When? [message #394834 is a reply to message #394831] Mon, 30 March 2009 08:25 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Thanks for the reply, I'll create on and post it, however give me a while.

Regards
Re: Select Query - Case When? [message #394838 is a reply to message #394834] Mon, 30 March 2009 08:44 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Here are the tables: -

CREATE TABLE transactions (
 id                     NUMBER(9),
 shop_id                NUMBER(9),
 trans_num              NUMERIC(5),
 bookkeeping_date       DATE);

CREATE TABLE trans_articles (
 transaction_id         NUMBER(9),
 article_id             NUMBER(9),
 price                  NUMERIC(10,2)   NOT NULL,
 discount               NUMERIC(10,2),
 promotion_discount     NUMERIC(10,2));
 
CREATE TABLE articles (
 id                     NUMBER(9),
 code                   VARCHAR(20)     NOT NULL,
 description            VARCHAR(20)     NOT NULL,
 group_a_id             NUMBER(9),
 group_b_id             NUMBER(9));
 
CREATE TABLE groups (
 id                     NUMBER(9),
 code                   NUMERIC(8),
 description            VARCHAR(40)     NOT NULL,
 notes1                 VARCHAR(40),
 notes2                 VARCHAR(40),
 notes3                 VARCHAR(40));


Data to follow...

Regards
Re: Select Query - Case When? [message #394839 is a reply to message #394838] Mon, 30 March 2009 08:54 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Data....

INSERT INTO transactions (id,shop_id,trans_num,bookkeeping_date) VALUES ('1','1','600','01/03/2009');
INSERT INTO transactions (id,shop_id,trans_num,bookkeeping_date) VALUES ('2','1','601','01/03/2009');
INSERT INTO transactions (id,shop_id,trans_num,bookkeeping_date) VALUES ('3','1','603','01/03/2009');

INSERT INTO trans_articles (transaction_id,article_id,price,discount,promotion_discount) VALUES ('1','1','1.10',NULL,NULL);
INSERT INTO trans_articles (transaction_id,article_id,price,discount,promotion_discount) VALUES ('1','2','0.50',NULL,NULL);
INSERT INTO trans_articles (transaction_id,article_id,price,discount,promotion_discount) VALUES ('2','1','1.10',NULL,NULL);
INSERT INTO trans_articles (transaction_id,article_id,price,discount,promotion_discount) VALUES ('2','3','4.50',NULL,NULL);
INSERT INTO trans_articles (transaction_id,article_id,price,discount,promotion_discount) VALUES ('3','3','4.50',NULL,NULL);
INSERT INTO trans_articles (transaction_id,article_id,price,discount,promotion_discount) VALUES ('3','3','4.50',NULL,NULL);

INSERT INTO articles (id,code,description,group_a_id,group_b_id) VALUES ('1','100','Beer','1',NULL);
INSERT INTO articles (id,code,description,group_a_id,group_b_id) VALUES ('2','101','Wine','1',NULL);
INSERT INTO articles (id,code,description,group_a_id,group_b_id) VALUES ('3','200','Food','2',NULL);

INSERT INTO groups (id,code,description,notes1,notes2,notes3) VALUES ('1','10','Wet',null,null,NULL);
INSERT INTO groups (id,code,description,notes1,notes2,notes3) VALUES ('2','20','Dry',null,null,NULL);


So for the query I want to show just the SUM of Price for the 2nd Transaction, the one containing both 'Wet' & 'Dry'.

Regards
Re: Select Query - Case When? [message #394840 is a reply to message #394833] Mon, 30 March 2009 08:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Mon, 30 March 2009 14:24
Since you've declared a second instance of the groups table (GR2) you might want to consider actually using it.

Re: Select Query - Case When? [message #394842 is a reply to message #394840] Mon, 30 March 2009 09:05 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
cookiemonster wrote on Mon, 30 March 2009 13:59
cookiemonster wrote on Mon, 30 March 2009 14:24
Since you've declared a second instance of the groups table (GR2) you might want to consider actually using it.




Hi,

I've changed the SQL to try and make use of the second instance, which was going to be used later for something else, but I get the same results if I use an 'AND' I get no records or an 'OR' I get all records?

SELECT      TR.trans_num, ar.code, ar.description,
            SUM(CASE WHEN GR1.CODE = '10' and GR2.CODE = '20' THEN NVL(TA.PRICE, 0) + NVL(TA.DISCOUNT,0) + NVL(TA.PROMOTION_DISCOUNT,0) ELSE 0 END)
FROM        TRANSACTIONS TR, TRANS_ARTICLES TA, ARTICLES AR, GROUPS GR1, GROUPS GR2
WHERE       TR.id = TA.transaction_id
AND         TA.article_id = AR.id (+)
AND         AR.group_a_id = GR1.ID (+)
AND         AR.group_a_id = GR2.ID (+)
AND         TR.shop_id = (SELECT ID FROM SHOPS WHERE CODE = '110')
AND         TR.BOOKKEEPING_DATE BETWEEN TO_DATE('01/03/2009','DD/MM/YYYY') AND TO_DATE('01/03/2009','DD/MM/YYYY')
GROUP BY    TR.TRANS_NUM, ar.code, ar.description


Regards
Re: Select Query - Case When? [message #394843 is a reply to message #394829] Mon, 30 March 2009 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>AND TR.BOOKKEEPING_DATE BETWEEN TO_DATE('01/03/2009','DD/MM/YYYY') AND TO_DATE('01/03/2009','DD/MM/YYYY')
How many records pass this filter?
Re: Select Query - Case When? [message #394844 is a reply to message #394843] Mon, 30 March 2009 09:10 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
BlackSwan wrote on Mon, 30 March 2009 14:07
>AND TR.BOOKKEEPING_DATE BETWEEN TO_DATE('01/03/2009','DD/MM/YYYY') AND TO_DATE('01/03/2009','DD/MM/YYYY')
How many records pass this filter?


Hi,

Thanks for the reply.

Do you mean how many records are returned?

220 records are returned.

Regards
Re: Select Query - Case When? [message #394847 is a reply to message #394829] Mon, 30 March 2009 09:40 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
ok - first some points about test cases.
1) post all tables referenced by your query (you're missing shops)
2) always to_date dates in your insert statements - just because it works for you doesn't mean it's going to work for everyone else - I get this:
INSERT INTO transactions (id,shop_id,trans_num,bookkeeping_date) VALUES ('2','1','601','01/03/2009')
 
ORA-01858: a non-numeric character was found where a numeric was expected

3) It helps to know what the keys are, we can make an educated guess but it'd be better if we didn't have to.

Onto the issue:
1) which AND are you changing to an OR?
2) I suspect you've just moved the problem:

AND         AR.group_a_id = GR1.ID (+)
AND         AR.group_a_id = GR2.ID (+)

Since both instances of groups are linked to the same column in articles they're going to have the same values for any record in the result set.
Previous Topic: Number data type issue
Next Topic: STAR - PROGRAM
Goto Forum:
  


Current Time: Fri Dec 09 15:33:25 CST 2016

Total time taken to generate the page: 0.11592 seconds