2 outer joins allowed in this sql statement

Hello,
I rewrote this original query to use 2 outer-joins (+) but gave me an error of 'ORA-01417: a table may be outer joined to at most one other table'.  

AND     PVD_UID = nvl(TCM_PVD_UID_FAC_AUTHD,TCM_PVD_UID_FAC_REQD)
AND     p1.PXC_UID = nvl(TCM_PXC_UID_LOW_AUTHD,TCM_PXC_UID_LOW_REQD)

I don't want to use the NVL function for this.  The logic is that to use the
'Authorized' data first and if none is available, then use the 'Required' data.

Is there a better way to write this query?  
Thanks.

SELECT  MEM_ID, 
            MEM_FORMATTED_NAME, 
            TAU_REFERENCE_NUMBER, 
            TCM_SEQUENCE, 
            SEV_DESC, 
            AST_DESC, 
            DTR_DESC,  
            p1.PXC_CODE low_code,
            NVL(TCM_DATE_BEGIN_AUTHD, TCM_DATE_BEGIN_REQD) "Beg Date", 
            NVL(TCM_DATE_END_AUTHD, TCM_DATE_END_REQD) "End Date", 
            NVL(TCM_TOTAL_QUANTITY_AUTHD, TCM_TOTAL_QUANTITY_REQD) "Qty",
            NVL(TCM_FREQUENCY_AUTHD, TCM_FREQUENCY_REQD) "Units",
            NVL(TCM_FREQUENCY_QUALIFIER_AUTHD,   
            TCM_FREQUENCY_QUALIFIER_REQD) "Frequency",
           TAU_DATE_AUTH_CLOSED, 
           PVD_ID, 
           PVD_NAME
FROM  KMAA_REPORT.TAU_TREATMENT_AUTHORIZATION,
           KMAA_REPORT.TCM_TAU_COMPONENT,
           KMAA_REPORT.PVD_PROVIDER,
           KMAA_REPORT.MEM_MEMBER,
           KMAA_REPORT.SEV_SERVICE,
           KMAA_REPORT.POS_PLACE_OF_SERVICE,
           KMAA_REPORT.AST_AUTHORIZATION_STATUS,
           KMAA_REPORT.DTR_DETERMINATION_REASON,
           KMAA_REPORT.PXC_PROCEDURE_CODE p1
WHERE TCM_TAU_UID = TAU_UID
AND     PVD_UID = nvl(TCM_PVD_UID_FAC_AUTHD,TCM_PVD_UID_FAC_REQD)
AND     MEM_UID = TAU_MEM_UID
AND     AST_CODE = TCM_AST_CODE 
AND     DTR_CODE = TCM_DTR_CODE
AND     TAU_CTE_CODE = 'WAIVER'
AND     p1.PXC_UID = nvl(TCM_PXC_UID_LOW_AUTHD,TCM_PXC_UID_LOW_REQD)
AND     SEV_CODE = TCM_SEV_CODE
AND     POS_CODE = TCM_POS_CODE_REQD 
AND     TCM_AST_CODE NOT IN ('VOID', 'CXL')
AND     TCM_SEV_CODE IN ('MPSEV', 'MPCDO', 'SCLIS', 'SCLS', 'SCLICD', 'SCLCDO')
AND     TCM_DATE_END_REQD >= TO_DATE('07/23/2013','MM/DD/YYYY')

Open in new window

MachinegunnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
HI, I placed your query into a code block as it's easier to 'select all' etc. (with my Topic Advisor hat on).

I would normally also attempt to answer - but as there are no table aliases used I do not know where each of the fields come from, consequently I'd probably end-up with a mess.

