Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (10.2.0.3.0)
SQL Query [message #411174] Thu, 02 July 2009 04:35 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have a table as follows.

Table_Name : Employeee_Desc
Customer_Num	TYPE_CD1  	L1_CD1
--------------------------------------------------------------------------------------
1111	                BIC_SER	                     STD
1111	                SSPSELL	                     S_FIRST
2222	                BIC_SER	                     STD
3333	                SSPSELL	                     C_EXEC


Required O/P is 

Customer_Num       SSP_CD1	        BIC_CD1
-------------------------------------------------------------------------
1111                      S FIRST                  STD
2222	             null	          STD
3333                     C_EXEC	          null

for this O/P I have written query as follws.
SELECT	  customer_num
,	  MIN (CASE WHEN type_cd1 = 'SSPSELL' THEN l1_cd1 END)	AS ssp_cd1
,	  MIN (CASE WHEN type_cd1 = 'BIC_SER' THEN l1_cd1 END)	AS la_cd1
FROM	  employee_desc
GROUP BY  cusotmer_num
ORDER BY  cusotmer_num
;
Now I need to write a query for the following for above written query.
If SSP_CD1 = 'null'
then la_CD1
else SSP_CD1
Default value: 'null';
can any please help me to write a query for this.
can I merge it with above mentioned query?if yes pls let me know and help me to write a query or the above requirement.

Thank you,
Ram
Re: SQL Query [message #411184 is a reply to message #411174] Thu, 02 July 2009 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Align the columns in result. Use the "Preview Message" button to verify.

Regards
Michel
Re: SQL Query [message #411190 is a reply to message #411174] Thu, 02 July 2009 06:28 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
USE NVL2
Re: SQL Query [message #411195 is a reply to message #411190] Thu, 02 July 2009 06:59 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Anand,

Along with that query I am not able to use NVL@ function.
can you please help me out to write the query ?

Thank you,
SNN
Re: SQL Query [message #411201 is a reply to message #411195] Thu, 02 July 2009 07:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, if you gave any reason WHY you are not able we might be able to help.

But since all we know is that there is a developer which is saying he is not able to do something, the only thing that one could suggest is to get another developer.
Re: SQL Query [message #411218 is a reply to message #411201] Thu, 02 July 2009 08:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
ThomasG wrote on Thu, 02 July 2009 13:22
Well, if you gave any reason WHY you are not able we might be able to help.

But since all we know is that there is a developer which is saying he is not able to do something, the only thing that one could suggest is to get another developer.

Laughing
Re: SQL Query [message #411222 is a reply to message #411195] Thu, 02 July 2009 09:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can't use NVL2, can you use DECODE?
Re: SQL Query [message #411294 is a reply to message #411195] Fri, 03 July 2009 00:25 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Here is one example
SELECT	  a
,	  NVL(MIN (CASE WHEN b = 'SSPSELL' THEN c END),MIN (CASE WHEN b = 'BIC_SER' THEN c END))	AS ssp_cd1
,	  MIN (CASE WHEN b = 'BIC_SER' THEN c END) AS la_cd1
FROM	  test_5
GROUP BY  a
ORDER BY  a
Re: SQL Query [message #411349 is a reply to message #411294] Fri, 03 July 2009 03:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looks like the OP's puled his usual disappearing trick.
Previous Topic: How to transform data of flat files while loading to external table
Next Topic: how to create partition index on non-partitiion table
Goto Forum:
  


Current Time: Tue Dec 06 16:04:26 CST 2016

Total time taken to generate the page: 0.09407 seconds