Solved

Oracle SQL Select within a Where Clause

Posted on 2016-11-03
9
65 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 53
create a nested synonym 4 25
SYbase 4 31
performance query 4 24
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

810 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