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

LVL 9
dustockAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>but I'm not sure how to say WHEN fiber_id = 1 that it should get the data for fiber_height1 as H_1.
Give the below line a whirl, changing ??? to whatever you want to populate here if fiver_id <> 1.  If that's not in play, then delete the 'ELSE ???'
CASE WHEN fiber_id = 1 THEN fiber_height1 ELSE ??? END as H_1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
dustockAuthor Commented:
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


Sample table and how I want my data to look
0
dustockAuthor Commented:
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

0
PortletPaulfreelancerCommented:
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}
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.