topic CASE statement when selecting data from Oracle in QlikView

CASE statement when selecting data from Oracle

Anonymous — Tue, 08 Mar 2011 19:17:40 GMT

Hi Everyone, I'm new to extracting data from Oracle db's. Have done a fair amount with SQL.

I'm trying to load data from an Oracle db using the following load scrip but get the "FROM keyword not found where expected" error. I know this script would work when referencing a SQL db. Any ideas\help would be most appreciated.

ODBC

CONNECT TO [HDBP;DBQ=HDBP] (XUserId is fEWfXEZJTaXEVCFIEaTcEGFLdE, XPassword is UXbWAJVMBDZeEXRNZDNSEHNKJF);

SELECT "COMPUTER_NAME" as NBNAME,

"OPERATING_SYSTEM",

"SERIAL_NUMBER",

"DEPARTMENT" =

CASE

WHEN TYPE = 'Laptops' THEN 'EUC'

WHEN TYPE = 'Workstations' THEN 'EUC'

ELSE 'Unasssigned'

END

FROM SIU.WORKSTATION;

CASE statement when selecting data from Oracle

Tue, 08 Mar 2011 19:29:46 GMT

Move your Oracle CASE logic to QlikView side.

CASE statement when selecting data from Oracle

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 19:52:42 GMT

Hello,

Sometimes the drivers don't pass on the database manager some instructions, but you can get something very similar by doing

Computers:LOAD NBNAME, "OPERATING_SYSTEM", "SERIAL_NUMBER", IF(MATCH("TYPE", 'Workstations', 'Laptops') > 0, 'EUC', 'Unassigned') AS DEPARTMENT;SQL SELECT "COMPUTER_NAME" as NBNAME,"OPERATING_SYSTEM","SERIAL_NUMBER","TYPE"FROM SIU.WORKSTATION;

Hope that helps.

CASE statement when selecting data from Oracle

Anonymous — Tue, 08 Mar 2011 20:03:02 GMT

Thanks very much Miguel - i'll give that a try!

Thanks again,

simon

CASE statement when selecting data from Oracle

nstefaniuk — Tue, 08 Mar 2011 22:13:25 GMT

Hi, the correct syntax in Oracle is:



SELECT "COMPUTER_NAME" "NBNAME",
"OPERATING_SYSTEM",
"SERIAL_NUMBER",
CASE
WHEN TYPE = 'Laptops' THEN 'EUC'
WHEN TYPE = 'Workstations' THEN 'EUC'
ELSE 'Unasssigned'
END "DEPARTMENT"
FROM SIU.WORKSTATION;

CASE statement when selecting data from Oracle

Anonymous — Tue, 08 Mar 2011 22:38:19 GMT

I think this should work:
SELECT "COMPUTER_NAME" as NBNAME,
"OPERATING_SYSTEM",
"SERIAL_NUMBER",
CASE
WHEN TYPE = 'Laptops' THEN 'EUC'
WHEN TYPE = 'Workstations' THEN 'EUC'
ELSE 'Unasssigned'
END as "DEPARTMENT"
FROM SIU.WORKSTATION;