Do you use ANSI join syntax at all? This is what I would propose using as I feel it is way easier to implement join conditions that way - but for me to do this would require knowing which fields belong to which table.
0
PortletPaulfreelancerCommented:
Well I had another look, and perhaps I can deduce the field to table through your naming convention (despite this I always recommend referencing field to table or aliases - that's your choice of course). So I've attempted an alternative which I believe meets your objectives.
SELECT
        MEM.MEM_ID
      , MEM.MEM_FORMATTED_NAME
      , TAU.TAU_REFERENCE_NUMBER
      , TCM.TCM_SEQUENCE
      , SEV.SEV_DESC
      , AST.AST_DESC
      , DTR.DTR_DESC
      , NVL(PXC1.PXC_CODE,PXC2.PXC_CODE)                                          low_code
      , NVL(TCM.TCM_DATE_BEGIN_AUTHD,          TCM.TCM_DATE_BEGIN_REQD)          "Beg Date"
      , NVL(TCM.TCM_DATE_END_AUTHD,            TCM.TCM_DATE_END_REQD)            "End Date"
      , NVL(TCM.TCM_TOTAL_QUANTITY_AUTHD,      TCM.TCM_TOTAL_QUANTITY_REQD)      "Qty"
      , NVL(TCM.TCM_FREQUENCY_AUTHD,           TCM.TCM_FREQUENCY_REQD)           "Units"
      , NVL(TCM.TCM_FREQUENCY_QUALIFIER_AUTHD, TCM.TCM_FREQUENCY_QUALIFIER_REQD) "Frequency"
      , TAU.TAU_DATE_AUTH_CLOSED
      , NVL(PVD1.PVD_ID,PVD2.PVD_ID)
      , NVL(PVD1.PVD_NAME,PVD2.PVD_NAME)
FROM KMAA_REPORT.TAU_TREATMENT_AUTHORIZATION TAU
INNER JOIN KMAA_REPORT.MEM_MEMBER MEM               ON TAU.TAU_MEM_UID = MEM.MEM_UID
INNER JOIN KMAA_REPORT.TCM_TAU_COMPONENT TCM        ON TAU.TAU_UID = TCM.TCM_TAU_UID
INNER JOIN KMAA_REPORT.SEV_SERVICE SEV              ON TCM.TCM_SEV_CODE = SEV.SEV_CODE
INNER JOIN KMAA_REPORT.POS_PLACE_OF_SERVICE POS     ON TCM.TCM_POS_CODE_REQD = POS.POS_CODE
INNER JOIN KMAA_REPORT.AST_AUTHORIZATION_STATUS AST ON TCM.TCM_AST_CODE = AST.AST_CODE
INNER JOIN KMAA_REPORT.DTR_DETERMINATION_REASON DTR ON TCM.TCM_DTR_CODE = DTR.DTR_CODE
   
LEFT JOIN KMAA_REPORT.PVD_PROVIDER PVD1 ON TCM.TCM_PVD_UID_FAC_AUTHD = PVD1.PVD_UID
LEFT JOIN KMAA_REPORT.PVD_PROVIDER PVD2 ON TCM.TCM_PVD_UID_FAC_REQD = PVD1.PVD_UID

LEFT JOIN KMAA_REPORT.PXC_PROCEDURE_CODE PXC1 ON TCM.TCM_PXC_UID_LOW_AUTHD = PXC1.PXC_UID
LEFT JOIN KMAA_REPORT.PXC_PROCEDURE_CODE PXC2 ON TCM.TCM_PXC_UID_LOW_REQD = PXC2.PXC_UID

WHERE TAU.TAU_CTE_CODE = 'WAIVER'
        AND TCM.TCM_AST_CODE NOT IN ('VOID', 'CXL')
        AND TCM.TCM_SEV_CODE IN ('MPSEV', 'MPCDO', 'SCLIS', 'SCLS', 'SCLICD', 'SCLCDO')
        AND TCM.TCM_DATE_END_REQD >= TO_DATE('07/23/2013', 'MM/DD/YYYY')

Open in new window

0
awking00Information Technology SpecialistCommented:
Can you provide some sample RELEVANT data (i.e. no need for every field and only records which meet all of your where criteria so as not to make things too complicated in testing) for the source tables and columns and what you want as output?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MachinegunnerAuthor Commented:
Thanks for the replies.
I tried your modified query and it kept bring like 128,500 rows and kept going!  So I stopped the query...  The original result set was like 374 rows.

Anyways, I am providing some data in this .xls file.  I modified the names and any other relevent personal information, so no worries about this data going back to anybody out there.

Thanks.
Data-Output.xls
0
PortletPaulfreelancerCommented:
Thanks for the spreadsheet - but without a representation of all of the tables I can't mimic the query. Sorry my suggestion didn't work and the increase in row numbers is probably a result of having outer joins in place... your original only seems to have inner joins.

In my experience NVL() works pretty well for the conditional joins, so perhaps you might just go with what you have if you judge it to be producing the correct results.

(I'd still recommend: a. using aliases, b. using ANSI join syntax)
0
MachinegunnerAuthor Commented:
Thanks for the replies.
From your suggestions, to keep the logic 'as is', how would you go about writing it then in your own style, meaning by what you see.
Just curious on how you would write the query, based on what is being shown, with your suggestions.
Thanks again.
0
PortletPaulfreelancerCommented:
Without knowing the full schema and without representative data (re)writing a query that fully retains the "as is logic" is awkward at best - I have already tried and it didn't work.

I have written several queries using NVL() in join conditions by the way - but for me that was due to use of parameters.

anyway, this is probably as close as I can get which is really just using my preferred  syntax
SELECT
        MEM.MEM_ID
      , MEM.MEM_FORMATTED_NAME
      , TAU.TAU_REFERENCE_NUMBER
      , TCM.TCM_SEQUENCE
      , SEV.SEV_DESC
      , AST.AST_DESC
      , DTR.DTR_DESC
      , PXC.PXC_CODE                                                             low_code
      , NVL(TCM.TCM_DATE_BEGIN_AUTHD,          TCM.TCM_DATE_BEGIN_REQD)          "Beg Date"
      , NVL(TCM.TCM_DATE_END_AUTHD,            TCM.TCM_DATE_END_REQD)            "End Date"
      , NVL(TCM.TCM_TOTAL_QUANTITY_AUTHD,      TCM.TCM_TOTAL_QUANTITY_REQD)      "Qty"
      , NVL(TCM.TCM_FREQUENCY_AUTHD,           TCM.TCM_FREQUENCY_REQD)           "Units"
      , NVL(TCM.TCM_FREQUENCY_QUALIFIER_AUTHD, TCM.TCM_FREQUENCY_QUALIFIER_REQD) "Frequency"
      , TAU.TAU_DATE_AUTH_CLOSED
      , PVD.PVD_ID
      , PVD.PVD_NAME
FROM KMAA_REPORT.TAU_TREATMENT_AUTHORIZATION TAU
INNER JOIN KMAA_REPORT.MEM_MEMBER MEM               ON TAU.TAU_MEM_UID = MEM.MEM_UID
INNER JOIN KMAA_REPORT.TCM_TAU_COMPONENT TCM        ON TAU.TAU_UID = TCM.TCM_TAU_UID
INNER JOIN KMAA_REPORT.SEV_SERVICE SEV              ON TCM.TCM_SEV_CODE = SEV.SEV_CODE
INNER JOIN KMAA_REPORT.POS_PLACE_OF_SERVICE POS     ON TCM.TCM_POS_CODE_REQD = POS.POS_CODE
INNER JOIN KMAA_REPORT.AST_AUTHORIZATION_STATUS AST ON TCM.TCM_AST_CODE = AST.AST_CODE
INNER JOIN KMAA_REPORT.DTR_DETERMINATION_REASON DTR ON TCM.TCM_DTR_CODE = DTR.DTR_CODE
   
INNER JOIN KMAA_REPORT.PVD_PROVIDER PVD       ON NVL(TCM.TCM_PVD_UID_FAC_AUTHD,TCM.TCM_PVD_UID_FAC_REQD) = PVD.PVD_UID

INNER JOIN KMAA_REPORT.PXC_PROCEDURE_CODE PXC ON NVL(TCM.TCM_PXC_UID_LOW_AUTHD,TCM.TCM_PXC_UID_LOW_REQD) = PXC.PXC_UID

WHERE TAU.TAU_CTE_CODE = 'WAIVER'
        AND TCM.TCM_AST_CODE NOT IN ('VOID', 'CXL')
        AND TCM.TCM_SEV_CODE IN ('MPSEV', 'MPCDO', 'SCLIS', 'SCLS', 'SCLICD', 'SCLCDO')
        AND TCM.TCM_DATE_END_REQD >= TO_DATE('07/23/2013', 'MM/DD/YYYY')
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.