Help required to tune the Sub query in WHERE Clause to access the lookup table

Database

Help required to tune the Sub query in WHERE Clause to access the lookup table

Hi,

I have a base table and a lookup table. I need to generate the product code based on the business conditions stated in the lookup table. However I do not have a  column to join the tables. Even if I could modify the structure of the lookup table to join with the base table, the value of the colulmns specified in the lookup table would not be always match with the base table and hence it is not feasible.

Lookup table structure:

BusRuleno buscond1 busval1 logicalcond1 buscond2 busval2 logicalcond2

1                 prod_cd      'AA'           OR          state_cd   'TN'       NULL

2                 NULL          NULL          NULL      state_cd   'CA'       NULL

3                 prod_cd     'BB'             AND        county_cd  'TT'      NULL

Base table has all the details related to product,state,county, country,order, etc.

I need the below query to filter the data. It gives syntax error as it looks like a scalar subquery. If it works fine, I can write the recursive query to validate all the conditions stated in the lookup table.

I can do the same functionality by using stored procedure by executing the whole query thru CALL DBC.SYSEXECSQL(query string ). But I would like to execute thru the simple process.

SEL * FROM mdb.mybasetbl

WHERE Country = 'USA'

AND (SEL buscond1 || '=' || busval1 || logicalcond1 || buscond2 || '=" busval2

FROM mydb.mylkuptbl)

Please help me to rewrite/tune the query. Thanks!

2 REPLIES

Re: Help required to tune the Sub query in WHERE Clause to access the lookup table

Hi,

As far as I have understood your problem is that you need to check all the conditions mentioned in the lookup table to filter records from the base table.

But in the subquery "(SEL buscond1 || '=' || busval1 || logicalcond1 || buscond2 || '=" busval2 FROM mydb.mylkuptbl)", you are selecting all the rows together from the lookup table.

You need to add a WHERE condition which will check one condition at a time from the lookup table.

Moreover, you are actually not executing a query directly. The query which will actually get the desired data is getting formed and then being executed. As far as I know, you WILL have to use DBC.SYSEXECSQL(query string )  to first form your query and then execute it.

Re: Help required to tune the Sub query in WHERE Clause to access the lookup table


yes, you are right.... we need to include the where condition and we will apply the filter condition based on the business rule we fetched from teh business rule table... otherthan sysexecsql, do we have any other simple solution?