1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Subquery in multiple joins

Discussion in 'SQL PL/SQL' started by Biswadeep, Sep 27, 2015.

  1. Biswadeep

    Biswadeep Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    I have a requirement in a Query.
    The Original query is somewhat like this:
    Select A.col1,
    A.col2,
    B.col1,
    B.col2,
    C.col1.
    C.col2,
    C.col3
    ,D.Col1,
    D.col2
    .,
    .,
    .,
    From
    A Left Outer Join B on (A.col1 = B.col1)
    Left Outer Join C on (A.col1 = B.col1)
    left outer join D on (C.col2 = D.col1)
    left outer join E on (D.col1 = E.col1)
    left outer join F on (E.col1 =F.col1)
    .
    .. so on

    where
    some filter condition..


    now i have to add two subqueries in the query above.

    Logic of 1st Subquery is
    ( if B.col3=C.col3 then C.col3 else max(C.col3))

    Logic for Second Subquery is (If B.col3 = (If D.col3 = 'A' then E.col3 else if D.col3='B' then F.col3) then B.col3 )
    Else Max ((If D.col3 = 'A' then E.col3 else if D.col3='B' then F.col3) )


    How to incorporate these Sub-queries into the Whole Above Query.
    I have incorporated the logic thru Two Simple Straightforward Case Statements in the Main Select Query itself with other columns which is working in oracle and but not working in the reporting tool where the query needs to be used so now it needs to be done using sub-queries as that's work in the reporting tool too.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Rewriting a query without actually SEEING the query to be re-written or a reasonable facsimile of it is not feasible. That said, I'm more interested in the following statement:

    What do you mean by this? If the 'reporting tool' is performing the same SELECT against Oracle that works 'in Oracle' (I presume using SQL*Plus/SQL*Dev/etc.) -- why would it not work? What is the error?

    If there is somehow a limitation to the SQL available inside the reporting tool, but you have SQL syntax to perform your desired logic that works from Oracle itself, can you not create a view in Oracle that performs all of the required JOINs and then query the view from the reporting tool?
     
  3. Biswadeep

    Biswadeep Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    Since i have to do group by in case statement so i am using over () function in the case statement which is working fine in oracle but not in oracle so want to replace the case statement with the Subquery.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I presume the second 'oracle' should have been something else. Basically the reporting tool you are using does not support analytic functions. As the last paragraph of my prior answer suggested, is it possible to create a view in Oracle that contains the SQL with the analytic functions (i.e. the OVER...) and then query the view from your reporting tool?

    Failing that, as I noted in the first sentence of my reply, you have not supplied sufficient information for make it feasible for anyone on this board to offer suggestions on rewriting the SQL. We don't have the tables or the data. You haven't even provided us with the actual SQL query, but you want suggestions on how to rewrite something we have never actually seen. This is not a reasonable request.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not necessarily; older versions of Oracle or the reporting software may not accept CASE statements as that may not be part of the SQL language for that release.
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    You quoted my whole post, so I'm not sure which portion you're referring to. My assumption is that you are responding to "I presume the second 'oracle' should have been something else." I realized that statement is not necessarily true -- hence the words 'I presume". If it's not true, I hope the OP will provide a correction. However, since his first post said: "...which is working in oracle and but not working in the reporting tool..." and the post I was replying to said "...which is working fine in oracle but not in oracle..." -- my best guess was that the second post was a typo. Mind you, the reporting tool might well be one developed by Oracle corporation, but the statement still should have distinguished it from the Oracle database.

    In any event, my primary question to the OP still stands. Since he indicates that the query *does* work 'from Oracle', is there some reason why he cannot use that SQL to create a view and then query the view from the reporting tool. This would seem to be the most straightforward solution.