Unable to transfer query from Oracle SQL to MS SQL which has multiple joins

user_9087678 0
Viewed 67

I have an SQL query in Oracle which is executing and giving me the correct result. However, I need a similar result in MS SQL where I'm having issues regarding the table joins.

Below is my Oracle SQL:

SELECT
    number_of_inspection.line,
    number_of_inspection.week,
    number_of_inspection.total_records,
    ( visual_fails.fails + qc_fails.fails ) total_fails,
    nvl(round((100 / number_of_inspection.total_records) *(visual_fails.fails + qc_fails.fails), 2), 0) percentage_fails
FROM
    (
        SELECT
            COUNT(*) total_records,
            line,
            to_char(inspection_date, 'IW') week
        FROM
            master_table
        WHERE
            to_char(inspection_date, 'YYYY') = to_char(sysdate, 'YYYY')
            AND inspection_date >= add_months(sysdate, - 3)
        GROUP BY
            line,
            to_char(inspection_date, 'IW')
    ) number_of_inspection,
    (
        SELECT
            line,
            to_char(inspection_date, 'IW') week,
            COUNT(*) fails
        FROM
            master_table
        WHERE
            to_char(inspection_date, 'YYYY') = to_char(sysdate, 'YYYY')
            AND visual_inspection = 'FAIL'
        GROUP BY
            line,
            to_char(inspection_date, 'IW')
    ) visual_fails,
    (
        SELECT
            line,
            to_char(inspection_date, 'IW') week,
            COUNT(*) fails
        FROM
            master_table
        WHERE
            to_char(inspection_date, 'YYYY') = to_char(sysdate, 'YYYY')
            AND visual_inspection != 'FAIL'
            AND nvl(qc_inspection, 'FAIL') = 'FAIL'
        GROUP BY
            line,
            to_char(inspection_date, 'IW')
    ) qc_fails
WHERE
    number_of_inspection.line = visual_fails.line (+)
    AND number_of_inspection.week = visual_fails.week (+)
    AND number_of_inspection.line = qc_fails.line (+)
    AND number_of_inspection.week = qc_fails.week (+)

The correct result I'm getting in Oracle: 

+--------+------+---------------+------------+-----------------+
| LINE   | WEEK | TOTAL_RECORDS |TOTAL_FAILS | PERCENTAGE_FAILS|
+--------+------+---------------+------------+-----------------+
| Line 1 |  08  |   845         |    6       |   0.71          |
| Line 2 |  08  |   1790        |    11      |   0.61          |
| Line 1 |  09  |   350         |    9       |   2.57          |
| Line 2 |  09  |   1125        |    20      |   1.77          |
+--------+------+---------------+------------+-----------------+

Below is my MS SQL query:

SELECT number_of_inspection.line, 
       number_of_inspection.week_no, 
       number_of_inspection.total_records, 
       ( visual_fails.fails + qc_fails.fails ) total_fails, 
       Isnull(Round(( 100 / number_of_inspection.total_records ) * ( 
                           visual_fails.fails + qc_fails.fails ), 2), 0) 
                                               percentage_fails 
FROM   (SELECT Count(*)                        total_records, 
               line, 
               Datepart(ww, [inspection date]) Week_No 
        FROM   master_table 
        WHERE  Year([inspection date]) = Year(Getdate()) 
               AND [inspection date] >= Dateadd(month, -1, Getdate()) 
        GROUP  BY line, 
                  Datepart(ww, [inspection date])) number_of_inspection, 
       (SELECT line, 
               Datepart(ww, [inspection date]) AS Week_No, 
               Count(*)                        AS fails 
        FROM   master_table 
        WHERE  Year([inspection date]) = Year(Getdate()) 
               AND [visual inspection] = 'FAIL' 
        GROUP  BY line, 
                  Datepart(ww, [inspection date])) visual_fails, 
       (SELECT line, 
               Datepart(ww, [inspection date]) AS Week_No, 
               Count(*)                        AS fails 
        FROM   master_table 
        WHERE  Year([inspection date]) = Year(Getdate()) 
               AND [visual inspection] != 'FAIL' 
               AND Isnull([qc inspection], 'FAIL') = 'FAIL' 
        GROUP  BY line, 
                  Datepart(ww, [inspection date])) qc_fails, 
       where number_of_inspection 
       LEFT OUTER JOIN visual_fails 
                    ON number_of_inspection.line = visual_fails.line 
                       -- join issue 
                       AND number_of_inspection 
       LEFT OUTER JOIN visual_fails 
                    ON number_of_inspection.week_no = visual_fails.week_no 
                       -- join issue 
                       AND number_of_inspection 
       LEFT OUTER JOIN qc_fails 
                    ON number_of_inspection.line = qc_fails.line -- join issue 
                       AND number_of_inspection 
       LEFT OUTER JOIN qc_fails 
                    ON number_of_inspection.week_no = qc_fails.week_no -- join issue

I'm aware that we join table(s) in FROM clause in MS SQL, unlike Oracle where we can also join in WHERE clause.

When I try executing the MS SQL query, I get the following error:

Incorrect syntax near the keyword 'WHERE'.

Note: I'm using SQL Server 2014, so I cannot alter the database to change Compatibility Level.

Can you please help me with what I'm doing wrong in my MS SQL query?

join oracle sql-server sql
2 Answers

I would use conditional aggregation in either database. Here is the idea in SQL Server:

select line, datepart(week, [Inspection Date]), 
       count(*) total_records,
       sum(case when visual_inspection = 'FAIL' then 1 else 0 end) as num_visual_fails,
       sum(case when [QC Inspection] = 'FAIL' or [QC Inspection] is null then 1 else 0 end) as num_qc_fails,
       avg(case when visual_inspection = 'FAIL' or
                     [QC Inspection] = 'FAIL' or
                     [QC Inspection] is null
                then 1.0 else 0
            end) as fail_ratio
       . . . 
from master_table
where year(inspection_date) = year(getdate()) and
      inspection_date >= dateadd(month, -3, getdate())
group by line, datepart(week, [Inspection Date]);

This isn't exactly your query. But it should give you the right idea on how to approach the problem.

user_11440350

I recommend Gordon's answer for improving your code, but I also want you to be able to understand how to convert Oracle's old join style to the modern standard join syntax (which also works in Oracle).

For readability, I edited out the contents of your inline view subqueries, but I think this gets the idea across.

SELECT
    number_of_inspection.line,
    number_of_inspection.Week_No,
    number_of_inspection.total_records,
    (visual_fails.fails + qc_fails.fails) total_fails,
    ISNULL(ROUND((100/number_of_inspection.total_records)*(visual_fails.fails + qc_fails.fails),2),0) percentage_fails
FROM
    (...) number_of_inspection
LEFT OUTER JOIN 
    (...) visual_fails
    ON number_of_inspection.line = visual_fails.line
    AND number_of_inspection.Week_No = visual_fails.Week_No
LEFT OUTER JOIN
    (...) qc_fails
    ON number_of_inspection.line = qc_fails.line
    AND number_of_inspection.Week_No = qc_fails.Week_No
;
user_30618520
Related