asked on

Converting Oracle query to Microsft SQL need help on DECODE

I have an Oracle Query that returns one line of data for information on multiple tables in multiple lines. I used the MAX and DECODE in Oracle to get the data I wanted in the format I wanted. So in the first DECODE when fiber_ID is 1 then returns the value of Fiber_height as H_1.

I now need to convert this query to Microsoft SQL. I have found that the CASE statement is the equivilent of DECODE but I can't seem to get it figured out.

So far I figure I am on the right track, but I'm not sure how to say WHEN fiber_id = 1 that it should get the data for fiber_height1 as H_1.

MAX(CASE fib.fiber_id WHEN '1' THEN ???  END)

Open in new window

Working Oracle Query

SELECT p.job_number, fib.serial_number, fib.test_datetime, fer.subunit_id, fer.employee_id, fer.terminal_id, fer.pass, fer.roc_long as RX, fer.roc_short as RY, fer.angle_long as SX, fer.angle_short as SY, fer.ferrule_flatness, fer.data_fill, fer.coplanarity as CF, fer.comments, fer.EFD_FILE, fer.MAX_DIF_FH as Pr, fer.ADJACENT_DIF_FH as HA, fer.GEOMETRY_LIMIT as GL, fer.X_SLOPE as X_FIBERS_SLOPE, fer.Y_SLOPE as Y_FIBERS_SLOPE,
    MAX(DECODE(fib.fiber_id, '1', fib.fiber_height)) H_1,
    MAX(DECODE(fib.fiber_id, '2', fib.fiber_height)) H_2,
    MAX(DECODE(fib.fiber_id, '3', fib.fiber_height)) H_3,
    MAX(DECODE(fib.fiber_id, '4', fib.fiber_height)) H_4,
    MAX(DECODE(fib.fiber_id, '5', fib.fiber_height)) H_5,
    MAX(DECODE(fib.fiber_id, '6', fib.fiber_height)) H_6,
    MAX(DECODE(fib.fiber_ID, '1', fib.core_dip)) HCd_1,
    MAX(DECODE(fib.fiber_ID, '2', fib.core_dip)) HCd_2,
    MAX(DECODE(fib.fiber_ID, '3', fib.core_dip)) HCd_3,
    MAX(DECODE(fib.fiber_ID, '4', fib.core_dip)) HCd_4,
    MAX(DECODE(fib.fiber_ID, '5', fib.core_dip)) HCd_5,
    MAX(DECODE(fib.fiber_ID, '6', fib.core_dip)) HCd_6,
    MAX(DECODE(fib.fiber_ID, '1', fib.x_fiber_radius)) RF_1,
    MAX(DECODE(fib.fiber_ID, '2', fib.x_fiber_radius)) RF_2,
    MAX(DECODE(fib.fiber_ID, '3', fib.x_fiber_radius)) RF_3,
    MAX(DECODE(fib.fiber_ID, '4', fib.x_fiber_radius)) RF_4,
    MAX(DECODE(fib.fiber_ID, '5', fib.x_fiber_radius)) RF_5,
    MAX(DECODE(fib.fiber_ID, '6', fib.x_fiber_radius)) RF_6,
FROM daisi_import_fiber fib, daisi_import_ferrule fer, products p
WHERE fib.test_datetime between to_date(^start_date_MM-DD-YYYY , 'MM-DD-YYYY') and     to_date(^end_date_MM-DD-YYYY , 'MM-DD-YYYY')
AND fer.database_id = fib.database_id
AND fer.serial_number = fib.serial_number
AND fer.subunit_id = fib.subunit_id
AND fer.test_datetime = fib.test_datetime
AND p.database_id = fer.database_id
AND p.serial_number = fer.serial_number
GROUP BY fib.database_id, p.job_number, fib.serial_number, fib.test_datetime, fer.subunit_id, fer.employee_id, fer.terminal_id, fer.pass, fer.roc_long, fer.roc_short, fer.angle_long, fer.angle_short, fer.ferrule_flatness, fer.data_fill, fer.coplanarity, fer.comments, fer.EFD_FILE, fer.MAX_DIF_FH, fer.ADJACENT_DIF_FH, fer.GEOMETRY_LIMIT, fer.X_SLOPE, fer.Y_SLOPE
ORDER BY fib.test_datetime

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

Couple other odds and ends...
* If fiber_id is numeric then the one does not have single quotes around it. If it's text, then it does.
* Multiple choices would be multiple WHEN..THEN fragments, and if it's evaluating the same column then you can put that after the case, like this..

