Home » SQL & PL/SQL » SQL & PL/SQL » SELECT QUERY (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
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
|
|
|
|
Re: SELECT QUERY [message #670537 is a reply to message #670536] |
Tue, 10 July 2018 14:19 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
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
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')
|
|
|
|
Re: SELECT QUERY [message #670539 is a reply to message #670538] |
Tue, 10 July 2018 14:50 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi Black Swan,
I'm trying to build a SELECT Query where it has some defaulted values for some columns and some columns value should has to be based on condition.
E.g :WHEN HEALTH_MEDICINE='x' THEN 'HEALTH AND MEDICINE GENERAL' the column ExtID_ProductClassification should be populated with 'HEALTH AND MEDICINE GENERAL'
WHEN HISTORY='x' THEN 'HISTORY GENERAL' the column ExtID_ProductClassification should be populated with 'HISTORY GENERAL'
Now that if a record has both HEALTH_MEDICINE and HISTORY has Value 'x' then how can i get 2 records with both values. Is this possible in Select query??
SELECT
'' AS Defaul,
'1' AS Default_Quantity,
'FALSE' AS Deleted,
CASE
WHEN HEALTH_MEDICINE='x' THEN 'HEALTH AND MEDICINE GENERAL'
WHEN HISTORY='x' THEN 'HISTORY GENERAL'
END AS ExtID_ProductClassification,
'1' AS MaxQuantity,
'0' AS MinQuantity,
'TRUE' AS Modifiable,
'' AS Apttus_Config2__ClassifId__r,
Oracle_Product_ID,
NAME as PRODUCTNAME
FROM
PQINF.XXPQ_PRODUCT_SUBJECTS
where id='01t40000003WarDAAS'';
Expected Result is uploaded.
|
|
|
Re: SELECT QUERY [message #670540 is a reply to message #670539] |
Tue, 10 July 2018 14:59 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
vharish006 wrote on Tue, 10 July 2018 12:50Hi Black Swan,
I'm trying to build a SELECT Query where it has some defaulted values for some columns and some columns value should has to be based on condition.
E.g :WHEN HEALTH_MEDICINE='x' THEN 'HEALTH AND MEDICINE GENERAL' the column ExtID_ProductClassification should be populated with 'HEALTH AND MEDICINE GENERAL'
WHEN HISTORY='x' THEN 'HISTORY GENERAL' the column ExtID_ProductClassification should be populated with 'HISTORY GENERAL'
Now that if a record has both HEALTH_MEDICINE and HISTORY has Value 'x' then how can i get 2 records with both values. Is this possible in Select query??
SELECT
'' AS Defaul,
'1' AS Default_Quantity,
'FALSE' AS Deleted,
CASE
WHEN HEALTH_MEDICINE='x' THEN 'HEALTH AND MEDICINE GENERAL'
WHEN HISTORY='x' THEN 'HISTORY GENERAL'
END AS ExtID_ProductClassification,
'1' AS MaxQuantity,
'0' AS MinQuantity,
'TRUE' AS Modifiable,
'' AS Apttus_Config2__ClassifId__r,
Oracle_Product_ID,
NAME as PRODUCTNAME
FROM
PQINF.XXPQ_PRODUCT_SUBJECTS
where id='01t40000003WarDAAS'';
Expected Result is uploaded.
We don't have your table PQINF.XXPQ_PRODUCT_SUBJECTS
We don't have your data.
We can't run, test, or improve posted SQL above when you refuse to provide working Test Case
We have no idea what are your "expected results".
|
|
|
Re: SELECT QUERY [message #670544 is a reply to message #670539] |
Wed, 11 July 2018 03:38 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
vharish006 wrote on Tue, 10 July 2018 21:50Now that if a record has both HEALTH_MEDICINE and HISTORY has Value 'x' then how can i get 2 records with both values. Is this possible in Select query??
This is what UNPIVOT clause was designed for.
SQL> -- Code to mimic some representative sample data
SQL> -- You may omit this use your real table in the main query instead
SQL> with xx_test (oracle_product_id,business,health_medicine,government_documents) as
2 ( select 'A', 'x',null,'x' from dual
3 union all select 'B', null,'x','x' from dual
4 union all select 'C', 'x','x','x' from dual
5 union all select 'D', null,null,'x' from dual )
6 -- The query for UNPIVOT demonstration
7 select oracle_product_id, classification, marker
8 from xx_test
9 unpivot exclude nulls (marker for classification
10 in (business, health_medicine, government_documents));
O CLASSIFICATION M
- -------------------- -
A BUSINESS x
A GOVERNMENT_DOCUMENTS x
B HEALTH_MEDICINE x
B GOVERNMENT_DOCUMENTS x
C BUSINESS x
C HEALTH_MEDICINE x
C GOVERNMENT_DOCUMENTS x
D GOVERNMENT_DOCUMENTS x
8 rows selected.
From the source four rows, this query produces eight rows (number of any value in the last three columns).
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:54:54 CDT 2024
|