BTEQ Related query

Tools

BTEQ Related query

Hi All,

I need your help to solve my issue.

Actually, we have created a file which having values like 200901,200902,200903.And also imported file with help of .import.
example:
.import vartext file=./PERD_ID.txt
using VAR_PERD_ID (VARCHAR(50))

and VAR_PERD_ID varible has values like 200901,200902,200903.

Query is not updateing any records when I pass parameter
PERD_ID IN (:VAR_PERD_ID).

Example: Update table.X
(Select
table1.a y,
table2.b z
from table1,
table2
where table1.a=table2.a
and PERD_ID IN (:VAR_PERD_ID))T1
where X.a=T1.y;

something like that issue is update script runs fine without showing an error and updated completed with no rows changed.But inner query fetches some values so it should update with records.

I ran query manually in teradata with hard coding parameter values like
Select
table1.a y,
table2.b z
from table1,
table2
where table1.a=table2.a
and PERD_ID IN (200901,200902,200903))

It works fine but its not working when I use PERD_ID IN (:VAR_PERD_ID) in update script.

Could you please anyone can help me out to find the issue?

I am new to this BTEQ so pls let me know if you have any idea.

Thank you in advance.
3 REPLIES
N/A

Re: BTEQ Related query

Hi,

Have you use the REPEAT statement in between your IMPORT AND USING statements? Sample BTEQ is given below

.LOGON demotdat/thiru,thiru;
.IMPORT VARTEXT ',' FILE=C:\ABC.TXT;
.REPEAT *
USING
emp_no (VARCHAR(6)),
emp_name (VARCHAR(25)),
emp_street (VARCHAR(25)),
emp_city (VARCHAR(25))

INSERT INTO mydb.mytable
values
(
:emp_no,
:emp_name,
:emp_street,
:emp_city
);

.QUIT;

Re: BTEQ Related query

Hi,

thank you for your response.

Yes I tried using Repeat between import and Using.

Actually, My question is :var_perd_id has list of period ids list like 200901,200902 etc but why is it not passing in update script?

Example: It is working fine if :var_perd_id has one period id.

It is not working if :var_perd_id has multiple period ids with separated commas.

So i just want the reason why is it not working?

Can you pls let me know if you have an idea regarding this?

Thank you in advance

Re: BTEQ Related query

Hi,

If the number of values that you import is FIXED, then you can go ahead and read it in separate variable name.

USING
( FIELD_1 CHAR(8),
FIELD_2 CHAR(8),
FIELD_3 CHAR(8)
)

Update table.X
(Select
table1.a y,
table2.b z
from table1,
table2
where table1.a=table2.a
and PERD_ID IN (:FIELD_1, :FIELD_2, :FIELD_3))T1
where X.a=T1.y;

We can't use single variable have multiple values in IN parameter.