[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

Oracle SQL Select within a Where Clause

I have the following code which has been working fine, however, we now have some vehicles which do not have a driver allocated to them and this query is excluding those vehicles from the results. I am aware that the clause "(RL_DRIVER_ALLOCATION_VW.TO_DATE IS NULL) " is the issue and believe that I need to add a select statement in the where clause but am struggling to work this out.

Any help would be greatly appreciated.

SELECT RL2_AR_INVOICES_VW.ACCOUNT_CODE,
       RL2_AR_INVOICES_VW.INVOICE_NUMBER,
       INVOICE_DATE,
       RL2_AR_INVOICE_LINES_VW.FROM_DATE,
       RL2_AR_INVOICE_LINES_VW.TO_DATE,
       DRIVER_FIRST_NAME,
       DRIVER_FAMILY_NAME,
       REG_NO,
       CONTRACT_NUMBER,
       START_DATE,
       RL2_CONTRACTS_VW.PERIOD,
       RL2_CONTRACTS_VW.VARIANT,
       RL2_CONTRACTS_VW.P11D,
       SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN NETT_AMOUNT END) "FIN NET",
       SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN NETT_AMOUNT + (TAX_AMOUNT * .5) END) "FIN NET + IR VAT",
       SUM (CASE WHEN ELEMENT_TYPE IN ('MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT END) "NON-FIN NET",
       SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT END) "TOTAL NET",
       SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN TAX_AMOUNT END) "FIN VAT",
       SUM (CASE WHEN ELEMENT_TYPE IN ('MAINT', 'ROADSIDE', 'SERVICE') THEN TAX_AMOUNT END) "NON-FIN VAT",
       SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN TAX_AMOUNT END) "TOTAL VAT",
       SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT + TAX_AMOUNT END) "TOTAL INC VAT"
  FROM RL2_CONTRACTS_VW
       INNER JOIN RL2_AR_INVOICE_LINES_VW
          ON (REGISTRATION_NUMBER = REG_NO)
       INNER JOIN RL_QUOTATIONS_VW
          ON (RL_QUOTATIONS_VW.QMD_ID = RL2_CONTRACTS_VW.QMD_ID)
       INNER JOIN RL_DRIVER_ALLOCATION_VW
          ON (REGISTRATION_NO = REGISTRATION_NUMBER)
       INNER JOIN RL2_AR_INVOICES_VW
          ON (RL2_AR_INVOICES_VW.DOC_ID = RL2_AR_INVOICE_LINES_VW.DOC_ID)
 WHERE     (    ( (RL2_AR_INVOICES_VW.C_ID = 3) AND (TIME_PERIOD = 140))
            AND (RL2_AR_INVOICES_VW.ACCOUNT_CODE = 'ICE001'))
       AND (RL_DRIVER_ALLOCATION_VW.TO_DATE IS NULL) AND (LATEST_REVISION = 'Y')
 GROUP BY
       RL2_AR_INVOICES_VW.ACCOUNT_CODE,
       RL2_AR_INVOICES_VW.INVOICE_NUMBER,
       INVOICE_DATE,
       RL2_AR_INVOICE_LINES_VW.FROM_DATE,
       RL2_AR_INVOICE_LINES_VW.TO_DATE,
       DRIVER_FIRST_NAME,
       DRIVER_FAMILY_NAME,
       REG_NO,
       CONTRACT_NUMBER,
       START_DATE,
       RL2_CONTRACTS_VW.PERIOD,
       RL2_CONTRACTS_VW.VARIANT,
       RL2_CONTRACTS_VW.P11D
0
kalees
Asked:
kalees
  • 4
  • 4
1 Solution
 
Helena Markováprogrammer-analystCommented:
Maybe you can try it with LEFT JOIN
SELECT RL2_AR_INVOICES_VW.ACCOUNT_CODE,
        RL2_AR_INVOICES_VW.INVOICE_NUMBER,
        INVOICE_DATE,
        RL2_AR_INVOICE_LINES_VW.FROM_DATE,
        RL2_AR_INVOICE_LINES_VW.TO_DATE,
        DRIVER_FIRST_NAME,
        DRIVER_FAMILY_NAME,
        REG_NO,
        CONTRACT_NUMBER,
        START_DATE,
        RL2_CONTRACTS_VW.PERIOD,
        RL2_CONTRACTS_VW.VARIANT,
        RL2_CONTRACTS_VW.P11D,
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN NETT_AMOUNT END) "FIN NET",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN NETT_AMOUNT + (TAX_AMOUNT * .5) END) "FIN NET + IR VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT END) "NON-FIN NET",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT END) "TOTAL NET",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN TAX_AMOUNT END) "FIN VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('MAINT', 'ROADSIDE', 'SERVICE') THEN TAX_AMOUNT END) "NON-FIN VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN TAX_AMOUNT END) "TOTAL VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT + TAX_AMOUNT END) "TOTAL INC VAT"
   FROM RL2_CONTRACTS_VW
        INNER JOIN RL2_AR_INVOICE_LINES_VW
           ON (REGISTRATION_NUMBER = REG_NO)
        INNER JOIN RL_QUOTATIONS_VW
           ON (RL_QUOTATIONS_VW.QMD_ID = RL2_CONTRACTS_VW.QMD_ID)
        LEFT JOIN RL_DRIVER_ALLOCATION_VW
           ON (REGISTRATION_NO = REGISTRATION_NUMBER)

        INNER JOIN RL2_AR_INVOICES_VW
           ON (RL2_AR_INVOICES_VW.DOC_ID = RL2_AR_INVOICE_LINES_VW.DOC_ID)
  WHERE     (    ( (RL2_AR_INVOICES_VW.C_ID = 3) AND (TIME_PERIOD = 140))
             AND (RL2_AR_INVOICES_VW.ACCOUNT_CODE = 'ICE001'))
       /* AND (RL_DRIVER_ALLOCATION_VW.TO_DATE IS NULL)*/ AND (LATEST_REVISION = 'Y')
  GROUP BY
        RL2_AR_INVOICES_VW.ACCOUNT_CODE,
        RL2_AR_INVOICES_VW.INVOICE_NUMBER,
        INVOICE_DATE,
        RL2_AR_INVOICE_LINES_VW.FROM_DATE,
        RL2_AR_INVOICE_LINES_VW.TO_DATE,
        DRIVER_FIRST_NAME,
        DRIVER_FAMILY_NAME,
        REG_NO,
        CONTRACT_NUMBER,
        START_DATE,
        RL2_CONTRACTS_VW.PERIOD,
        RL2_CONTRACTS_VW.VARIANT,
        RL2_CONTRACTS_VW.P11D
