Home » SQL & PL/SQL » SQL & PL/SQL » SQL Nested Query based on value of Dependent Column (SQL)
icon5.gif  SQL Nested Query based on value of Dependent Column [message #595400] Tue, 10 September 2013 22:32 Go to next message
nickz
Messages: 16
Registered: September 2013
Junior Member
In the below Table Structure, TB_Vehicles is the Master table and TB_Cars, TB_Bikes, TB_Cars are Satellite tables which hold more info about respective vehicles.

./fa/11097/0/

I want users to search using the Name. So, when users enter Name as 'Access', my query should give all information about that vehicle, including that from Satellite Tables (using both TB_Vehicles and TB_Scooters).

SELECT * 
FROM TB_Vehicles
WHERE V_NAME = 'Access';


Similarly, if user enters 'Linea', it should give info from TB_Vehicles and TB_Cars tables.

So, with V_NAME as input, I'll find V_TYPE from TB_Vehicles table, and using that, I need to identify which satellite table, I need to join to retrieve more info.

How do I do this? Can you please help me with the query? Do I need to use CASE or DECODE to achieve this?
Re: SQL Nested Query based on value of Dependent Column [message #595401 is a reply to message #595400] Tue, 10 September 2013 22:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2447
Registered: May 2013
Location: World Wide on the Web
Senior Member
Use a single SQL with all your tables, and with proper join conditions. Make sure you have proper constraints on the parent and child tables for data integrity. Your join condition should be on V_ID, V_YEAR, V_TYPE. You might need OUTER JOIN for all the join conditions of child tables.

You can do something like this(I used outer joins blindly since I just have your test data) -

SQL> CREATE TABLE tb_vehicles AS SELECT * FROM(
  2  WITH DATA AS (
  3  SELECT 'access' v_name, 10 v_id, 2004 v_year, 'scooter' v_type, 'black' v_colour from dual union ALL
  4  SELECT 'linea' v_name, 20 v_id, 2005 v_year, 'car' v_type, 'black' v_colour from dual)
  5  SELECT * from DATA);
 
Table created
SQL> CREATE TABLE tb_scooters AS SELECT * FROM(
  2  WITH DATA AS (
  3  SELECT 'access' v_name, 10 v_id, 2004 v_year, 'scooter' v_type from dual
  4  )
  5  SELECT * from DATA);
 
Table created
SQL> CREATE TABLE tb_cars AS SELECT * FROM(
  2  WITH DATA AS (
  3  SELECT 'linea' v_name, 20 v_id, 2005 v_year, 'car' v_type from dual
  4  )
  5  SELECT * from DATA);
 
Table created
SQL> CREATE TABLE tb_bike AS SELECT * FROM(
  2  WITH DATA AS (
  3  SELECT 'pulsar' v_name, 30 v_id, 2006 v_year, 'bike' v_type from dual
  4  )
  5  SELECT * from DATA);
 
Table created
SQL> SELECT TV.*
  2    FROM TB_VEHICLES TV, TB_SCOOTERS TS, TB_CARS TC, TB_BIKE TB
  3   WHERE TV.V_NAME = TS.V_NAME(+)
  4     AND TV.V_NAME = TC.V_NAME(+)
  5     AND TV.V_NAME = TB.V_NAME(+)
  6     AND TV.V_ID = TS.V_ID(+)
  7     AND TV.V_ID = TC.V_ID(+)
  8     AND TV.V_ID = TB.V_ID(+)
  9     AND TV.V_YEAR = TS.V_YEAR(+)
 10     AND TV.V_YEAR = TC.V_YEAR(+)
 11     AND TV.V_YEAR = TB.V_YEAR(+)
 12     AND TV.V_TYPE = TS.V_TYPE(+)
 13     AND TV.V_TYPE = TC.V_TYPE(+)
 14     AND TV.V_TYPE = TB.V_TYPE(+)
 15     AND TV.V_NAME = 'access';
 
V_NAME       V_ID     V_YEAR V_TYPE  V_COLOUR
------ ---------- ---------- ------- --------
access         10       2004 scooter black
 
SQL> 

[Updated on: Wed, 11 September 2013 00:45]

Report message to a moderator

Re: SQL Nested Query based on value of Dependent Column [message #595405 is a reply to message #595400] Wed, 11 September 2013 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: SQL Nested Query based on value of Dependent Column [message #595430 is a reply to message #595401] Wed, 11 September 2013 04:32 Go to previous messageGo to next message
nickz
Messages: 16
Registered: September 2013
Junior Member
Thanks Lalit!

Sorry I didn't mention earlier but I don't have the option of creating any extra table or view for this. It's a single dynamic SQL query, which should be able to fulfill the requirement.

The requirement here is to display only those columns which are applicable for that particular vehicle type. So, when user gives 'Access' as input, it should display all columns from TB_Vehicles table, and v_mileage from TB_Scooters table.

I was thinking whether this can be done using CASE or DECODE, where I first identify the type (V_TYPE) of the vehicle from TB_Vehicles table using the input name (V_NAME), and then based on this V_TYPE, I'll search the relevant table and display only relevant columns.

When Input Name is 'Access', then result should be:
V_NAME	V_ID	V_YEAR	V_TYPE	V_COLOR	V_AVAILABLE	V_MILEAGE
Access	20	2004	Scooter	Black	No	        50

When Input Name is 'i20', then result should be:
V_NAME	V_ID	V_YEAR	V_TYPE	V_COLOR	V_AVAILABLE	V_CAR_TYPE
i20	60	2012	Car	Red	Yes	        Sedan

Re: SQL Nested Query based on value of Dependent Column [message #595440 is a reply to message #595430] Wed, 11 September 2013 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sorry I didn't mention earlier but I don't have the option of creating any extra table or view for this.


But you ALREADY have these table otherwise how could you query them?

Quote:
The requirement here is to display only those columns which are applicable for that particular vehicle type.


If you have no table you have no column and so cannot display anything.

Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: SQL Nested Query based on value of Dependent Column [message #595446 is a reply to message #595440] Wed, 11 September 2013 05:53 Go to previous messageGo to next message
nickz
Messages: 16
Registered: September 2013
Junior Member
Hi Michel,

What I meant was I just have the following tables with me:

TB_Vehicles
TB_Scooters
TB_Bikes
TB_Cars

Other than the above, no other temporary tables or views can be created. My query should take the help of only the four tables as mentioned above and not use any view.

Requirement/Result/Output should be as I mentioned in my earlier post.

Please find attached the SQL script for table creation.
  • Attachment: Script.sql
    (Size: 2.50KB, Downloaded 44 times)
Re: SQL Nested Query based on value of Dependent Column [message #595451 is a reply to message #595446] Wed, 11 September 2013 06:05 Go to previous messageGo to next message
Maaher
Messages: 7054
Registered: December 2001
Senior Member
You didn't test your script, now did you?

Here's a corrected version:
 CREATE TABLE TB_SCOOTERS
   (	V_NAME    VARCHAR2(20), 
	V_ID      NUMBER(4) NOT NULL ENABLE, 
	V_YEAR    NUMBER(4), 
	V_TYPE    VARCHAR2(20), 
	V_MILEAGE NUMBER(4), 
	  CONSTRAINT TB_SCOOTERS_PK PRIMARY KEY (V_ID)
   );
 

 CREATE TABLE TB_BIKES
   (	V_NAME   VARCHAR2(20), 
	V_ID     NUMBER(4) NOT NULL ENABLE, 
	V_YEAR   NUMBER(4), 
	V_TYPE   VARCHAR2(20), 
	V_ENGINE NUMBER(4), 
	  CONSTRAINT TB_BIKES_PK PRIMARY KEY (V_ID)
   );
 

 CREATE TABLE TB_CARS
   (	V_NAME     VARCHAR2(20), 
	V_ID       NUMBER(4) NOT NULL ENABLE, 
	V_YEAR     NUMBER(4), 
	V_TYPE     VARCHAR2(20), 
	V_CAR_TYPE VARCHAR2(20), 
	  CONSTRAINT TB_CARS_PK PRIMARY KEY (V_ID)
   );
 
 CREATE TABLE TB_VEHICLES
   (	V_NAME      VARCHAR2(20), 
	V_ID        NUMBER(4) NOT NULL ENABLE, 
	V_YEAR      NUMBER(4), 
	V_TYPE      VARCHAR2(20), 
	V_COLOR     VARCHAR2(20), 
	V_AVAILABLE VARCHAR2(20), 
	  CONSTRAINT TB_VECHILES_PK PRIMARY KEY (V_ID)
   );
 


INSERT INTO TB_SCOOTERS (V_NAME, V_ID, V_YEAR, V_TYPE, V_MILEAGE)            VALUES ('Activa', '10', '2003', 'Scooter', '45');
INSERT INTO TB_SCOOTERS (V_NAME, V_ID, V_YEAR, V_TYPE, V_MILEAGE)            VALUES ('Access', '20', '2004', 'Scooter', '50');

INSERT INTO TB_BIKES    (V_NAME, V_ID, V_YEAR, V_TYPE, V_ENGINE)          VALUES ('Karizma', '30', '2001', 'Bike', '180');
INSERT INTO TB_BIKES    (V_NAME, V_ID, V_YEAR, V_TYPE, V_ENGINE)          VALUES ('Pulsar', '40', '2008', 'Bike', '150');

INSERT INTO TB_CARS     (V_NAME, V_ID, V_YEAR, V_TYPE, V_CAR_TYPE)        VALUES ('Linea', '50', '2011', 'Car', 'Sedan');
INSERT INTO TB_CARS     (V_NAME, V_ID, V_YEAR, V_TYPE, V_CAR_TYPE)        VALUES ('i20', '60', '2012', 'Car', 'Sedan');

INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Activa', '10', '2003', 'Scooter', 'Black', 'Yes');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Access', '20', '2004', 'Scooter', 'Black', 'No');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Karizma', '30', '2001', 'Bike', 'Red', 'Yes');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Pulsar', '40', '2008', 'Bike', 'Red', 'Yes');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('Linea', '50', '2011', 'Car', 'White', 'No');
INSERT INTO TB_VEHICLES (V_NAME, V_ID, V_YEAR, V_TYPE, V_COLOR, V_AVAILABLE) VALUES ('i20', '60', '2012', 'Car', 'Red', 'Yes');


COMMIT;



MHE

[Updated on: Wed, 11 September 2013 06:06]

Report message to a moderator

Re: SQL Nested Query based on value of Dependent Column [message #595452 is a reply to message #595446] Wed, 11 September 2013 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Other than the above, no other temporary tables or views can be created.


Lalit did not use any.

And for the script, Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Quote:
The requirement here is to display only those columns which are applicable for that particular vehicle type.



Cannot be done in a single query if the number of columns is different with the type.
If the number is the same one, the header can only be the same one for each type if you use a single query.
So what is the other output you can accept given it can't be done with your current requirements: several query or meaningless headers or...?

Regards
Michel
Re: SQL Nested Query based on value of Dependent Column [message #595464 is a reply to message #595452] Wed, 11 September 2013 06:19 Go to previous messageGo to next message
Maaher
Messages: 7054
Registered: December 2001
Senior Member
Michel has a valid point: the selected columns are different, depending on the type of vehicle. If you're willing to accept a "generic" last column, you could modify Lalit Kumar B's query and add an "generic" extra last column that is populated using a decode, as you already suggested yourself. The exact output, including column headers, cannot be generated in a single query.

Does that help?

MHE
Re: SQL Nested Query based on value of Dependent Column [message #595513 is a reply to message #595446] Wed, 11 September 2013 08:05 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
It would help to state in which tool you want to use that query and how the "result" shall be "displayed". As you want to "display" (what that means? it depends on the client tool) dynamic number/types of columns (if I understanding it correctly), you have to use dynamic SQL or use some dynamic feature of your client tool.

E.g. in sqlplus and simple output to terminal/file, I would use cursor variable, something like:
var out_cur refcursor

declare in_val integer := 2;
begin
  if in_val=1 then
    open :out_cur for select * from emp;
  elsif in_val=2 then
    open :out_cur for select * from dept;
  elsif in_val=3 then
    open :out_cur for select * from bonus;
  elsif in_val=4 then
    open :out_cur for select * from salgrade;
  end if;
end;
/

print out_cur

(note that I do not have your table, so I used standard scott user; I am also lazy to create procedure and run it afterwards, so I simply used the anonymous block; IN_VAL and OUT_CUR would be IN/OUT parameters of that procedure)
You may create the similar code in PL/SQL procedure e.g. here: http://www.orafaq.com/scripts/plsql/refcurs.txt
Re: SQL Nested Query based on value of Dependent Column [message #595520 is a reply to message #595464] Wed, 11 September 2013 08:42 Go to previous messageGo to next message
nickz
Messages: 16
Registered: September 2013
Junior Member
@Maaher: Sorry I just typed the script in a notepad, didn't test it.. I should have! Sad
@Michel: I am new to this forum. Will certainly keep that in mind when posting next time.

Quote:
I was thinking whether this can be done using CASE or DECODE, where I first identify the type (V_TYPE) of the vehicle from TB_Vehicles table using the input name (V_NAME), and then based on this V_TYPE, I'll search the relevant table and display only relevant columns.

Can this not be done?? This might be a dumb question to ask again, but just to clarify all my doubts. Suppose, I identify the V_TYPE from TB_Vehicles table, using the V_NAME, supplied as input by the user. Then for each CASE for each V_TYPE (Car/Bike/Scooter), I put a query inside it which will have a sub-query on the relevant table returning the relevant columns. And all this in a single query.
CASE V_TYPE
  WHEN 'Car' THEN <Query on TB_Cars>
  WHEN 'Bike' THEN <Query on TB_Bikes>
...


Another approach: Let's say I create a View with all columns from TB_Vehicles, TB_Cars, TB_Scooters, TB_Bikes. Now, what should be the query in this case if I just want to display only the NOT NULL columns, if V_NAME column matches the user given input?
WHERE V_NAME = 'Access'


[Updated on: Wed, 11 September 2013 08:45]

Report message to a moderator

Re: SQL Nested Query based on value of Dependent Column [message #595523 is a reply to message #595520] Wed, 11 September 2013 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
The root cause of this challenge is that the table "design" is flawed.
Table name or column name should NEVER be application data; like BIKE, SCOOTER, or CAR.
If the tables were normalized to Third Normal Form, then the coding solution would be trivial.

Re: SQL Nested Query based on value of Dependent Column [message #595526 is a reply to message #595523] Wed, 11 September 2013 09:03 Go to previous messageGo to next message
nickz
Messages: 16
Registered: September 2013
Junior Member
@BlackSwan: I apologize for any inconvenience. Actually, this is just a dummy scenario/table names I created since I can't post the original client tables/data. This is what I felt could replicate my scenario if not in a perfect way. So, I created such tables for easy understanding and didn't spend much time on constraint creation/relationships.
Re: SQL Nested Query based on value of Dependent Column [message #595528 is a reply to message #595526] Wed, 11 September 2013 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
The root cause of this challenge is that the table "design" is flawed.
Table name or column name should NEVER be application data; like JANUARY, FEBRUARY, or MARCH.
Table name or column name should NEVER be application data; like 2009, 2010, 2011, 2012, 2013.
Table name or column name should NEVER be application data; like FRANCE, GERMANY, SWITZERLAND
It does NOT matter what the actual names are. Data values should NEVER be part of table name or column name.
If the tables were normalized to Third Normal Form, then the coding solution would be trivial.



Re: SQL Nested Query based on value of Dependent Column [message #595529 is a reply to message #595520] Wed, 11 September 2013 09:17 Go to previous messageGo to next message
Maaher
Messages: 7054
Registered: December 2001
Senior Member
nickz wrote on Wed, 11 September 2013 15:42
Can this not be done??
It can be done, but not with a dynamic column header because a select statement can't have a dynamic layout.

Here's an example of what can be done. I've added a "V_SPECIFIC" column, containing type specific information. I agree with BlackSwan that the design is not optimal but sometimes we have to work with what we've got.

Note: I've created the tables using the script mentioned above.

SQL> -- Using Lalit Kumar B's query:
SQL> Column v_name format a10
SQL> Column v_id   format 9999
SQL> Column v_year format 99999
SQL> Column v_type format a10
SQL> Column v_color format a10
SQL> Column v_specific format a10
SQL> Select tv.v_name
  2       , tv.v_id
  3       , tv.v_year
  4       , tv.v_type
  5       , tv.v_color
  6       , tv.v_available
  7       , decode( tv.v_type
  8               , 'Scooter', To_Char(ts.v_mileage)
  9               , 'Bike'   , To_Char(tb.v_engine)
 10               , 'Car'    , tc.v_car_type
 11               ) v_specific
 12  From tb_vehicles tv
 13     , tb_scooters ts
 14     , tb_cars tc
 15     , tb_bikes tb
 16  Where tv.v_name = ts.v_name(+)
 17  And   tv.v_name = tc.v_name(+)
 18  And   tv.v_name = tb.v_name(+)
 19  And   tv.v_id = ts.v_id(+)
 20  And   tv.v_id = tc.v_id(+)
 21  And   tv.v_id = tb.v_id(+)
 22  And   tv.v_year = ts.v_year(+)
 23  And   tv.v_year = tc.v_year(+)
 24  And   tv.v_year = tb.v_year(+)
 25  And   tv.v_type = ts.v_type(+)
 26  And   tv.v_type = tc.v_type(+)
 27  And   tv.v_type = tb.v_type(+)
 28  And   lower(tv.v_name) = 'access'
 29  /

V_NAME      V_ID V_YEAR V_TYPE     V_COLOR    V_AVAILABLE          V_SPECIFIC
---------- ----- ------ ---------- ---------- -------------------- ----------
Access        20   2004 Scooter    Black      No                   50


MHE
Re: SQL Nested Query based on value of Dependent Column [message #595531 is a reply to message #595520] Wed, 11 September 2013 09:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you accept a generic column name, you can do it both with Lalit's query or with your CASE statement.
SQL> SELECT TV.*,
  2         case TV.V_TYPE
  3           when 'Car' then tc.V_CAR_TYPE
  4           when 'Scooter' then to_char(ts.V_MILEAGE)
  5           when 'Bike' then to_char(tb.V_ENGINE)
  6         end add_on
  7  FROM TB_VEHICLES TV, TB_SCOOTERS TS, TB_CARS TC, TB_BIKES TB
  8  WHERE TV.V_NAME = TS.V_NAME(+)
  9    AND TV.V_NAME = TC.V_NAME(+)
 10    AND TV.V_NAME = TB.V_NAME(+)
 11    AND TV.V_ID = TS.V_ID(+)
 12    AND TV.V_ID = TC.V_ID(+)
 13    AND TV.V_ID = TB.V_ID(+)
 14    AND TV.V_YEAR = TS.V_YEAR(+)
 15    AND TV.V_YEAR = TC.V_YEAR(+)
 16    AND TV.V_YEAR = TB.V_YEAR(+)
 17    AND TV.V_TYPE = TS.V_TYPE(+)
 18    AND TV.V_TYPE = TC.V_TYPE(+)
 19    AND TV.V_TYPE = TB.V_TYPE(+)
 20    AND TV.V_NAME = '&1'
 21  /
Enter value for 1: Access
V_NAME                     V_ID     V_YEAR V_TYPE               V_COLOR              V_AVAILABLE          ADD_ON
-------------------- ---------- ---------- -------------------- -------------------- -------------------- ----------
Access                       20       2004 Scooter              Black                No                   50

1 row selected.

SQL> /
Enter value for 1: i20
V_NAME                     V_ID     V_YEAR V_TYPE               V_COLOR              V_AVAILABLE          ADD_ON
-------------------- ---------- ---------- -------------------- -------------------- -------------------- ----------
i20                          60       2012 Car                  Red                  Yes                  Sedan

1 row selected.

SQL> SELECT TV.*,
  2         case TV.V_TYPE
  3           when 'Car' then
  4             (select tc.V_CAR_TYPE from TB_CARS TC 
  5              where TV.V_NAME = TC.V_NAME and TV.V_ID = TC.V_ID and TV.V_TYPE = TC.V_TYPE)
  6           when 'Scooter' then 
  7             (select to_char(ts.V_MILEAGE) from TB_SCOOTERS TS
  8              where TV.V_NAME = TS.V_NAME and TV.V_ID = TS.V_ID and TV.V_TYPE = TS.V_TYPE)
  9           when 'Bike' then 
 10             (select to_char(tb.V_ENGINE) from TB_BIKES TB
 11              where TV.V_NAME = TB.V_NAME and TV.V_ID = TB.V_ID and TV.V_TYPE = TB.V_TYPE)
 12         end add_on
 13  FROM TB_VEHICLES TV
 14  WHERE TV.V_NAME = '&1'
 15  /
Enter value for 1: Access
V_NAME                     V_ID     V_YEAR V_TYPE               V_COLOR              V_AVAILABLE          ADD_ON
-------------------- ---------- ---------- -------------------- -------------------- -------------------- ----------
Access                       20       2004 Scooter              Black                No                   50

1 row selected.

SQL> /
Enter value for 1: i20
V_NAME                     V_ID     V_YEAR V_TYPE               V_COLOR              V_AVAILABLE          ADD_ON
-------------------- ---------- ---------- -------------------- -------------------- -------------------- ----------
i20                          60       2012 Car                  Red                  Yes                  Sedan

1 row selected.

Regards
Michel
Re: SQL Nested Query based on value of Dependent Column [message #595537 is a reply to message #595531] Wed, 11 September 2013 09:56 Go to previous messageGo to next message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

I know this is ugly. But still, can we have this? Confused Embarassed

SET HEADING OFF

SELECT
	  'V_NAME',
	  'V_ID',
	  'V_YEAR',
	  'V_TYPE',
	  'V_COLOR',
	  'V_AVAILABLE',
	  CASE TV.V_TYPE
		  WHEN 'Car'
		  THEN
			  ( SELECT 'V_CAR_TYPE' FROM DUAL )
		  WHEN 'Scooter'
		  THEN
			  ( SELECT 'V_MILEAGE' FROM DUAL )
		  WHEN 'Bike'
		  THEN
			  ( SELECT 'V_ENGINE' FROM DUAL )
	  END
		  AS HEADERR
FROM
	  TB_VEHICLES TV
WHERE
	  TV.V_NAME = '&1'
UNION
SELECT
	  TO_CHAR ( V_NAME ),
	  TO_CHAR ( V_ID ),
	  TO_CHAR ( V_YEAR ),
	  TO_CHAR ( V_TYPE ),
	  TO_CHAR ( V_COLOR ),
	  TO_CHAR ( V_AVAILABLE ),
	  CASE TV.V_TYPE
		  WHEN 'Car'
		  THEN
			  ( SELECT
					TC.V_CAR_TYPE
			   FROM
					TB_CARS TC
			   WHERE
					    TV.V_NAME = TC.V_NAME
					AND TV.V_ID = TC.V_ID
					AND TV.V_TYPE = TC.V_TYPE )
		  WHEN 'Scooter'
		  THEN
			  ( SELECT
					TO_CHAR ( TS.V_MILEAGE )
			   FROM
					TB_SCOOTERS TS
			   WHERE
					    TV.V_NAME = TS.V_NAME
					AND TV.V_ID = TS.V_ID
					AND TV.V_TYPE = TS.V_TYPE )
		  WHEN 'Bike'
		  THEN
			  ( SELECT
					TO_CHAR ( TB.V_ENGINE )
			   FROM
					TB_BIKES TB
			   WHERE
					    TV.V_NAME = TB.V_NAME
					AND TV.V_ID = TB.V_ID
					AND TV.V_TYPE = TB.V_TYPE )
	  END
		  ADD_ON
FROM
	  TB_VEHICLES TV
WHERE
	  TV.V_NAME = '&1'
/

Enter value for 1: i20
V_NAME                     V_ID     V_YEAR V_TYPE               V_COLOR              V_AVAILABLE          V_CAR_TYPE
i20                        60       2012   Car                  Red                  Yes                  Sedan

1 row selected.

SQL> /

Enter value for 1: Access
V_NAME                     V_ID     V_YEAR V_TYPE               V_COLOR              V_AVAILABLE          V_MILEAGE
Access                     20       2004   Scooter              Black                No                   50

1 row selected.


[Updated on: Wed, 11 September 2013 09:57]

Report message to a moderator

Re: SQL Nested Query based on value of Dependent Column [message #595538 is a reply to message #595537] Wed, 11 September 2013 10:11 Go to previous messageGo to next message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

Also this one works...

WITH TEMP_VEHICLES
	AS (SELECT
			 TV.V_NAME,
			 TV.V_ID,
			 TV.V_YEAR,
			 TV.V_TYPE,
			 TV.V_COLOR,
			 TV.V_AVAILABLE,
			 TO_CHAR ( TC.V_CAR_TYPE ) V_CAR_TYPE 
	    FROM
			 TB_CARS TC,
			 TB_VEHICLES TV
	    WHERE
				TV.V_NAME = TC.V_NAME
			 AND TV.V_ID = TC.V_ID
			 AND TV.V_TYPE = TC.V_TYPE
	    UNION
	    SELECT
			 TV.V_NAME,
			 TV.V_ID,
			 TV.V_YEAR,
			 TV.V_TYPE,
			 TV.V_COLOR,
			 TV.V_AVAILABLE,
			 TO_CHAR ( TC.V_MILEAGE ) V_MILEAGE 
	    FROM
			 TB_SCOOTERS TC,
			 TB_VEHICLES TV
	    WHERE
				TV.V_NAME = TC.V_NAME
			 AND TV.V_ID = TC.V_ID
			 AND TV.V_TYPE = TC.V_TYPE
	    UNION
	    SELECT
			 TV.V_NAME,
			 TV.V_ID,
			 TV.V_YEAR,
			 TV.V_TYPE,
			 TV.V_COLOR,
			 TV.V_AVAILABLE,
			 TO_CHAR ( TC.V_ENGINE ) V_ENGINE 
	    FROM
			 TB_BIKES TC,
			 TB_VEHICLES TV
	    WHERE
				TV.V_NAME = TC.V_NAME
			 AND TV.V_ID = TC.V_ID
			 AND TV.V_TYPE = TC.V_TYPE)
SELECT
	  *
FROM
	  TEMP_VEHICLES
WHERE
	  V_NAME = '&1'
Re: SQL Nested Query based on value of Dependent Column [message #595539 is a reply to message #595537] Wed, 11 September 2013 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I know this is ugly. But still, can we have this?


You faked your output.
It can't return "1 row selected" when you actually get 2.

Regards
Michel
Re: SQL Nested Query based on value of Dependent Column [message #595540 is a reply to message #595538] Wed, 11 September 2013 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Also this one works...


But is quite bad compare to the previous ones.
In addition, you should use UNION ALL and not UNION.

Regards
Michel

Re: SQL Nested Query based on value of Dependent Column [message #595542 is a reply to message #595539] Wed, 11 September 2013 10:30 Go to previous messageGo to next message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

Yup.. I did faked it since I use toad. Razz
icon14.gif  Re: SQL Nested Query based on value of Dependent Column [message #595543 is a reply to message #595540] Wed, 11 September 2013 10:31 Go to previous messageGo to next message
realspirituals
Messages: 27
Registered: September 2011
Location: Italy
Junior Member

Point noted mate. Wink
Re: SQL Nested Query based on value of Dependent Column [message #595544 is a reply to message #595540] Wed, 11 September 2013 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Although it is quite bad you can improve it with the help of the optimizer smartness using (and and adding the missing condition on year and fixing other things):
WITH TEMP_VEHICLES
	AS (SELECT
			 TV.V_NAME,
			 TV.V_ID,
			 TV.V_YEAR,
			 TV.V_TYPE,
			 TV.V_COLOR,
			 TV.V_AVAILABLE,
			 V_CAR_TYPE  ADD_ON
	    FROM
			 TB_CARS TC,
			 TB_VEHICLES TV
	    WHERE
				TV.V_NAME = TC.V_NAME
			 AND TV.V_ID = TC.V_ID
			 AND TV.V_YEAR = TC.V_YEAR
			 AND TV.V_TYPE = TC.V_TYPE
			 AND TV.V_TYPE = 'Car'
                         AND TC.V_TYPE = 'Car'
	    UNION ALL
	    SELECT
			 TV.V_NAME,
			 TV.V_ID,
			 TV.V_YEAR,
			 TV.V_TYPE,
			 TV.V_COLOR,
			 TV.V_AVAILABLE,
			 TO_CHAR ( TC.V_MILEAGE ) V_MILEAGE 
	    FROM
			 TB_SCOOTERS TC,
			 TB_VEHICLES TV
	    WHERE
				TV.V_NAME = TC.V_NAME
			 AND TV.V_ID = TC.V_ID
			 AND TV.V_YEAR = TC.V_YEAR
			 AND TV.V_TYPE = TC.V_TYPE
			 AND TV.V_TYPE = 'Scooter'
                         AND TC.V_TYPE = 'Scooter'
	    UNION ALL
	    SELECT
			 TV.V_NAME,
			 TV.V_ID,
			 TV.V_YEAR,
			 TV.V_TYPE,
			 TV.V_COLOR,
			 TV.V_AVAILABLE,
			 TO_CHAR ( TC.V_ENGINE ) V_ENGINE 
	    FROM
			 TB_BIKES TC,
			 TB_VEHICLES TV
	    WHERE
				TV.V_NAME = TC.V_NAME
			 AND TV.V_ID = TC.V_ID
			 AND TV.V_YEAR = TC.V_YEAR
			 AND TV.V_TYPE = TC.V_TYPE
                         AND TV.V_TYPE = 'Bike'
                         AND TC.V_TYPE = 'Bike')
SELECT
	  *
FROM
	  TEMP_VEHICLES
WHERE
	  V_NAME = '&1'
/


Regards
Michel
Re: SQL Nested Query based on value of Dependent Column [message #595618 is a reply to message #595544] Thu, 12 September 2013 08:06 Go to previous messageGo to next message
nickz
Messages: 16
Registered: September 2013
Junior Member
Thanks All! My problem is solved... I used the query using CASE by Michel, with modifications as required in my real implementation. Thanks again! Smile
Re: SQL Nested Query based on value of Dependent Column [message #595682 is a reply to message #595618] Thu, 12 September 2013 13:47 Go to previous message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But most of the time use of subquery in SELECT clause is an abuse and lead to bad performances.
It should be better to learn SQL to developers (and for instance with the help of Stephane Faroult latest book and courses).

Regards
Michel
Previous Topic: Query to delete two weeks old data.
Next Topic: All rows with denormalized values
Goto Forum:
  


Current Time: Sat Oct 25 01:32:05 CDT 2014

Total time taken to generate the page: 0.05508 seconds