CODEWITHSUNDEEP
sqljoinsubqueryinformix
souhaib fares
souhaib fares

Reputation: 21

replace sub query with join

I have the following query:

select distinct dco datpos 
from bkhis 
where dco>=(select min(dco) 
            from bkhis 
            where dag=TODAY - 1 and dco<=dag)

Due to the Informix version we use (10.00), we can't work with sub query and with the aggregation function in the sub query it become more complicated.

Can anyone help me please to rewrite this query using join.

Upvotes: 2

Views: 67

Answers (1)

krokodilko
krokodilko

Reputation: 36107

There is no comma between dco and datpos in your question here: ... distinct dco datpos; I am assuming that this is a typo.

Use this query:

select distinct b.dco, b.datpos 
from bkhis b
JOIN bkhis b1 ON b.dco >= b1.dco
WHERE b1.dag=TODAY - 1 and b1.dco<=dag

Upvotes: 1

Related Questions