need help with one select



IBM's flagship relational database management system

need help with one select

Postby nion » Wed Nov 09, 2011 1:34 pm

Hi all.
Maybe someone could help me please.
My problem is the following:
There are two tables A and B.
Table A contains column ColA (varchar 20).
Table B contains column ColB (char 10).
I need to select the rows from table A where ColA contain values: a) which match with values in B.ColB; b) which match with 1st chars of values in B.ColB.

For example:
ColA ColB
======= =======
abc999 abc999 >>> select
gjk222 gik >>> not select
def def999 >>> select
z zyx111 >>> select

Is it possible to do such selection by single sql?

Thank you in advance.
nion
nion
 
Posts: 3
Joined: Wed Nov 09, 2011 1:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: need help with one select

Postby GuyC » Wed Nov 09, 2011 8:36 pm

select *
from a,b
where substr(colb,1,length(colA)) = colA

but it will not be performant, for each row of A it will completely scan table B
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: need help with one select

Postby nion » Thu Nov 10, 2011 12:29 pm

Thank you.
But to my regret it does not solve the problem.
In where clause I need something as
B.colB like A.colA||'%'
in order to select table A row with ColA=def if B.ColB=def999.
The above sql selects a row from A only if its ColA=B.ColB.
nion
nion
 
Posts: 3
Joined: Wed Nov 09, 2011 1:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: need help with one select

Postby GuyC » Thu Nov 10, 2011 2:05 pm

I'm pretty sure my select is correct as proven by the following query :
with a (ColA) as (
select cast('abc999' as varchar(20)) from sysibm.sysdummy1 union all
select cast('gjk222' as varchar(20)) from sysibm.sysdummy1 union all
select cast('def' as varchar(20)) from sysibm.sysdummy1 union all
select cast('z' as varchar(20)) from sysibm.sysdummy1)

, b (ColB) as (
select cast('abc999' as char(10)) from sysibm.sysdummy1 union all
select cast('gik' as char(10)) from sysibm.sysdummy1 union all
select cast('def999' as char(10)) from sysibm.sysdummy1 union all
select cast('zyx111 ' as char(10)) from sysibm.sysdummy1)

select *
from a,b
where substr(colb,1,length(colA)) = colA

=> output
COLA   COLB
abc999   abc999   
def   def999   
z   zyx111   

or your definitions are not what you said or there are trailing spaces in ColA
try:
select *
from a,b
where substr(colb,1,length(strip(colA))) = colA
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: need help with one select

Postby nion » Thu Nov 10, 2011 2:35 pm

GuyC!
Excuse me please - you are completely right.
It was my own lack of attention.
Yes, it works as expected.
Thank you a lot for your help and for your patience!
With thanks and best wishes,
nion
nion
 
Posts: 3
Joined: Wed Nov 09, 2011 1:19 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post