Solved

Oracle SQL Select within a Where Clause

Posted on 2016-11-03
9
56 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 24

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

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 24

Accepted Solution

by:
Pawan Kumar earned 500 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 24

Expert Comment

by:Pawan Kumar
ID: 41872165
Welcome Kalees !!

Regards,
Pawan
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-01008: not all variables bound. 6 38
SQL Query 34 82
SQL Statement to Update Email Domain 2 23
SQL query question 8 40
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now