0
 
kaleesAuthor Commented:
I had tried that in the past. The reason that this does not work is that the vehicles may have had many drivers and the only way to identify the current driver is by using the AND (RL_DRIVER_ALLOCATION_VW.TO_DATE IS NULL) clause hence needing to keep this in.
0
 
Pawan KumarDatabase ExpertCommented:
@Author - what is your question then ?

You want all the records where we have driver or not  ???
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kaleesAuthor Commented:
That is correct, all record regardless of driver or not but if there is a driver, it needs to be the most recent one i.e. where the driver allocation to date is null which identifies that the driver still has the vehicle.
0
 
Pawan KumarDatabase ExpertCommented:
Which column you are fetching from RL_DRIVER_ALLOCATION_VW  based on where the driver allocation to date is null ?
0
 
kaleesAuthor Commented:
DRIVER_FIRST_NAME,
        DRIVER_FAMILY_NAME,
0
 
Pawan KumarDatabase ExpertCommented:
Try this..

SELECT  RL2_AR_INVOICES_VW.ACCOUNT_CODE,
        RL2_AR_INVOICES_VW.INVOICE_NUMBER,
        INVOICE_DATE,
        RL2_AR_INVOICE_LINES_VW.FROM_DATE,
        RL2_AR_INVOICE_LINES_VW.TO_DATE,
        DRIVER_FIRST_NAME,
        DRIVER_FAMILY_NAME,
        REG_NO,
        CONTRACT_NUMBER,
        START_DATE,
        RL2_CONTRACTS_VW.PERIOD,
        RL2_CONTRACTS_VW.VARIANT,
        RL2_CONTRACTS_VW.P11D,
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN NETT_AMOUNT END) "FIN NET",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN NETT_AMOUNT + (TAX_AMOUNT * .5) END) "FIN NET + IR VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT END) "NON-FIN NET",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT END) "TOTAL NET",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT') THEN TAX_AMOUNT END) "FIN VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('MAINT', 'ROADSIDE', 'SERVICE') THEN TAX_AMOUNT END) "NON-FIN VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN TAX_AMOUNT END) "TOTAL VAT",
        SUM (CASE WHEN ELEMENT_TYPE IN ('FINANCE', 'RFT', 'MAINT', 'ROADSIDE', 'SERVICE') THEN NETT_AMOUNT + TAX_AMOUNT END) "TOTAL INC VAT"
   FROM RL2_CONTRACTS_VW
        INNER JOIN RL2_AR_INVOICE_LINES_VW 
           ON (REGISTRATION_NUMBER = REG_NO)
        INNER JOIN RL_QUOTATIONS_VW
           ON (RL_QUOTATIONS_VW.QMD_ID = RL2_CONTRACTS_VW.QMD_ID)
        LEFT JOIN RL_DRIVER_ALLOCATION_VW
           ON (REGISTRATION_NO = REGISTRATION_NUMBER AND RL_DRIVER_ALLOCATION_VW.TO_DATE IS NULL )
        INNER JOIN RL2_AR_INVOICES_VW
           ON (RL2_AR_INVOICES_VW.DOC_ID = RL2_AR_INVOICE_LINES_VW.DOC_ID)
  WHERE     (    ( (RL2_AR_INVOICES_VW.C_ID = 3) AND (TIME_PERIOD = 140))
             AND (RL2_AR_INVOICES_VW.ACCOUNT_CODE = 'ICE001'))
        AND (LATEST_REVISION = 'Y')
  GROUP BY 
        RL2_AR_INVOICES_VW.ACCOUNT_CODE,
        RL2_AR_INVOICES_VW.INVOICE_NUMBER,
        INVOICE_DATE,
        RL2_AR_INVOICE_LINES_VW.FROM_DATE,
        RL2_AR_INVOICE_LINES_VW.TO_DATE,
        DRIVER_FIRST_NAME,
        DRIVER_FAMILY_NAME,
        REG_NO,
        CONTRACT_NUMBER,
        START_DATE,
        RL2_CONTRACTS_VW.PERIOD,
        RL2_CONTRACTS_VW.VARIANT,
        RL2_CONTRACTS_VW.P11D

Open in new window

0
 
kaleesAuthor Commented:
Many thanks Pawan, a much simpler solution than I envisaged but works perfectly.
0
 
Pawan KumarDatabase ExpertCommented:
Welcome Kalees !!

Regards,
Pawan
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now