vendredi 21 février 2020

Conditional Join Statements using If or Case

I need to Conditionally Left Join the same table, Essentially my On statement needs to either match the Insurance company or The customer based on if there is an Insurance company involved or not but due to how i retrieve the info the relative Column positions cannot change.

These are the two statements i would need to run Insurance Company Invoice

Left Join Public.Invoices  INV ON INV.PK_QuoSum = QSM.PK_QuoSum And INV.nDebtor = QSM.InsNum 

Customer Invoice

Left Join Public.Invoices  INV  ON INV.PK_QuoSum = QSM.PK_QuoSum And INV.nDebtor = QSM.PK_Customer

So i have tried this IF statement but get an error On the IF

IF QSM.InsNum <> 0
    Left Join Public.Invoices  INV ON INV.PK_QuoSum = QSM.PK_QuoSum And INV.nDebtor = QSM.InsNum 
    Else 
 Left Join Public.Invoices  INV  ON QSM.InsNum = 0 AND INV.PK_QuoSum = QSM.PK_QuoSum And INV.nDebtor = QSM.PK_Customer 
    END

Here is my subset of my full statement

SELECT DISTINCT ON (QSM.PK_QuoSum) 
    QSM.PK_QuoSum , QSM.PK_Customer , QSM.PK_CarCust , QSM.InsNum , QSM.nDate 
    CUS.FName ,CUS.SName , CUS.Phone1 , CUS.EMail, 
    INV.nDate, 

    From Public.QuoSum          QSM 
    Left Join Public.Customer   CUS       ON  CUS.PK_Customer = QSM.PK_Customer 
IF QSM.InsNum <> 0
    Left Join Public.Invoices  INV ON INV.PK_QuoSum = QSM.PK_QuoSum And INV.nDebtor = QSM.InsNum 
    Else 
 Left Join Public.Invoices  INV  ON QSM.InsNum = 0 AND INV.PK_QuoSum = QSM.PK_QuoSum And INV.nDebtor = QSM.PK_Customer 
    END

Aucun commentaire:

Enregistrer un commentaire