Run Open Query through list of numbers that are stored on sql server table

Database

Run Open Query through list of numbers that are stored on sql server table

Hello All - I am new but exitinted to join this forum.

I am having an issue with running query through the list of orders stored on sql server table - any idea/toughts are much appreciate it!

see below query and teh description of problem in comment part:


select * from openquery(tdproductionTEST,' SELECT OrdersTBL."ORDER_NUM" AS "Order Num" ,OrdersTBL_2."SKU_NUM" AS "Item Num" ,OrdersTBL_3."PART_NUM" AS "Part Num" FROM EURO_FIN.ORDER_HEADER_ALL_EURO OrdersTBL /* here is the part that i have listed the order numbers by comma - i want to do something like WHERE sql.server.myDB.myTable - i want to reference this part to table in which i will store those order numbers*/ WHERE ((trim(Leading ''0'' from OrdersTBL."ORDER_NUM" IN ( ''92539865'', ''104978987'', ''442111486'' )) ) GROUP BY OrdersTBL."ORDER_NUM" ,OrdersTBL_2."SKU_NUM" ,OrdersTBL_3."PART_NUM"

Accepted Solutions
Teradata Employee

Re: Run Open Query through list of numbers that are stored on sql server table

Is SPS_ORDERS_TBL in SQL Server or Teradata?  If it's not in Teradata you'll have to copy it to there.  If it is in Teradata, then I think all you really need to do is join OrdersTBL to SPS_ORDERS_TBL on order number.

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Run Open Query through list of numbers that are stored on sql server table

If this is running on a teradata system to need to ue only single quotes '

not  ''

dave

Teradata Employee

Re: Run Open Query through list of numbers that are stored on sql server table

I think the doubled single-quotes are needed here because they are in a quoted string!

 

The only problem I see is that there are far too many parenetheses, and they don't match up.

 

WHERE trim(Leading ''0'' from OrdersTBL."ORDER_NUM") IN ( ''92539865'', ''104978987'', ''442111486'' )

 

You might also need to add close-parenthesis (")") to the openquery function call at the end, but maybe that just got truncated when you pasted the query in here.

Re: Run Open Query through list of numbers that are stored on sql server table

Thanks for responses - sorry , i think i wasn`t clear in my previus message ...

I wanted to use something like this after WHERE clause:

 

something like this...WHERE ((trim(Leading ''0'' from OrdersTBL."Order_Num") IN 
 
( SELECT * FROM [Database].[AMERICA].[dbo].[SPS_ORDERS_TBL] where OrdersTBL."ORDER_NUM" = [dbo].[SPS_ORDERS_TBL]."Order_Nb" )
 

so I could store my orders numer in 

SPS_ORDERS_TBL

 

Teradata Employee

Re: Run Open Query through list of numbers that are stored on sql server table

Is SPS_ORDERS_TBL in SQL Server or Teradata?  If it's not in Teradata you'll have to copy it to there.  If it is in Teradata, then I think all you really need to do is join OrdersTBL to SPS_ORDERS_TBL on order number.

Re: Run Open Query through list of numbers that are stored on sql server table

Thanks GJColeman - it worked beautifull :-)