Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a excel file with 10 Product_Sub_group_ code
and Teradata table with Millions of rows.
I have done script like below but give me an error
Tbl1:
LOAD Product_Sub_Group_Code
FROM
(
Tbl2:
SQL SELECT *
FROM BUYER_LOOKUP
WHERE exists (Tbl1.Product_Sub_Group_Code)
drop table Tbl1;
error:---
SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the word 'Tbl1'.
Tbl2:
SQL SELECT *
FROM BUYER_LOOKUP
WHERE exists (Tbl1.Product_Sub_Group_Code)
Note: I do not want Full Teradata table to be load. Can we use where clause to filter data using Tbl1?
the clause Exists can be used with qlik syntax not sql. So you should have to load all the table in teradata then reload it with the exists condition but in this case you have to load all data.
Another solution is (but I do not know is syntax is correct) is:
SELECT *
FROM BUYER_LOOKUP
WHERE Tbl1.Product_Sub_Group_Code in ('Code1','Code2','Code3', ....,'Code10')
where codes 1..10 are codes you have in your Excel
Let me know
don't think in this way because not exists is a QlikView syntax and it only works in the Load section (but in the load section you're not in Teradata)
Maybe you can use a variable to identify the Product_Sub_Group_Code to filter
and then use the variable in the second statement (SQL), pseudocode
load
.....
set variablewithproductsubgroupcode = ......;
sql select
...
WHERE fieldinteradata in ('$()variablewithproductsubgroupcode')
Hi Saccone,
I will use IN clause but It was just example of 10 rows but accualy there are Hundred of Code.
we can do for all Codes.
Any other solution?
Regards & Thanks
Vinay
the only thing is load all data from teradata, (during night?) and store them in a qvd file. Then load all data in your document from the qvd and finally reload them with exists, the advantage is that you load all data in a different moment from the loading of the document
try something like this (load inline is a replacement of your load from excel)
TB1:
load * inline [
Product_Sub_Group_Code
aaaa
bbbbb
cccccccc
ddddd
aaa bbb
e
f
e
f
g
];
B:
load
'(' & chr(39) & concat(distinct Product_Sub_Group_Code, chr(39) & ',' & chr(39)) & chr(39) & ')' as SqlFilter
Resident
TB1;
LET v = Peek('SqlFilter');
Tbl2:
SQL SELECT *
FROM BUYER_LOOKUP
WHERE Product_Sub_Group_Code in $(v);
the query you send to db is
SELECT *
FROM BUYER_LOOKUP
WHERE Product_Sub_Group_Code in ('aaa bbb','aaaa','bbbbb','cccccccc','ddddd','e','f','g')