SQL in oracle

Hello Experts,

I have a table with below structure:  

SQL> DESC info_test
Name           Type               Nullable Default Comments 
-------------- ------------------ -------- ------- -------- 
ID    NUMBER             Y                         
OBJECT_TYPE VARCHAR2(100 CHAR)        

Open in new window

                 

And below are the records that I am having it:  

SQL> SELECT * FROM ms_sra_fac_info_test;
 
       ID      OBJECT_TYPE
----------- --------------------------------------------------------------------------------
       4002    VISIT_SETUP
       4003    VISIT
       4003    VISIT_SETUP
 

Open in new window


Now I want a  SQL if the object_type is "VISIT" then I will pick that ID even if I have "VISIT_SETUP" with the same ID .
But if the "VISIT" is not present in object_type column then default we can pick the ID's with object_type as "VISIT_SETUP" .
LVL 17
Swadhin RaySenior Technical Engineer Asked:
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.

johnsoneSenior Oracle DBACommented:
There might be a better way, but given your sample data, this seems to work

SELECT id, 
       object_type 
FROM   (SELECT id, 
               object_type, 
               Row_number() 
                 over ( 
                   PARTITION BY id 
                   ORDER BY CASE WHEN object_type = 'VISIT' THEN 1 WHEN 
                 object_type = 
                 'VISIT_SETUP' THEN 2 END) rn 
        FROM   info_test 
        WHERE  object_type IN ( 'VISIT', 'VISIT_SETUP' )) 
WHERE  rn = 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
PortletPaulfreelancerCommented:
select distinct
    id
from ms_sra_fac_info_test
where object_type IN ( 'VISIT', 'VISIT_SETUP' );

but I'm afraid I don't see the relevance of the table info_test ....

select distinct
    id
from ms_sra_fac_info_test
where object_type IN ( 'VISIT', 'VISIT_SETUP' )
and id in (select id from info_test);

Could you expand on the sample data in info_test and provide expected results?
0
Swadhin RaySenior Technical Engineer Author Commented:
The expected result will be as below:

   ID      OBJECT_TYPE
----------- --------------------------------------------------------------------------------
       4002    VISIT_SETUP
       4003    VISIT

Open in new window


As the 4003 ID has VISIT ... so this has to be picked . And the 4002 is not having "VISIT" in Object type column in the table so that that has been picked .

Means we need to check if the records are present in the table with two records then we need to check if the object_type  has VISIT or not if yes then pick that column and leave "VISIT_SETUP" record ..
0
Swadhin RaySenior Technical Engineer Author Commented:
Thanks...
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
Oracle Database

From novice to tech pro — start learning today.