Home » SQL & PL/SQL » SQL & PL/SQL » Logic (Oracle, 10g, Windows server 2003)
Logic [message #442263] Sat, 06 February 2010 04:22 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
Following is script for table creation and data creation
in it.


create table senrating(seniority varchar2(10),rating varchar2(4),id_currency number,effdt date);

Table created.

Insert into senrating values('Samurai','+A',1,To_date('01-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Enigma','+B',1,To_date('02-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Samurai','+B',1,To_date('03-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Samurai','+AB',2,To_date('04-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Samurai','A',2,To_date('05-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Enigma','A',2,To_date('06-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Enigma','AA',2,To_date('07-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Enigma','AAB',1,To_date('08-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Cameron','BB',2,To_date('09-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.

Insert into senrating values('Cameron','BB',2,To_date('10-JAN-2010','DD-MM-YYYY'));

1 row(s) inserted.


Now i executed the following query


select a.seniority,a.rating local_curr_ra,a.id_currency,b.seniority,b.rating Foreign_curr_ra,b.id_currency
from (select seniority,rating,id_currency
      From(Select seniority,rating,id_currency,
                  rank()over (partition by seniority order by effdt desc) rk
           from senrating
           where id_currency = 1
          ) 
      where rk = 1) a,
    (Select seniority,rating,id_currency
     From (Select seniority,rating,id_currency,
                   rank()over (partition by seniority order by effdt desc) rk
           from senrating
           where id_currency = 2) 
     where rk = 1) b;

SEN_LCL  LCL_CURR_RA  ID_LCL  SEN_FGN  FGN_CURR_RA  ID_FGN       
Enigma   AAB          1       Cameron  BC           2           
Enigma   AAB          1       Enigma   AA           2           
Enigma   AAB          1       Samurai  A            2           
Samurai  +B           1       Cameron  BC           2
Samurai  +B           1       Enigma   AA           2
Samurai  +B           1       Samurai  A            2

6 rows selected


My requirement is that i need only one row of Enigma,Cameron,Samurai like


SEN_LCL  LCL_CURR_RA  ID_LCL  SEN_FGN  FGN_CURR_RA  ID_FGN       
Enigma   AAB          1       Enigma   AAB          2           
Samurai  +B           1       Samurai  +B           2
Cameron Null          1       Cameron  BC           2


Is this possible through a query?
If no please give me the reason?
If yes please design it

Regards,
Ritesh

[Updated on: Sat, 06 February 2010 04:44]

Report message to a moderator

Re: Logic [message #442268 is a reply to message #442263] Sat, 06 February 2010 05:07 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
What logic dictates resultset, it is not clear.
Re: Logic [message #442271 is a reply to message #442263] Sat, 06 February 2010 05:42 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
My requirement is

SEN_LCL  LCL_CURR_RA  ID_LCL  SEN_FGN  FGN_CURR_RA  ID_FGN       
Enigma   AAB          1       Enigma   AAB          2           
Samurai  +B           1       Samurai  +B           2
                              Cameron  BC           2



I need this recordset for frontend in my project which is an
Sharepoint.

My frontend developer wants Enigma or Samurai or Cameron record in single line

He want record in this ways
Enigma its lcl_curr_ra fgn_curr_ra


Here Cameron does not have lcl_curr_ra i.e. no record where
id_currency is 1, so suggest me a proper solution.


Regards,
Ritesh

[Updated on: Sat, 06 February 2010 05:45]

Report message to a moderator

Re: Logic [message #442279 is a reply to message #442271] Sat, 06 February 2010 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My requirement is that i need only one row of Enigma,Cameron,Samurai like
SEN_LCL  LCL_CURR_RA  ID_LCL  SEN_FGN  FGN_CURR_RA  ID_FGN       
Enigma   AAB          1       Enigma   AAB          2           
Samurai  +B           1       Samurai  +B           2
Cameron Null          1       Cameron  BC           2

Quote:
My requirement is
SEN_LCL  LCL_CURR_RA  ID_LCL  SEN_FGN  FGN_CURR_RA  ID_FGN       
Enigma   AAB          1       Enigma   AAB          2           
Samurai  +B           1       Samurai  +B           2
                              Cameron  BC           2

Are you sure of your requirements?

Regards
Michel

Re: Logic [message #442280 is a reply to message #442271] Sat, 06 February 2010 08:51 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
Sorry i was little confused about output.
My sharepoint developer wants resultset as


[b]SEN_LCL  LCL_CURR_RA  SEN_FGN_CURR_RA  [/b]
   Enigma      AB           AAB                     
   Samurai     +B           +B           
   Cameron     Null         BC           




Regards,
Ritesh
Re: Logic [message #442286 is a reply to message #442280] Sat, 06 February 2010 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure?
This is the third result and it is different of the 2 previous ones.

It should be better if you could clearly explain the result with words.

Regards
Michel
Re: Logic [message #442289 is a reply to message #442271] Sat, 06 February 2010 09:23 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
getritesh wrote on Sat, 06 February 2010 12:42
Here Cameron does not have lcl_curr_ra i.e. no record where
id_currency is 1, so suggest me a proper solution.

As the sample query did not contain any join condition, it used CROSS JOIN (cartesian product) for joining A and B subqueries.
Use FULL OUTER JOIN instead for joining A and B subqueries. See example e.g. here (the last one). 10g supports ANSI join syntax, so you can use it instead of the Oracle one which would be complicated in this case.
Re: Logic [message #442290 is a reply to message #442286] Sat, 06 February 2010 10:02 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
Explanation is as follows


SEN_LCL LCL_CURR_RA SEN_FGN_CURR_RA
Enigma AB AAB
Samurai +B +B
Cameron Null BC

Consider the record of Cameron

Cameron is seniority in table senrating which may two records
one for local rating (column rating)and another for foreign currency rating (column rating).

If column id_currency is equal to 1 then the value in rating
column is local currency rating (column rating)

If column id_currency is equal to 2 then the value in rating
column is local currency rating (column rating)

Cameron may have mutiple foreign rating records i.e. multiple
records with id_currency = 2 and seniority = Cameron, same
for local rating with id_currency = 1 and seniority Cameron.

But we have to display only one Cameron record with maximum
effdt, that is why i used rank().

I hope u will understand

Regards,
Ritesh
Re: Logic [message #442296 is a reply to message #442290] Sat, 06 February 2010 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If column id_currency is equal to 1 then the value in rating
column is local currency rating (column rating)

If column id_currency is equal to 2 then the value in rating
column is local currency rating (column rating)

So if it is 1 or 2 it is local currency and rating
So what is "foreign currency rating"?

Quote:
Cameron may have mutiple foreign rating records i.e. multiple records with id_currency = 2

Does this not contradict the previous paragraph?

Quote:
I hope u will understand

Well, you explain what there is in the table but what should be the result, that is what is the requirement (in words)?

Regards
Michel
Re: Logic [message #442407 is a reply to message #442286] Sun, 07 February 2010 21:23 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,

My sharepoint developer want output (Final) as

SENIORITY LCL_CURR_RA SEN_FGN_CURR_RA
Enigma AB AAB
Samurai +B +B
Cameron Null BC

See Record 1
Enigma is seniority which has local currency rating as AB
It has foreign currency rating as AAB

See Record 2
Samurai is seniority which has local currency rating as +B
It has local currency rating as +B

See Record 3
Cameron is seniority which dont have local currency rating,
hence it displayed as Null.
It has foreign currency rating as AB.

Regards,
Ritesh
Re: Logic [message #442409 is a reply to message #442407] Sun, 07 February 2010 21:44 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
From your test case Provided....

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table senrating(seniority varchar2(10),rating varchar2(4),id_currency number,effdt date);

Table created.

SQL>
SQL> Insert into senrating values('Samurai','+A',1,To_date('01-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Enigma','+B',1,To_date('02-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Samurai','+B',1,To_date('03-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Samurai','+AB',2,To_date('04-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Samurai','A',2,To_date('05-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Enigma','A',2,To_date('06-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Enigma','AA',2,To_date('07-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL>
SQL> Insert into senrating values('Enigma','AAB',1,To_date('08-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Cameron','BB',2,To_date('09-JAN-2010','DD-MM-YYYY'));

1 row created.

SQL> Insert into senrating values('Cameron','BB',2,To_date('10-JAN-2010','DD-MM-YYYY'));

1 row created.


Quote:
SQL> select a.seniority,a.rating local_curr_ra,a.id_currency,b.seniority,b.rating Foreign_curr_ra,b.id_currency
  2  from (select seniority,rating,id_currency
  3        From(Select seniority,rating,id_currency,
  4                    rank()over (partition by seniority order by effdt desc) rk
  5             from senrating
  6             where id_currency = 1
  7            )
  8        where rk = 1) a,
  9      (Select seniority,rating,id_currency
 10       From (Select seniority,rating,id_currency,
 11                     rank()over (partition by seniority order by effdt desc) rk
 12             from senrating
 13             where id_currency = 2)
 14       where rk = 1) b;

SENIORITY  LOCA ID_CURRENCY SENIORITY  FORE ID_CURRENCY
---------- ---- ----------- ---------- ---- -----------
Enigma     AAB            1 Cameron    BB             2-------> How you got that BC
Enigma     AAB            1 Enigma     AA             2
Enigma     AAB            1 Samurai    A              2
Samurai    +B             1 Cameron    BB             2-------> How you got that BC
Samurai    +B             1 Enigma     AA             2
Samurai    +B             1 Samurai    A              2

6 rows selected.

SQL>



And i really did n`t understood this

Quote:
SENIORITY LCL_CURR_RA SEN_FGN_CURR_RA
Enigma AB AAB
Samurai +B +B
Cameron Null BC

See Record 1
Enigma is seniority which has local currency rating as AB
It has foreign currency rating as AAB

See Record 2
Samurai is seniority which has local currency rating as +B
It has local currency rating as +B

See Record 3
Cameron is seniority which dont have local currency rating,
hence it displayed as Null.
It has foreign currency rating as AB.


sriram Smile
Previous Topic: sub query
Next Topic: Procedure to do incremental update
Goto Forum:
  


Current Time: Mon Sep 26 21:28:26 CDT 2016

Total time taken to generate the page: 0.06190 seconds