Retrieving records with multiple tupples

Database
Enthusiast

Retrieving records with multiple tupples

I would like to retrieve records using a combination of two columns in a table.

 

I have a table containing 2 columns "channel_number" and "service_id".  I want to retrieve records from the table that match a unique combination of the two columns.  There are multiple such combinations.  Basically I need a IN clause with multiple columns.

 

From Stack Overflow, I found that this could be done using the WITH clause in DB2, Oracle etc.  I tried the following query, but I get an error.

 

with filter(cnum,sid) as (
	values 
	(10,495), (12,495), (14, 166), (14, 448) 
)
SELECT *
FROM   overdelivery, filter 
WHERE  controller_id = 100 AND
       create_date = '2017-08-02' AND
	   channel_number = filter.cnum and
	   service_id = filter.sid  
ORDER  BY account_number,channel_number

This is the error I get

 [Code: 3707, SQL State: 42000]  [Teradata Database] [TeraJDBC 16.00.00.28] [Error 3707] [SQLState 42000] Syntax error, expected something like a 'SELECT' keyword or '(' or a 'NONTEMPORAL' keyword or 'AS' keyword between '(' and the 'values' keyword.

All the example I see using the WITH clause on Teradata seems to expect a SELECT after the WITH AS.  Is there a way to pass a "hardcoded" list of values?


Accepted Solutions
Supporter

Re: Retrieving records with multiple tupples

Hi Milind,

 

May be the below modified query should work for you. I have converted columns into strings and concatenated them and then compared with the values.

SELECT *
FROM   overdelivery, filter 
WHERE  
  controller_id = 100 AND
  create_date = '2017-08-02' AND
  channel_number = filter.cnum AND
  service_id = filter.sid  AND
  (TO_CHAR(cnum) || TO_CHAR(sid)) IN ('10' || '495', '12' || '495', '14' || '166', '14' || '448')
ORDER  BY account_number,channel_number;

Hope it helps.

 

Thanks,

Rohan Sawant

1 ACCEPTED SOLUTION
9 REPLIES
Supporter

Re: Retrieving records with multiple tupples

Hi Milind,

 

As far as I know, Teradata does not support the "expanded" where clause syntax as you can do in DB2, Oracle. May be you could use the below modified query.

 

SELECT *
FROM   overdelivery, filter 
WHERE  
  controller_id = 100 AND
  create_date = '2017-08-02' AND
  channel_number = filter.cnum AND
  service_id = filter.sid  AND
  (cnum=10 and sid=495) OR 
  (cnum=12 and sid=495) OR
  (cnum=14 and sid=166) OR
  (cnum=14 and sid=448)   
ORDER  BY account_number,channel_number;

Hope this helps.

 

 

Thanks,

Rohan Sawant

Enthusiast

Re: Retrieving records with multiple tupples

Yeah, I can definitely do that. But I could have a 1000 combinations and I'm not sure if there is a limit to how long the query string can be. I'm using JDBC to generate the query.

The code will probably be more maintainable if I just use the IN clause with channel number and then filter out the service Ids that are not in my filter. Will end up retrieving unnecessary records from the database, and may be slightly slower. But if Teradata does not support it, there's not much I can do.
Supporter

Re: Retrieving records with multiple tupples

Hi Milind,

 

May be the below modified query should work for you. I have converted columns into strings and concatenated them and then compared with the values.

SELECT *
FROM   overdelivery, filter 
WHERE  
  controller_id = 100 AND
  create_date = '2017-08-02' AND
  channel_number = filter.cnum AND
  service_id = filter.sid  AND
  (TO_CHAR(cnum) || TO_CHAR(sid)) IN ('10' || '495', '12' || '495', '14' || '166', '14' || '448')
ORDER  BY account_number,channel_number;

Hope it helps.

 

Thanks,

Rohan Sawant

Enthusiast

Re: Retrieving records with multiple tupples

Rohan, what would "filter" be here? In my original example, filter was the virtual table created using WITH. Would I still be using WITH? How?


Supporter

Re: Retrieving records with multiple tupples

Hi Milind,

 

As far as I understand the WITH FILTER is used to filter records that match the tuple. I have added the same in WHERE clause. It should give same result at your original query.

 

Thanks,

Rohan Sawant

Enthusiast

Re: Retrieving records with multiple tupples

Rohan, with a small tweak, your code worked.  Thanks!!

 

SELECT *
FROM   overdelivery 
WHERE  controller_id = 100 AND
       create_date = '2017-08-02' AND
       (TO_CHAR(channel_number) || ',' TO_CHAR(service_id)) IN
       ('10,495', '12,495', '14,166', '14,448') 
ORDER  BY account_number,channel_number 
Highlighted
Apprentice

Re: Retrieving records with multiple tupples

Hi,

 

I don't think you can, the WITH clause creates a derived table (aka Common Table Expression) which - at least in Teradata - requires a SELECT.

 

I don't know how many pairs of values you have, but using your example you could code

with filter(cnum,sid) as (
	select cast(10 as integer) as cnum
       ,cast(495 as integer) as sid
from dbc.databasesv
where databasename = user
union all
	select cast(12 as integer) as cnum
       ,cast(495 as integer) as sid
from dbc.databasesv
where databasename = user
union all
	select cast(14 as integer) as cnum
       ,cast(166 as integer) as sid
from dbc.databasesv
where databasename = user
union all
	select cast(14 as integer) as cnum
       ,cast(448 as integer) as sid
from dbc.databasesv
where databasename = user
)
SELECT *
FROM   overdelivery, filter 
WHERE  controller_id = 100 AND
       create_date = '2017-08-02' AND
	   channel_number = filter.cnum and
	   service_id = filter.sid  
ORDER  BY account_number,channel_number

If you've got "hundreds" of pairs of values that may not be practical.

 

The FROM and WHERE clause against dbc.databasesv simply generates a single row using a standard system view. You could create your own one-row table and use that (TD doesn't have the Oracle style dummy table which is often used for such a purpose).

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Retrieving records with multiple tupples

Teradata allows the row constructor construct in a suquery comparison.

Since there is a long list of values, it would be better to do this as a subquery/join anyway.

 

To do so, make a temp table called filter rather than the WITH clause into which the pairs of values are inserted.

 

Then in your query use the clause:

WHERE <other conditions> AND

  (channel_number, service_id ) = ANY (SELECT channel, sid from filter)

 

This also means that the query never has to be changed. Just put different values in the table and run the query again.

Teradata Employee

Re: Retrieving records with multiple tupples

We can't find an existing request for the row constructor to be allowed in the WITH clause or in the IN list itself.

 

It would be great if this were submitted as a customer request for change either through the Partners Advisory Council web submission or through the system administrator of your site via Teradata @ Your Service.