Home » SQL & PL/SQL » SQL & PL/SQL » Need to get null values! (10g, ubuntu)
Need to get null values! [message #382069] Tue, 20 January 2009 19:13 Go to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi,

My requirement is as follows...

Example:

I have a table called emp and emp_salary. I need to get the salaries of the manager category. I write query something like...

SELECT e.emp_name, es.salary
FROM emp e
LEFT JOIN emp_salary es ON e.emp_id = es.emp_id
WHERE es.category = 'M';

In the above query shows the only records of whose category is equal to 'M'. But my requirement is to get all the emp names and the salaries of the manager category. Other employees salaries should be null.

How to write query to get this kind of output?

Thanks in advance.

Regards

Re: Need to get null values! [message #382070 is a reply to message #382069] Tue, 20 January 2009 19:17 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Need to get null values! [message #382071 is a reply to message #382070] Tue, 20 January 2009 19:46 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi BlackSwan,

Sorry for the post. Thanks a lot for your correction.

I will try to explain you better now.

I have a query like following...
SELECT CA.C_PROJECT_ID, SUM(CA.AMTACCTCR - CA.AMTACCTDR) AS AMOUNT 
FROM CUS_ACCTDIM_FACT_ACCT CA
LEFT JOIN M_PRODUCT MP ON MP.M_PRODUCT_ID = CA.M_PRODUCT_ID
WHERE MP.PRODUCTTYPE = 'S'
GROUP BY CA.C_PROJECT_ID;
Result is:
---------
PROJECT AMOUNT
1000087	4175
1000093	4000


But I need to get the following result:

PROJECT AMOUNT
1000087	4175
1000088	
1000089	
1000090	
1000091	
1000092	
1000093	4000


I mean I need to get all the project ids but amount should show only to the records that match the condition. Other projects will have null value.

Can someone help me how to write query to get this output?

Thanks in advance.
Re: Need to get null values! [message #382091 is a reply to message #382071] Tue, 20 January 2009 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an OUTER JOIN.

Regards
Michel
Re: Need to get null values! [message #382101 is a reply to message #382091] Wed, 21 January 2009 00:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 21 January 2009 06:15
Use an OUTER JOIN.

Regards
Michel


And using an outer join, move the change the where condition (which works on the whole dataset) to a join condition (only for table salaries)
How to get null values! [message #382141 is a reply to message #382101] Wed, 21 January 2009 02:31 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi,

Thanks for your reply. In fact my first example was wrong. I need to get two columns from the same table, but I use more tables in the WHERE section.

My query is:
SELECT CA.C_PROJECT_ID, SUM (CA.AMTACCTCR - CA.AMTACCTDR) AS AMOUNT
   FROM CUS_ACCTDIM_FACT_ACCT CA
LEFT JOIN M_PRODUCT MP ON CA.M_PRODUCT_ID = MP.M_PRODUCT_ID
  WHERE MP.PRODUCTTYPE  = 'S'
  GROUP BY CA.C_PROJECT_ID;


I need to get all the C_PROJECT_IDs and AMOUNT for the Projects that are associated with Product (ProductType = 'S').

My output should look like the following:

PROJECT AMOUNT
1000087	4175
1000088	
1000089	
1000090	
1000091	
1000092	
1000093	4000

My present output is:

[CODE]PROJECT AMOUNT
1000087	4175
1000093	4000
[/CODE]



Thanks in advance.
Re: How to get null values! [message #382142 is a reply to message #382141] Wed, 21 January 2009 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change you have to use outer join.

Also post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How to get null values! [message #382159 is a reply to message #382142] Wed, 21 January 2009 03:39 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi Michel,

Here is my Test case...

--Create table CUS_PRODUCT 
CREATE TABLE CUS_PRODUCT(M_PRODUCT_ID NUMBER(10)
                      ,PRODUCT_TYPE CHAR(1)
);

--Create table CUS_ACCTDIM_FACT_ACCT
CREATE TABLE CUS_ACCTDIM_FACT_ACCT(C_PROJECT_ID NUMBER(10)
                                  ,M_PRODUCT_ID NUMBER (10)
                                  ,AMTACCTCR NUMBER(10,2)
                                  ,AMTACCTDR NUMBER(10,2)
                                  );
--Inserting CUS_PRODUCT
INSERT INTO CUS_PRODUCT VALUES (20000, 'E');
INSERT INTO CUS_PRODUCT VALUES (20001, 'P');
INSERT INTO CUS_PRODUCT VALUES (20002, 'S');
commit;

--Inserting CUS_ACCTDIM_FACT_ACCT
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10000, 20000, 500, 0);
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10001, 20001, 500, 0);
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10002, 20002, 500, 0);
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10003, 20003, 500, 0);
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10004, 20002, 500, 0);
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10005, 20002, 500, 0);
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10004, 20000, 2000, 0);
INSERT INTO CUS_ACCTDIM_FACT_ACCT VALUES (10005, 20002, 4000, 0);
commit;


--Query
SELECT CA.C_PROJECT_ID as PROJECT, SUM (CA.AMTACCTCR - CA.AMTACCTDR) AS AMOUNT FROM CUS_ACCTDIM_FACT_ACCT CA
LEFT JOIN CUS_PRODUCT P ON CA.M_PRODUCT_ID = P.M_PRODUCT_ID
WHERE P.PRODUCT_TYPE = 'S'
GROUP BY CA.C_PROJECT_ID;

--Output
 
PROJECT  AMOUNT
10004	   500
10002	   500
10005	  4500

But I need to get like...

PROJECT  AMOUNT
10004	   500
10002	   500
10005	  4500
10001      
10003     



Could you please help me to get it?

Thanks in advance.
Re: How to get null values! [message #382166 is a reply to message #382159] Wed, 21 January 2009 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT CA.C_PROJECT_ID as PROJECT,
  2         SUM(decode(P.PRODUCT_TYPE, 'S', CA.AMTACCTCR - CA.AMTACCTDR, NULL)) AS AMOUNT
  3  FROM CUS_ACCTDIM_FACT_ACCT CA
  4       LEFT outer JOIN CUS_PRODUCT P
  5       ON ( CA.M_PRODUCT_ID = P.M_PRODUCT_ID )
  6  GROUP BY CA.C_PROJECT_ID
  7  order by CA.C_PROJECT_ID
  8  /
   PROJECT     AMOUNT
---------- ----------
     10000
     10001
     10002        500
     10003
     10004        500
     10005       4500

6 rows selected.

Regards
Michel
Re: How to get null values! [message #382182 is a reply to message #382166] Wed, 21 January 2009 04:44 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi Michel,

Thanks a lot. Its working fine.

Its really great help.

Regards.

Re: How to get null values! [message #382186 is a reply to message #382182] Wed, 21 January 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The most important thing is: Do you understand the modification I made to your query and the final query itself?

Regards
Michel
Re: How to get null values! [message #382230 is a reply to message #382186] Wed, 21 January 2009 07:26 Go to previous message
gynanda
Messages: 33
Registered: November 2007
Member
Yes Michel, I understood that logic. Thanks a lot.

Regards.
Previous Topic: ora-04091 table mutating error with trigger
Next Topic: Inheritance and Collections
Goto Forum:
  


Current Time: Mon Dec 05 21:19:54 CST 2016

Total time taken to generate the page: 0.08821 seconds