×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Query Help

SQL Query Help

SQL Query Help

(OP)
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.  

RE: SQL Query Help

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.
 

RE: SQL Query Help

(OP)
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.  

RE: SQL Query Help

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

RE: SQL Query Help

(OP)
ok great. Thanks a lot Carp.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close