Solved

Oracle SQL Select within a Where Clause

Posted on 2016-11-03
9
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 28

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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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 28

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 28

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 28

Expert Comment

by:Pawan Kumar
ID: 41872165
Welcome Kalees !!

Regards,
Pawan
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

751 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