Home » SQL & PL/SQL » SQL & PL/SQL » Need a query
Need a query [message #327124] Sat, 14 June 2008 01:25 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hi,

I have two tables Service_name, Language.Data is as follows:

Service_name


DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
999 100041 fin Disney World
999 100041 eng Swathi1
999 100001 eng kod1
999 100001 chin chinese
999 100003 germ germanese
999 100003 french french

Language table has following rows

LANGUAGE_KEY LANGUAGE_CODE LANGUAGE_NAME IS_DEFAULT
1 eng English 0
2 fin Finnish 1
3 sve Swedish 0
4 deu German 0



I want to return rows from Service_name table as follows:
In second table is_default is 1 for finnish language.

So from first table I have to get rows grouping by DCO_ID AND SERVICE_ID. If the language_code='fin' i should get only that row for dco_id 999 and service_id =100041. It should discard second row for dco_id=999,service_id=100041 and language_code='eng' should be discarded.

For dco_id=999 and service_id=100001 there is no default language_code='fin' so it should retreive any one row either with 'eng' or 'chin' for that dco_id and service_id.

For dco_id=999 and service_id=100003 it should get a row either
with 'germ' or 'french' as there is no default language 'fin'

I need a query for this?

Ravi

Re: Need a query [message #327126 is a reply to message #327124] Sat, 14 June 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More than 100 posts and you don't know to:
- give a meaningul title
- post with code tags
- post a test case: create tables and insert statements
- give the result for the test case.

Correct that.

Regards
Michel
Re: Need a query [message #327129 is a reply to message #327124] Sat, 14 June 2008 02:02 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Two tables Service_name and Language:

create table Service_name(dco_id integer,service_id integer, language_code varchar2(10), service_name varchar2(50));

insert into service_name values (999,100041,'fin','Disney World');
insert into service_name values (999,100041,'eng','swathi1');
insert into service_name values (999,100001,'eng','kod1');
insert into service_name values (999,100001,'chin','chinese');
insert into service_name values (999,100003,'germ','german');
insert into service_name values (999,100003,'french','french');

commit;


create table language(language_key integer, language_code varchar2(10),language_name varchar2(15),is_default integer);

insert into language values (1,'eng','English',0);
insert into language values (2,'fin','Finnish',0);
insert into language values (3,'sve','Swedish',0);
insert into language values (4,'deu','German',0);


I want to return rows from Service_name table as follows:
In second table is_default is 1 for finnish language.

So from first table I have to get rows grouping by DCO_ID AND SERVICE_ID. If the language_code='fin' i should get only that row for dco_id 999 and service_id =100041. It should discard second row for dco_id=999,service_id=100041 and language_code='eng' should be discarded.

For dco_id=999 and service_id=100001 there is no default language_code='fin' so it should retreive any one row either with 'eng' or 'chin' for that dco_id and service_id.

For dco_id=999 and service_id=100003 it should get a row either
with 'germ' or 'french' as there is no default language 'fin'

I need a query for this?





Re: Need a query [message #327135 is a reply to message #327129] Sat, 14 June 2008 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is still not formatted and there is no result chart.

Regards
Michel
Re: Need a query [message #327139 is a reply to message #327124] Sat, 14 June 2008 03:45 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Please dont irritate people. The ultimate aim is to get the answer. Did u understand what I wrote. After all , its in human readable format.

Dont degrade or belittle others.
Re: Need a query [message #327141 is a reply to message #327139] Sat, 14 June 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The ultimate aim is to get the answer.

YOUR aim is to get answer.

Quote:
Did u understand what I wrote.

I don't know what "u" understand.

Quote:
Dont degrade or belittle others.

It is not my aim.
If you feel this then maybe the reason is that you actually feel you did it wrong.

If you really want an answer then follow the rules.

Regards
Michel
Re: Need a query [message #327142 is a reply to message #327124] Sat, 14 June 2008 04:20 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hey raviraviki,,


could u make a tabled format of which how the desired result u want for the above query ??.. .
pls then we peoples easily understand ..



thanks
seyed
Re: Need a query [message #327143 is a reply to message #327124] Sat, 14 June 2008 04:22 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
The result set should like this:

DCO_ID	 SERVICE_ID	LANGUAGE_CODE	  SERVICE_NAME
999	 100041	        fin	           Disney World
999	 100001	        eng	           kod1
999	 100003	        germ	           german




[mod-edit: added code tags]

[Updated on: Sat, 14 June 2008 11:50] by Moderator

Report message to a moderator

Re: Need a query [message #327145 is a reply to message #327124] Sat, 14 June 2008 04:33 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

both of the following queries will work ..
SELECT *
  FROM service_name
 WHERE  (ROWID) in (SELECT   MIN (ROWID)
                               FROM service_name
                          GROUP BY dco_id, service_id ) 




SELECT *
  FROM service_name
 WHERE  (ROWID) not in (SELECT   MAX (ROWID)
                               FROM service_name
                          GROUP BY dco_id, service_id )
Re: Need a query [message #327163 is a reply to message #327124] Sat, 14 June 2008 06:59 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Did u check the condition for table Language? I also know eliminating the duplicate rows.But its not the case here.

Read the description properly. Understand what I wrote and then
give a reply
Re: Need a query [message #327165 is a reply to message #327163] Sat, 14 June 2008 07:05 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

i dint see that well but your result chart n mine s same..
then i have to make a solution like that na. .,.
Re: Need a query [message #327186 is a reply to message #327165] Sat, 14 June 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
seyed456 wrote on Sat, 14 June 2008 14:05
i dint see that well but your result chart n mine s same..
then i have to make a solution like that na. .,.

Keep it in english, please?

Regards
Michel

Re: Need a query [message #327198 is a reply to message #327163] Sat, 14 June 2008 12:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Here are a couple of different methods:

-- test data:
SCOTT@orcl_11g> COLUMN language_code FORMAT A13
SCOTT@orcl_11g> COLUMN service_name  FORMAT A12
SCOTT@orcl_11g> SELECT * FROM service_name
  2  /

    DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
---------- ---------- ------------- ------------
       999     100041 fin           Disney World
       999     100041 eng           swathi1
       999     100001 eng           kod1
       999     100001 chin          chinese
       999     100003 germ          german
       999     100003 french        french

6 rows selected.

SCOTT@orcl_11g> SELECT * FROM language
  2  /

LANGUAGE_KEY LANGUAGE_CODE LANGUAGE_NAME   IS_DEFAULT
------------ ------------- --------------- ----------
           1 eng           English                  0
           2 fin           Finnish                  1
           3 sve           Swedish                  0
           4 deu           German                   0

SCOTT@orcl_11g> 


-- using row_number:
SCOTT@orcl_11g> SELECT dco_id, service_id, language_code, service_name
  2  FROM   (SELECT s.dco_id, s.service_id, s.language_code, s.service_name,
  3  		    l.is_default,
  4  		    ROW_NUMBER () OVER
  5  		      (PARTITION BY s.dco_id, s.service_id
  6  		       ORDER BY l.is_default DESC NULLS LAST) AS rn
  7  	     FROM   service_name s, language l
  8  	     WHERE  s.language_code = l.language_code (+))
  9  WHERE  rn = 1
 10  /

    DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
---------- ---------- ------------- ------------
       999     100001 eng           kod1
       999     100003 french        french
       999     100041 fin           Disney World

SCOTT@orcl_11g> 


-- using keep and dense_rank:
SCOTT@orcl_11g> SELECT s.dco_id, s.service_id,
  2  	    MAX (s.language_code) KEEP
  3  	      (DENSE_RANK FIRST ORDER BY is_default DESC NULLS LAST)
  4  	      AS language_code,
  5  	    MAX (s.service_name) KEEP
  6  	      (DENSE_RANK FIRST ORDER BY is_default DESC NULLS LAST)
  7  	      AS service_name
  8  FROM   service_name s, language l
  9  WHERE  s.language_code = l.language_code (+)
 10  GROUP  BY s.dco_id, s.service_id
 11  /

    DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
---------- ---------- ------------- ------------
       999     100001 eng           kod1
       999     100003 germ          german
       999     100041 fin           Disney World

SCOTT@orcl_11g> 

Re: Need a query [message #327213 is a reply to message #327124] Sun, 15 June 2008 02:55 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hi Barbara,

I think you didn’t get my problem.

Your dense query returns different servicename for the dco_id and service_id. It should
Exactly fetch the same row as it is in the Service_name table.

The condition you should check should be something like this.

a) Check which is the default language_code in the Language table( is_default=1). As of now it is ‘fin’. The default language may change in future.

b) Then go to Service_name table and check if any rows exist with default language_code for that dco_id and service_id.. If four rows exist then fetch any one row which has default language_code ‘fin’. Discard other rows
for that dco_id and service_id with other language_codes. If two or more rows exist with default language_code for that dco_id and service_id then also return any one row which has default language_code.

c)If no rows exist with the default language_code ‘fin’ for that dco_id and service_id , then return any one row which has other language_code.

d)Populate some different values for each dco_id and service_id . With 2 or more default language_codes and other language codes for that dco_id and service_id. And also populate records with no default language_code at all for that dco_id and service_id and check if your query works.

I need a query with logic like this.





Re: Need a query [message #327254 is a reply to message #327213] Sun, 15 June 2008 13:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
raviraviki wrote on Sun, 15 June 2008 00:55

Populate some different values for each dco_id and service_id . With 2 or more default language_codes and other language codes for that dco_id and service_id. And also populate records with no default language_code at all for that dco_id and service_id....



You need to provide accurate insert statements for such a dataset yourself and the results that you want that match. Also, you should have provided some sort of initial attempt yourself. You are being rather demanding. Nobody on this site gets paid to do your work and this is not official Oracle support. If that is what you expect, then you should use Metalink.
"Need a query" went wrong [message #327255 is a reply to message #327124] Sun, 15 June 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
raviraviki wrote on Sat, 14 June 2008 10:45
Please dont irritate people. The ultimate aim is to get the answer. Did u understand what I wrote. After all , its in human readable format.

Dont degrade or belittle others.

Don't you see the answer for posting such things? Twisted Evil Wink

Regards
Michel

[Updated on: Sun, 15 June 2008 13:45]

Report message to a moderator

Re: Need a query [message #327328 is a reply to message #327255] Mon, 16 June 2008 01:35 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hey Raam,

Iam not demanding.

Micheal: Is it a revenge?

Ravi

Re: Need a query [message #327332 is a reply to message #327328] Mon, 16 June 2008 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Micheal: Is it a revenge?

Take it as you want, for me it is joke but I can understand you take it bad.

Regards
Michel

[Updated on: Mon, 16 June 2008 02:11]

Report message to a moderator

Re: Need a query [message #327342 is a reply to message #327328] Mon, 16 June 2008 02:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
raviraviki wrote on Mon, 16 June 2008 08:35
Hey Raam,

Iam not demanding.



Who is Raam?
Re: Need a query [message #327345 is a reply to message #327342] Mon, 16 June 2008 03:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Who is Raam?



Must be "u"s second cousin or something, as far as I understood this thread.
Re: Need a query [message #327347 is a reply to message #327342] Mon, 16 June 2008 03:06 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Micheal: Is it a revenge?

Not sure that there is a Micheal Involved either Smile
Re: Need a query [message #327359 is a reply to message #327255] Mon, 16 June 2008 03:59 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Raam is our Hindu God!

Dunno whether Raam and Michael are involved . But why Frank,Thomas and Pebble are involved in this? Laughing
Re: Need a query [message #327360 is a reply to message #327255] Mon, 16 June 2008 04:03 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Thanks all for your effort especially BarBara atleast he tried to some extent.
Re: Need a query [message #327361 is a reply to message #327359] Mon, 16 June 2008 04:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Pebble? Who is Pebble?
Quote:
But why Frank,Thomas and Pebble are involved in this?

Because it is a Public forum
Re: Need a query [message #327362 is a reply to message #327255] Mon, 16 June 2008 04:05 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Dear pablolee,

Your name sounds like Pebble!
Re: Need a query [message #327363 is a reply to message #327362] Mon, 16 June 2008 04:08 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Your name sounds like Pebble!

No, it really doesn't.
Pebble would phonetically end with bal or bil depending on your pronounciation, whereas Pablolee phoneticaly ends with lee. bal, bil, lee. Quite different sounding.

Based on your soundex I'm gueesing that your name maybe sounds like Rimmer?
Re: Need a query [message #327364 is a reply to message #327360] Mon, 16 June 2008 04:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
raviraviki wrote on Mon, 16 June 2008 11:03
Thanks all for your effort especially BarBara atleast he tried to some extent.

eehh.. Barbara is a she.
Re: Need a query [message #327365 is a reply to message #327254] Mon, 16 June 2008 04:16 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have split this topic; please, continue your chat in "Need a query" went wrong topic moved to Community Hangout forum.
Re: Need a query [message #327370 is a reply to message #327124] Mon, 16 June 2008 04:32 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hey Littlefoot,

You are the right person to answer this query. Please try and give me an answer

Ravi
Re: Need a query [message #327389 is a reply to message #327370] Mon, 16 June 2008 05:01 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
raviraviki can you please post the create table and insert statement scripts and the query that you have tried and o/p you require.

It will help in solving your problem quickly.

If Michel or any other senior member of this forum say something.It does not mean that you start arguing with them.They are trying to solve your problem with free of cost,but they need data in formatted way.

Regards,
Rajat Ratewal
Re: Need a query [message #327420 is a reply to message #327124] Mon, 16 June 2008 06:07 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
create table Service_name(dco_id integer,service_id integer, language_code varchar2(10), service_name varchar2(50));

insert into service_name values (999,100041,'fin','Disney World');
insert into service_name values (999,100041,'eng','swathi1');
insert into service_name values (999,100001,'eng','kod1');
insert into service_name values (999,100001,'chin','chinese');
insert into service_name values (999,100003,'germ','german');
insert into service_name values (999,100003,'french','french');
insert into service_name values(888,100005,'eng','english');
insert into service_name values(888,100005,'fin','Finnish');
insert into service_name values(888,100005,'germ','german');
insert into service_name values(888,100005,'fin','finish2');

create table language(language_key integer, language_code varchar2(10),language_name varchar2(15),is_default integer);

insert into language values (1,'eng','English',0);
insert into language values (2,'fin','Finnish',0);
insert into language values (3,'sve','Swedish',0);
insert into language values (4,'deu','German',0);


Re: Need a query [message #327421 is a reply to message #327124] Mon, 16 June 2008 06:08 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
insert into language values (1,'eng','English',0);
insert into language values (2,'fin','Finnish',1);
insert into language values (3,'sve','Swedish',0);
insert into language values (4,'deu','German',0);


Re: Need a query [message #327422 is a reply to message #327124] Mon, 16 June 2008 06:13 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
I think you had understood the description.Iam fed up with the arguments with them. If you try and get a query ,please share with me.

Iam trying to do it with Pl/sql.
Re: Need a query [message #327437 is a reply to message #327422] Mon, 16 June 2008 07:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
raviraviki wrote on Mon, 16 June 2008 13:13
I think you had understood the description.Iam fed up with the arguments with them. If you try and get a query ,please share with me.

If you are, then please read and follow the Forum guidelines or hire a payed help.
Re: Need a query [message #327440 is a reply to message #327422] Mon, 16 June 2008 07:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Iam fed up with the arguments with them.

Somebody sort this man out with a full refund.
Re: Need a query [message #327456 is a reply to message #327124] Mon, 16 June 2008 08:15 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
When people dont know the answer ,they try to get into arguments.
Re: Need a query [message #327457 is a reply to message #327456] Mon, 16 June 2008 08:21 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
raviraviki wrote on Mon, 16 June 2008 06:15
When people dont know the answer ,they try to get into arguments.

You are correct. Please request your refund from /dev/null
Re: Need a query [message #327458 is a reply to message #327365] Mon, 16 June 2008 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot wrote on Mon, 16 June 2008 11:16
I have split this topic; please, continue your chat in "Need a query" went wrong topic moved to Community Hangout forum.

As the discussion goes on here and not in the topic created in Community Hangout forum, I remerged the 2.

Regards
Michel
Re: Need a query [message #327466 is a reply to message #327213] Mon, 16 June 2008 08:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

b) Discard other rows
for that dco_id and service_id with other language_codes.
d) Populate some different values for each dco_id and service_id


What do you mean by the above two statements ? Did you try the query what @Barbara has given ? If you think the query is not giving you the result why don't you post the data and the output and explain why it is not the output you are expecting rather than whining about comments made by other forum members. For example look at the comments you have made.

Quote:

Your name sounds like Pebble!
Iam not demanding.
Micheal: Is it a revenge?
Read the description properly. Understand what I wrote and then
give a reply
Please dont irritate people. The ultimate aim is to get the answer. Did u understand what I wrote. After all , its in human readable format.

Dont degrade or belittle others.



Quote:

The ultimate aim is to get the answer.

Especially this comment I am not able to see any of your post contributing to the above quote you have mentioned.

Regards

Raj
Re: Need a query [message #327507 is a reply to message #327421] Mon, 16 June 2008 11:32 Go to previous messageGo to previous message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
raviraviki wrote

The result set should like this:

DCO_ID	 SERVICE_ID	LANGUAGE_CODE	  SERVICE_NAME
999	 100041	        fin	           Disney World
999	 100001	        eng	           kod1
999	 100003	        germ	           german




Those are the same rows that my second query returned, just not in the same order:

    DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
---------- ---------- ------------- ------------
       999     100001 eng           kod1
       999     100003 germ          german
       999     100041 fin           Disney World


raviraviki wrote

Your dense query returns different servicename for the dco_id and service_id.



Not true.

raviraviki wrote

It should Exactly fetch the same row as it is in the Service_name table.



It did.


You did not provide the results that you want to match the new insert statements that you provided. You need to provide an example of the result that you want from the query on the data from the new insert statements, not just describe it in words, that is different from what my queries provide below. If you are getting different results, then you must have some different data or be doing something different from what I am doing. You need to post a copy and paste of an actual run, as I have done, including the query, with line numbers, and results. I hope you realize that if you had just posted the complete information properly as per the forum guidelines three days ago, that you would have had the complete answer three days ago. The only thing slowing down this process is you. You may already have the answer you need and just not recognize it.

-- the create table and insert statements that you provided:
SCOTT@orcl_11g> create table Service_name(dco_id integer,service_id integer, language_code varchar2(10), service_name varchar2(50));

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> insert into service_name values (999,100041,'fin','Disney World');

1 row created.

SCOTT@orcl_11g> insert into service_name values (999,100041,'eng','swathi1');

1 row created.

SCOTT@orcl_11g> insert into service_name values (999,100001,'eng','kod1');

1 row created.

SCOTT@orcl_11g> insert into service_name values (999,100001,'chin','chinese');

1 row created.

SCOTT@orcl_11g> insert into service_name values (999,100003,'germ','german');

1 row created.

SCOTT@orcl_11g> insert into service_name values (999,100003,'french','french');

1 row created.

SCOTT@orcl_11g> insert into service_name values(888,100005,'eng','english');

1 row created.

SCOTT@orcl_11g> insert into service_name values(888,100005,'fin','Finnish');

1 row created.

SCOTT@orcl_11g> insert into service_name values(888,100005,'germ','german');

1 row created.

SCOTT@orcl_11g> insert into service_name values(888,100005,'fin','finish2');

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> create table language(language_key integer, language_code varchar2(10),language_name varchar2(15),is_default integer);

Table created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> insert into language values (1,'eng','English',0);

1 row created.

SCOTT@orcl_11g> insert into language values (2,'fin','Finnish',1);

1 row created.

SCOTT@orcl_11g> insert into language values (3,'sve','Swedish',0);

1 row created.

SCOTT@orcl_11g> insert into language values (4,'deu','German',0);

1 row created.

SCOTT@orcl_11g> 
SCOTT@orcl_11g> commit;

Commit complete.

SCOTT@orcl_11g> 


-- the data that the provided statements produces:
SCOTT@orcl_11g> COLUMN language_code FORMAT A13
SCOTT@orcl_11g> COLUMN service_name  FORMAT A12
SCOTT@orcl_11g> SELECT * FROM service_name
  2  /

    DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
---------- ---------- ------------- ------------
       999     100041 fin           Disney World
       999     100041 eng           swathi1
       999     100001 eng           kod1
       999     100001 chin          chinese
       999     100003 germ          german
       999     100003 french        french
       888     100005 eng           english
       888     100005 fin           Finnish
       888     100005 germ          german
       888     100005 fin           finish2

10 rows selected.

SCOTT@orcl_11g> SELECT * FROM language
  2  /

LANGUAGE_KEY LANGUAGE_CODE LANGUAGE_NAME   IS_DEFAULT
------------ ------------- --------------- ----------
           1 eng           English                  0
           2 fin           Finnish                  1
           3 sve           Swedish                  0
           4 deu           German                   0


-- queries previously provided using the new data:
SCOTT@orcl_11g> SELECT dco_id, service_id, language_code, service_name
  2  FROM   (SELECT s.dco_id, s.service_id, s.language_code, s.service_name,
  3  		    l.is_default,
  4  		    ROW_NUMBER () OVER
  5  		      (PARTITION BY s.dco_id, s.service_id
  6  		       ORDER BY l.is_default DESC NULLS LAST) AS rn
  7  	     FROM   service_name s, language l
  8  	     WHERE  s.language_code = l.language_code (+))
  9  WHERE  rn = 1
 10  /

    DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
---------- ---------- ------------- ------------
       888     100005 fin           finish2
       999     100001 eng           kod1
       999     100003 french        french
       999     100041 fin           Disney World

SCOTT@orcl_11g> SELECT s.dco_id, s.service_id,
  2  	    MAX (s.language_code) KEEP
  3  	      (DENSE_RANK FIRST ORDER BY is_default DESC NULLS LAST)
  4  	      AS language_code,
  5  	    MAX (s.service_name) KEEP
  6  	      (DENSE_RANK FIRST ORDER BY is_default DESC NULLS LAST)
  7  	      AS service_name
  8  FROM   service_name s, language l
  9  WHERE  s.language_code = l.language_code (+)
 10  GROUP  BY s.dco_id, s.service_id
 11  /

    DCO_ID SERVICE_ID LANGUAGE_CODE SERVICE_NAME
---------- ---------- ------------- ------------
       888     100005 fin           finish2
       999     100001 eng           kod1
       999     100003 germ          german
       999     100041 fin           Disney World

SCOTT@orcl_11g> 


Previous Topic: diff between rollup and cube
Next Topic: SQL Tunning
Goto Forum:
  


Current Time: Tue Dec 06 02:53:24 CST 2016

Total time taken to generate the page: 0.07724 seconds