Teradata Dynamic Query

[peac3]

Hi all,

I would like to store multiple search strings values into table or variable so I can maintain them dynamically.
Then I am going to parse them using like any.

codes are like below

-- this is expected codes which is working
    select top 10  *
    from table_main pr, table_search ms
    where pr.name like any ('%wool%','%test%');

Then I store the '%wool%','%test%' as a single record so I put the search strings values into table_search and it throws syntax error as per codes below

-- syntax error
    select top 10  *
    from table_main pr, table_search ms
    where pr.name like any (ms.stringname);

Please help me how to resolve this.
Thank you,

 

[Olaf Doschke]

Remove the word "any", that should solve it, depending on the contents of ms.stringname.

Bye, Olaf.

 

[peac3]

Hi olaf,

For Teradata, if multiple strings we HAVE to put "any".

 

[Olaf Doschke]

But you don't have multiple strings in your last version, you join multiple records, one at a time.

Bye, Olaf.

 

[peac3]

yeah, but inside the column ms.stringname, it has multiple strings hence i got syntax error, I have tried it.

 

[Olaf Doschke]

You say stringname could be %wool%,%test% in a single row?
That won't work, a single column of a single row is a single value and can't represent a list of values in a query.

Bye, Olaf.

 

[peac3]

That's the challenge.
we need to be able to search multiple strings.

Eventhough i split up the search strings to have single row single values.

    select top 10 *
    from table_main pr, table_search ms
    where pr.name like any (ms.stringname, ms.stringname1, ms.stringname2 ); 
-- won't work

    select top 10 *
    from table_main pr, table_search ms
    where pr.name like any (select ms.stringname where company = 'x' ); 
-- won't work either

only hardcording as per sample above would work.
any other suggestions how to achieve that without hardcording?

 

[Olaf Doschke]

Well, if you don't post errors you get from the non working statments, and since this is a MS SQL Server forum, I fear this won't be solved here.

I don't see how your second statement could work, a subquery does not result in a list of values either.

Your first statement could work, what is the type of stringname, stringname1 and stringname2? If you don't have varchar but char there, this can't work because of trailing spaces.

Bye, Olaf.