Select query with sub query in Oracle SQL

Question

This is an example of what I am trying to achieve with lot of simplifications.

I have a table like this:

CREATE TABLE temp_pt
    (
        pt_key          number PRIMARY KEY
        , history       VARCHAR(20)
        , country       VARCHAR(2)
        , currency      VARCHAR(3)
        , settlementday VARCHAR(10)
    );

There are some records in this table, say as follows:

insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(1,  'MATCH', 'GB', 'GBP', '2020-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(2,  'MATCH', 'GB', 'GBP', '2021-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(3,  'MATCH', 'GB', 'EUR', '2020-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(4,  'MATCH', 'GB', 'EUR', '2021-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(5,  'MATCH', 'GI', 'GBP', '2020-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(6,  'MATCH', 'GI', 'GBP', '2021-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(7,  'MATCH', 'GI', 'EUR', '2020-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(8,  'MATCH', 'GI', 'EUR', '2021-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(9,  'MATCH', 'NL', 'GBP', '2020-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(10, 'MATCH', 'NL', 'GBP', '2021-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(11, 'MATCH', 'NL', 'EUR', '2020-12-01');
insert into  temp_pt(PT_KEY, history, country, currency, settlementday) values(12, 'MATCH', 'NL', 'EUR', '2021-12-01');

I need all records with the settlementday > 2020-12-31 except those where (currency is GBP and country is GB or GI). How do I write this?

Gordon Linoff · Accepted Answer · 2020-12-15 16:48:12Z

1

need to select all records in this table where currency is GBP, but not if country is GB or GI.

This seems like a simple where clause:

select t.*
from temp_pt
where current = 'GBP' and country not in ('GB', 'GI')

answered Dec 15, 2020 at 16:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prasanna Narayanan Over a year ago

I am sorry my question was not clear. I need all records with the settlementday > 2020-12-31 except those where (currency is GBP and country is GB or GI). How do I write this?

Prasanna Narayanan · Accepted Answer · 2020-12-15 17:35:23Z

0

I think I found the answer, but its not elegant:

SELECT  * from temp_pt 
where 
TO_DATE(settlementday,'yyyy-MM-dd') > TO_DATE('2020-12-31','yyyy-MM-dd') AND pt_key NOT IN 
(SELECT PT_KEY FROM TEMP_PT WHERE currency = 'GBP' 
AND country IN ('GB', 'GI') 
AND TO_DATE(settlementday,'yyyy-MM-dd') > TO_DATE('2020-12-31','yyyy-MM-dd'))
;

answered Dec 15, 2020 at 17:35

Prasanna Narayanan

4391 gold badge3 silver badges17 bronze badges

Collectives™ on Stack Overflow

Select query with sub query in Oracle SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related