SQL Query Help

excelcus · Feb 20, 2012

Hi!
I have a derived table in which there is an outer select statement and in FROM clause I have 2 more selects. I need to add a colum to outer select based on table in FROM clause. Now the other queries seemed pretty simple but here is the issue with this one. On DB level the table from which I have to add column to current derived table is in the FROM select clause of this derived table. Am I supposed to add this column if this table from which this column is coming, is in FROM select clause of the derived table? If I do then do I have to add this column to select clause in FROM clause first and then subsequently add to outer select? Thanks.

carp · Feb 20, 2012

It would help immensely if you would include your query. If I am understanding your question correctly, what you have is something like

Code:

SELECT t1.a, t2.b
FROM (SELECT a,c FROM t_one) t1
             INNER JOIN (SELECT b,c FROM t_two) t2
                    ON t1.c = t2.c;

and you want to add a column to your outer select. If this is the case, then yes, you need to make the column available to the outer select:

Code:

SELECT t1.a, t2.b, t2.d
FROM (SELECT a, c FROM t_one) t1
             INNER JOIN (SELECT b,c,d FROM t_two) t2
                    ON t1.c = t2.c;

Please let us know if I have summarized your question correctly and if this answers it.

excelcus · Feb 20, 2012

SELECT Table1."Customer ID",
CONCAT(CONCAT(CONCAT('FY ',Table1."Period1"), ' to FY '), Table2."Period1") AS "YEAR_PERIOD",
ROUND(((Table1."SALES" - Table2."SALES")/Table2."SALES") * 100, 2) AS "PERCENT",
Table1."NEW COLUMN"
FROM
(SELECT "Customer ID", TO_NUMBER(SUBSTR("Period1", 3, 4)) "PERIOD1", SALES, "NEW COLUMN"
FROM BO."Company Sales" A1
WHERE TO_NUMBER(SUBSTR("Period1", 3, 4)) =
(SELECT MAX(TO_NUMBER(SUBSTR("Period1", 3, 4))) MAXPERIOD FROM BO."Customer Sales" WHERE "Period1 End Month" IS NULL GROUP BY "Customer ID" HAVING
"Customer ID" = BO."Customer ID" )
AND (NOT "Period1" = 'P')
AND "Fiscal Period End Month" IS NULL) TABLE 1,
(SELECT "Customer ID", TO_NUMBER(SUBSTR("Period1", 3, 4)) "Period1", SALES, "NEW COLUMN"
FROM BO."Company Sales" A2
WHERE TO_NUMBER(SUBSTR("Period1", 3, 4)) =
(SELECT MAX(TO_NUMBER(SUBSTR("Period1", 3, 4)))-1 PREVPERIOD FROM BO."Company Sales" WHERE "Period1 End Month" IS NULL GROUP BY "Customer ID" HAVING
"Customer ID" = A2."Customer ID" )
AND (NOT "Fiscal Period" = 'FY')
AND "Period1 End Month" IS NULL) Table2
WHERE Table1."Customer ID" = Table2."Customer ID"
AND Table1."NEW COLUMN"='Y'

I have the above derived table where I need to add this column called "NEW COLUMN". But I need to add NEW COLUMN only if the table from which it is coming from is present in the derived table. In this case the table is present in derived table's FROM clause called "Customer Sales". But since it is in another select therefore I am confused. So I have added this NEW COLUMN first in both selects in FROM and then outer select.Also, I need to add this NEW COLUMN as a condition at the end, not sure if I have done that correctly or not. Please let me know if this is the right way to do it since I am concerned about the results.

carp · Feb 21, 2012

Yes, this looks correct. However, since you are only referencing table1.newColumn, you do not need to include it in your definition of table2.

excelcus · Feb 21, 2012

ok great. Thanks a lot Carp.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL Query Help

excelcus

Technical User

carp

MIS

excelcus

Technical User

carp

MIS

excelcus

Technical User

Similar threads

Part and Inventory Search

Sponsor