IBM Mainframe Forum

by **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

by **GuyC** » Wed Nov 09, 2011 8:36 pm

Select all

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

by **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

by **GuyC** » Thu Nov 10, 2011 2:05 pm

I'm pretty sure my select is correct as proven by the following query :

Select all

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

Select all

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 all

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

by **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

IBM Mainframe Forum

need help with one select

need help with one select

Re: need help with one select

Re: need help with one select

Re: need help with one select

Re: need help with one select