SELECT QUERY [message #670534] |
Tue, 10 July 2018 13:47 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi All,
I'm trying to create a SELECT Query .Most of the columns are defaulted
For example:if business='x' then column Apttus_Config2__ClassifId__r should populate with value 'BUSINESS GENERAL'
if HEALTH_MEDICINE='x' then column Apttus_Config2__ClassifId__r should populate with value 'HEALTH AND MEDICINE GENERAL'
But there are cases where both Business and Health Medicine has x,How can i populate 2 records with different Column names in it as part of select query.
Is this possible??
QUERY :
----------
SELECT
'' AS Defaul,
'1' AS Default_Quantity,
'FALSE' AS Deleted,
CASE
WHEN BUSINESS = 'x' THEN 'BUSINESS GENERAL'
WHEN THE_ARTS = 'x' THEN 'ARTS GENERAL'
WHEN GOVERNMENT_DOCUMENTS='x' THEN 'GOVERNMENT DOCUMENTS GENERAL'
WHEN HEALTH_MEDICINE='x' THEN 'HEALTH AND MEDICINE GENERAL'
WHEN HISTORY='x' THEN 'HISTORY GENERAL'
WHEN GENERAL_K12_AND_PUBLIC_LIBRARY='x' THEN 'K12 PUBLIC LIBRARY GENERAL'
WHEN LITERATURE='x' THEN 'LITERATURE GENERAL'
WHEN NEWS_AND_NEWSPAPERS='x' THEN 'NEWS AND NEWSPAPER GENERAL'
WHEN SCIENCE_AND_TECHNOLOGY='x' THEN 'SCIENCE AND TECHNOLOGY GENERAL'
WHEN SOCIAL_SCIENCE='x' THEN 'SOCIAL SCIENCE GENERAL'
WHEN DISSERTATIONS_AND_THESES='x' THEN 'DISSERTATIONS_AND_THESES'
WHEN INTERDISCIPLINARY='x' THEN 'INTERDISCIPLINARY'
WHEN PATENTS='x' THEN 'PATENTS'
WHEN SERVICES_AND_FEES='x' THEN 'SERVICES AND FEES'
WHEN LIBRARY_SYSTEMS_AND_TOOLS='x' THEN 'LIBRARY SYSTEMS AND TOOLS'
END AS ExtID_ProductClassification,
'1' AS MaxQuantity,
'0' AS MinQuantity,
'TRUE' AS Modifiable,
'' AS Apttus_Config2__ClassifId__r,
CASE
WHEN BUSINESS = 'x' THEN 'BUSINESS'
WHEN THE_ARTS = 'x' THEN 'THE_ARTS'
WHEN GOVERNMENT_DOCUMENTS='x' THEN 'GOVERNMENT DOCUMENTS'
WHEN HEALTH_MEDICINE='x' THEN 'HEALTH AND MEDICINE'
WHEN HISTORY='x' THEN 'HISTORY'
WHEN GENERAL_K12_AND_PUBLIC_LIBRARY='x' THEN 'K12 PUBLIC LIBRARY'
WHEN LITERATURE='x' THEN 'LITERATURE'
WHEN NEWS_AND_NEWSPAPERS='x' THEN 'NEWS AND NEWSPAPER'
WHEN SCIENCE_AND_TECHNOLOGY='x' THEN 'SCIENCE AND TECHNOLOGY'
WHEN SOCIAL_SCIENCE='x' THEN 'SOCIAL SCIENCE'
WHEN DISSERTATIONS_AND_THESES='x' THEN 'DISSERTATIONS_AND_THESES'
WHEN INTERDISCIPLINARY='x' THEN 'INTERDISCIPLINARY'
WHEN PATENTS='x' THEN 'PATENTS'
WHEN SERVICES_AND_FEES='x' THEN 'SERVICES AND FEES'
WHEN LIBRARY_SYSTEMS_AND_TOOLS='x' THEN 'LIBRARY SYSTEMS AND TOOLS'
END AS CLASSIFICATIONID__NAME,
Oracle_Product_ID,
NAME as PRODUCTNAME
FROM
PQINF.XXPQ_PRODUCT_SUBJECTS
Sample DATA:
-----------
CREATE TABLE XX_TEST
(BUSINESS VARCHAR2(20),
HEALTH_MEDICINE VARCHAR2(20),
GOVERMENT_DOCUMENTS VARCHAR2(20));
INSERT INTO XX_TEST
XX_TEST(BUSINESS,HEALTH_MEDICINE,GOVERMENT_DOCUMENTS)
VALUES('x','x','x')
[Updated on: Tue, 10 July 2018 13:51] Report message to a moderator
|
|
|