CODEWITHSUNDEEP
sqloracle-databasedate
Goran Bart Bartolić
Goran Bart Bartolić

Reputation: 81

Get clients only if they are born in 09 month

I have a problem with a message " invalid relational operator"

select distinct ig.describe from info_groupe ig 
inner join client_info ki on (ig.id=ki.id_info_group) 
where exists (select * from client k inner join client_contact kk
on (k.id=kk.id_client)  where kk.location='B'
 and kk.type_contact= 'EMAIL'
 AND substr(date, 4, 2),
 'dd.mm.yyyy'), 'MM' ='09' 
 order by describe desc;

Except month, i have to satisfy condition that the location ="B" and type_contact = "EMAIL". What I want is to display "describe" with the condition location is B and type contact = Email for all clients that have date (for example 01.09.88) month 09

Upvotes: 0

Views: 50

Answers (2)

sagi
sagi

Reputation: 40481

Try this:

select distinct ig.describe from info_groupe ig 
inner join client_info ki on (ig.id=ki.id_info_group) 
where exists (select * from client k inner join client_contact kk
               on (k.id=kk.id_client)  where kk.location='B'
                 and kk.type_contact= 'EMAIL'
                 AND to_char(date,'MM') ='09' 
                 AND ki.id = kk.id_client)
order by describe desc

I assumed that you have column of client id in client_info, i called it id_client, if its different then change it.

I used to_char(date,'MM') to get the month.

Upvotes: 1

MT0
MT0

Reputation: 168071

You can use EXTRACT( MONTH FROM <date column> ):

select distinct ig.describe
from   info_groupe ig 
       inner join client_info ki
       on (ig.id=ki.id_info_group) 
where exists ( select *
               from   client k
                      inner join client_contact kk
                      on (k.id=kk.id_client)
               where  kk.location='B'
               and    kk.type_contact= 'EMAIL'
               AND    EXTRACT( MONTH FROM date ) = 9
             ) 
order by describe desc;

Upvotes: 3

Related Questions