CASE fiber_id 
WHEN 1 THEN fiber_height1 
WHEN 2 THEN fiber_height2
WHEN 3 THEN fiber_height3 ELSE you_get_the_idea END as H_1

Open in new window

dustock

ASKER

jimhorn
I think we are on the right track, but this still isn't working. Here is my revised statement, and while it runs, it doesnt return any data. I have attached a screen shot of how the data looks in the table for fiber_height, core_dip, and x_fiber_radius and how I want my output to look. Also fiber_id is datatype nvarchar and I don't require an else, it will always have fiber_id of 1 - 12 or 1 - 24 and fiber_height, core_dip, and x_fiber_radius data for each fiber

SELECT p.job_number, fib.serial_number, fib.test_datetime, fer.subunit_id, fer.employee_id, fer.terminal_id, fer.pass, fer.roc_long as RX, fer.roc_short as RY, fer.angle_long as SX, fer.angle_short as SY, fer.ferrule_flatness, fer.data_fill, fer.coplanarity as CF, fer.comments, fer.EFD_FILE, fer.MAX_DIF_FH as Pr, fer.ADJACENT_DIF_FH as HA, fer.GEOMETRY_LIMIT as GL, fer.X_SLOPE as X_FIBERS_SLOPE, fer.Y_SLOPE as Y_FIBERS_SLOPE,    
    MAX(CASE fib.fiber_id WHEN '1' THEN fib.fiber_height END)AS H_1,
    MAX(CASE fib.fiber_id WHEN '2' THEN fib.fiber_height END)AS H_2,
    MAX(CASE fib.fiber_id WHEN '3' THEN fib.fiber_height END)AS H_3,
    MAX(CASE fib.fiber_id WHEN '4' THEN fib.fiber_height END)AS H_4,
    MAX(CASE fib.fiber_id WHEN '5' THEN fib.fiber_height END)AS H_5,
    MAX(CASE fib.fiber_id WHEN '6' THEN fib.fiber_height END)AS H_6,
    MAX(CASE fib.fiber_ID WHEN '1' THEN fib.core_dip END)AS HCd_1,
    MAX(CASE fib.fiber_ID WHEN '2' THEN fib.core_dip END)AS HCd_2,
    MAX(CASE fib.fiber_ID WHEN '3' THEN fib.core_dip END)AS HCd_3,
    MAX(CASE fib.fiber_ID WHEN '4' THEN fib.core_dip END)AS HCd_4,
    MAX(CASE fib.fiber_ID WHEN '5' THEN fib.core_dip END)AS HCd_5,
    MAX(CASE fib.fiber_ID WHEN '6' THEN fib.core_dip END)AS HCd_6,
    MAX(CASE fib.fiber_ID WHEN '1' THEN fib.x_fiber_radius END)AS RF_1
    MAX(CASE fib.fiber_ID WHEN '2' THEN fib.x_fiber_radius END)AS RF_2
    MAX(CASE fib.fiber_ID WHEN '3' THEN fib.x_fiber_radius END)AS RF_3
    MAX(CASE fib.fiber_ID WHEN '4' THEN fib.x_fiber_radius END)AS RF_4
    MAX(CASE fib.fiber_ID WHEN '5' THEN fib.x_fiber_radius END)AS RF_5
    MAX(CASE fib.fiber_ID WHEN '6' THEN fib.x_fiber_radius END)AS RF_6
FROM daisi_import_fiber fib, daisi_import_ferrule fer, products p
WHERE fib.SERIAL_NUMBER = '1433353'
AND fer.database_id = fib.database_id
AND fer.serial_number = fib.serial_number
AND fer.subunit_id = fib.subunit_id
AND fer.test_datetime = fib.test_datetime
AND p.database_id = fer.database_id
AND p.serial_number = fer.serial_number
GROUP BY fib.database_id, p.job_number, fib.serial_number, fib.test_datetime, fer.subunit_id, fer.employee_id, fer.terminal_id, fer.pass, fer.roc_long, fer.roc_short, fer.angle_long, fer.angle_short, fer.ferrule_flatness, fer.data_fill, fer.coplanarity, fer.comments, fer.EFD_FILE, fer.MAX_DIF_FH, fer.ADJACENT_DIF_FH, fer.GEOMETRY_LIMIT, fer.X_SLOPE, fer.Y_SLOPE
ORDER BY fib.test_datetime

Open in new window

dustock

ASKER

I seem to have this working now. if I remove AND p.serial_number = fer.serial_number from the query it starts working. In Oracle these was necessary to only show 1 line of data without repeating a lot of the data. I have only done some minor checking but it appears not to be repeating data. Any idea why removing this causes it to work?

