Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle SQL Select within a Where Clause

Posted on 2016-11-03
9
Medium Priority
?
90 Views
Last Modified: 2016-11-03
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
Comment
Question by:kalees
  • 4
  • 4
9 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 41871900
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
 

Author Comment

by:kalees
ID: 41871907
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
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41871955
@Author - what is your question then ?

You want all the records where we have driver or not  ???
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:kalees
ID: 41871970
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
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41871977
Which column you are fetching from RL_DRIVER_ALLOCATION_VW  based on where the driver allocation to date is null ?
0
 

Author Comment

by:kalees
ID: 41871981
DRIVER_FIRST_NAME,
        DRIVER_FAMILY_NAME,
0
 
LVL 35

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41872010
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
 

Author Closing Comment

by:kalees
ID: 41872036
Many thanks Pawan, a much simpler solution than I envisaged but works perfectly.
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41872165
Welcome Kalees !!

Regards,
Pawan
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question