Use the outcome of a query as input for a new select statement

Database

Use the outcome of a query as input for a new select statement

Hi,

Ik have two tables with data. On table with forms with names such as 'Ticket' or 'change_name'. The form table has 30 columns named column1, column2, etc... and depending on the form name, one of them contains a last name. 

Form table:

Table 1

Form_name         |     column1    |        column2      |        column3 etc.

Ticket                 |     henderson  |     €350              |       Aruba

change_name    |     90210        |    Smith              |       flower

I have a second lookup table that tells for each form which column holds the last name (there are hundreds of forms and they change often, so hard coding is not an option). It looks like this:

Table 2

Form_name        |    last_name

Ticket                |    column1

change_name   |    column2

I would like to write a query to pick up the last name from Table1 using the values from Table 2.

So, something like:

select (table2.last_name) as last name from Table1

left join table2 on table1.formcode = table2.formcode

The desired outcome would look like:

form_name    |     last_name

Ticket             |    Henderson 

change_name |    Smith

I have the feeling I should use dynamic SQL, but I am not experienced and after a couple of hours of browsing forums, I thought I'd try my luck here.

4 REPLIES
Enthusiast

Re: Use the outcome of a query as input for a new select statement

hi edger,

i am not sure about dynamic SQL but it can be achieved without dynamic SQL as well.

CREATE TABLE col_number ( col VARCHAR(10));
INSERT INTO col_number ('col1');
INSERT INTO col_number ('col2');
INSERT INTO col_number ('col3');
INSERT INTO col_number ('col4');
INSERT INTO col_number ('col5');
INSERT INTO col_number ('col6');

SEL A.FORM_NAME,
A.LAST_NAME
FROM
(
SELECT
FORM_NAME,
B.COL AS COL_NUM,
CASE B.COL
WHEN 'COL1' THEN TAB.COL1
WHEN 'COL2' THEN TAB.COL2
WHEN 'COL3' THEN TAB.COL3
WHEN 'COL4' THEN TAB.COL4
WHEN 'COL5' THEN TAB.COL5
WHEN 'COL6' THEN TAB.COL6
END AS LAST_NAME
FROM FORM TAB CROSS JOIN COL_NUMBER AS B) A
INNER JOIN FORM_1 B
ON A.FORM_NAME=B.FORM_NAME
AND A.COL_NUM=B.LAST_NAME;

Regards,

Arun

Junior Contributor

Re: Use the outcome of a query as input for a new select statement

It's a horrible data model, that's why you need horrible queries :)

select frm.form_name,
case lkp.last_name
when 'column1' then frm.column1
when 'column2' then frm.column2
when 'column3' then frm.column3
etc.
end
from formtable as frm join lookuptable as lkp
on frm.form_name = lkp.form_name

Re: Use the outcome of a query as input for a new select statement

Spot on Dieter! Unfortunately, changing the data model will take many years and this hopefully just days :-)

Re: Use the outcome of a query as input for a new select statement

And thanks for your help, I got it running !