SELECT p.job_number, fib.serial_number, fib.test_datetime, fer.subunit_id, fer.employee_id, fer.terminal_id, fer.pass, fer.roc_long as RX, fer.roc_short as RY, fer.angle_long as SX, fer.angle_short as SY, fer.ferrule_flatness, fer.data_fill, fer.coplanarity as CF, fer.comments, fer.EFD_FILE, fer.MAX_DIF_FH as Pr, fer.ADJACENT_DIF_FH as HA, fer.GEOMETRY_LIMIT as GL, fer.X_SLOPE as X_FIBERS_SLOPE, fer.Y_SLOPE as Y_FIBERS_SLOPE,    
    MAX(CASE fib.fiber_id WHEN '1' THEN fib.fiber_height END)AS H_1,
    MAX(CASE fib.fiber_id WHEN '2' THEN fib.fiber_height END)AS H_2,
    MAX(CASE fib.fiber_id WHEN '3' THEN fib.fiber_height END)AS H_3,
    MAX(CASE fib.fiber_id WHEN '4' THEN fib.fiber_height END)AS H_4,
    MAX(CASE fib.fiber_id WHEN '5' THEN fib.fiber_height END)AS H_5,
    MAX(CASE fib.fiber_id WHEN '6' THEN fib.fiber_height END)AS H_6,
    MAX(CASE fib.fiber_ID WHEN '1' THEN fib.core_dip END)AS HCd_1,
    MAX(CASE fib.fiber_ID WHEN '2' THEN fib.core_dip END)AS HCd_2,
    MAX(CASE fib.fiber_ID WHEN '3' THEN fib.core_dip END)AS HCd_3,
    MAX(CASE fib.fiber_ID WHEN '4' THEN fib.core_dip END)AS HCd_4,
    MAX(CASE fib.fiber_ID WHEN '5' THEN fib.core_dip END)AS HCd_5,
    MAX(CASE fib.fiber_ID WHEN '6' THEN fib.core_dip END)AS HCd_6,
    MAX(CASE fib.fiber_ID WHEN '1' THEN fib.x_fiber_radius END)AS RF_1
    MAX(CASE fib.fiber_ID WHEN '2' THEN fib.x_fiber_radius END)AS RF_2
    MAX(CASE fib.fiber_ID WHEN '3' THEN fib.x_fiber_radius END)AS RF_3
    MAX(CASE fib.fiber_ID WHEN '4' THEN fib.x_fiber_radius END)AS RF_4
    MAX(CASE fib.fiber_ID WHEN '5' THEN fib.x_fiber_radius END)AS RF_5
    MAX(CASE fib.fiber_ID WHEN '6' THEN fib.x_fiber_radius END)AS RF_6
FROM daisi_import_fiber fib, daisi_import_ferrule fer, products p
WHERE fib.SERIAL_NUMBER = '1433353'
AND fer.database_id = fib.database_id
AND fer.serial_number = fib.serial_number
AND fer.subunit_id = fib.subunit_id
AND fer.test_datetime = fib.test_datetime
AND p.database_id = fer.database_id

GROUP BY fib.database_id, p.job_number, fib.serial_number, fib.test_datetime, fer.subunit_id, fer.employee_id, fer.terminal_id, fer.pass, fer.roc_long, fer.roc_short, fer.angle_long, fer.angle_short, fer.ferrule_flatness, fer.data_fill, fer.coplanarity, fer.comments, fer.EFD_FILE, fer.MAX_DIF_FH, fer.ADJACENT_DIF_FH, fer.GEOMETRY_LIMIT, fer.X_SLOPE, fer.Y_SLOPE
ORDER BY fib.test_datetime

Open in new window

PortletPaul

the where clause (excluding joins) is:

WHERE fib.SERIAL_NUMBER = '1433353'

so this is not be needed to inhibit multiple lines (for that query)
"AND p.serial_number = fer.serial_number"

(because: fib.serial_number = fer.serial_number also)

If your intent is to replicate the former data model you would expect that join condition to remain required.

Perhaps if your where clause was more like the Oracle example you may re-discover that you need that join condition. ( a selection over a date range)

pity you aren't using ANSI join syntax

FROM daisi_import_fiber fib
INNER JOIN daisi_import_ferrule fer  ON fib.database_id = fer.database_id
                                    AND fib.serial_number = fer.serial_number
                                    AND fib.subunit_id = fer.subunit_id
                                    AND fib.test_datetime = fer.test_datetime
INNER JOIN products p  ON fer.database_id = p.database_id
                      AND fer.serial_number = p.serial_number

Open in new window

btw: you have commas missing at the ends of lines 14-18 above

{+ an edit - sorry}