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 Go to next message
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 #670536 is a reply to message #670534] Tue, 10 July 2018 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

BTW - Table have ROWS; not records!
Re: SELECT QUERY [message #670537 is a reply to message #670536] Tue, 10 July 2018 14:19 Go to previous messageGo to next message
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 #670538 is a reply to message #670537] Tue, 10 July 2018 14:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

What problem needs to be solved?

what is expected & desired results?

Re: SELECT QUERY [message #670539 is a reply to message #670538] Tue, 10 July 2018 14:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vharish006 wrote on Tue, 10 July 2018 12:50
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.




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 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
vharish006 wrote on Tue, 10 July 2018 21:50
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??
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).
Previous Topic: rownum from table
Next Topic: Need Help on Creating FUNCTION
Goto Forum:
  


Current Time: Thu Mar 28 21:39:16 CDT 2024