Help - Dynamic Table

[Gb]

Hi,
just a quick (maybe stupid) question ..
Can be possible to link a table dynamically using this logic?:

--@@@ SQL START

SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
-- I want to dynamically attach a table here based on the value of
myvar (which is changed by the sql expression builder)
CASE
when myvar='123' then myFile2 m
else myfile3 m
end
where m.field1 ='Blah'

--@@@ SQL END
I cannot use procedure and I want see if something like this can be
done directly in SQL...... just plain sql!!

Thank you!

[Knut Stolze]

Gb wrote:

Hi,
just a quick (maybe stupid) question ..
Can be possible to link a table dynamically using this logic?:

--@@@ SQL START

SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
-- I want to dynamically attach a table here based on the value of
myvar (which is changed by the sql expression builder)
CASE
when myvar='123' then myFile2 m
else myfile3 m
end
where m.field1 ='Blah'

--@@@ SQL END
I cannot use procedure and I want see if something like this can be
done directly in SQL...... just plain sql!!

Yes, if you use dynamic SQL, then you can construct any statement you like,
including varying table names. But dynamic SQL is usually something done
from an application.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Gb]

It cannot be done directly in SQL?

Knut Stolze <st****@de.ibm.com> wrote in message news:<cc**********@fsuj29.rz.uni-jena.de>...

Gb wrote:

Hi,
just a quick (maybe stupid) question ..
Can be possible to link a table dynamically using this logic?:

--@@@ SQL START

SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
-- I want to dynamically attach a table here based on the value of
myvar (which is changed by the sql expression builder)
CASE
when myvar='123' then myFile2 m
else myfile3 m
end
where m.field1 ='Blah'

--@@@ SQL END
I cannot use procedure and I want see if something like this can be
done directly in SQL...... just plain sql!!

Yes, if you use dynamic SQL, then you can construct any statement you like,
including varying table names. But dynamic SQL is usually something done
from an application.

[Charles Wilt]

Uh, no you can't use CASE in the where clause.

Dynamically building the entire correct statement would be your best
choice.

Perhaps you could make use of the ALIAS keyword?

Could you replace the query with a stored procedure?

Lastly,

select *
from ( select
case
when value = '123' then value
else NULL
end as myvar,
case
when value = '123' then field1
else NULL
end as fld1,
case
when value = '123' then field2
else NULL
end as fld2,
case
when value = '123' then field3
else NULL
end as fld3
from myfile2
union
select
case
when value <> '123' then value
else NULL
end as myvar,
case
when value <> '123' then field1
else NULL
end as fld1,
case
when value <> '123' then field2
else NULL
end as fld2,
case
when value <> '123' then field3
else NULL
end as fld3
from myfile3
) as tbl
where myvar is not NULL

HTH,
Charles
In article <85**************************@posting.google.com >,
do*****************@yahoo.com says...

Hi,
just a quick (maybe stupid) question ..
Can be possible to link a table dynamically using this logic?:

--@@@ SQL START

SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
-- I want to dynamically attach a table here based on the value of
myvar (which is changed by the sql expression builder)
CASE
when myvar='123' then myFile2 m
else myfile3 m
end
where m.field1 ='Blah'

--@@@ SQL END
I cannot use procedure and I want see if something like this can be
done directly in SQL...... just plain sql!!

Thank you!

[Serge Rielau]

Try this:

SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
myFile2
WHERE myvar='123' as m
AND m.field1 = 'Blah'
UNION ALL
SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
myFile3 as m
WHERE myvar<>'123'
and m.field1 ='Blah'

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Gb wrote:

It cannot be done directly in SQL?

In a stored procedure, you can do that. You would use the procedure to
build a string containing the SQL statement and then execute that statement
dynamically.

But I'd say that Serge's approach would be the easiest for your example.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

Charles Wilt wrote:

Uh, no you can't use CASE in the where clause.

I guess you meant the FROM clause, right? (And even there you could have it
in the join predicate - but that's another topic.)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Gb]

Great guys!

Thank you

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<cc**********@hanover.torolab.ibm.com>...

Try this:

SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
myFile2
WHERE myvar='123' as m
AND m.field1 = 'Blah'
UNION ALL
SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
myFile3 as m
WHERE myvar<>'123'
and m.field1 ='Blah'

Cheers
Serge

[Charles Wilt]

Serge,

I'm hesitent to ask since you're from the IBM SQL compiler development
team, but....

Is that going to work? How?
"WHERE myvar='123' as m
AND m.field1 = 'Blah'"

Seems to be wrong to me given the "m" used as a table/correlation name
and a column name.
Using a basic UNION like this was my original thought. But I thought it
wouldn't work. I could have sworn I tested it and it didn't work.
Which lead to the complicated mess I posted <grin>. But trying again it
works fine.
Charles
In article <cc**********@hanover.torolab.ibm.com>, sr*****@ca.eye-be-
em.com says...

Try this:

SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
myFile2
WHERE myvar='123' as m
AND m.field1 = 'Blah'
UNION ALL
SELECT Value as myvar, m.field1,m.field2,m.field3 FROM
myFile3 as m
WHERE myvar<>'123'
and m.field1 ='Blah'

Cheers
Serge

[Serge Rielau]

Never trust anything just because of a signature....
Of course the correlation name m belongs to the FROM clause :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab