asked on

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

PortletPaul

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.

PortletPaul

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

awking00

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?

Steve A

ASKER

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

PortletPaul

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)

Steve A

ASKER

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial