CODEWITHSUNDEEP
sqlteradata
MadireP
MadireP

Reputation: 1

Using Excel row values in Teradata Query

Requirement : Have to select values from 2 or more tables ,In the query where condition should take values from excel records (around 5 k ) .

Query:

     select 
     distinct 
     tableA.x
    ,tableB.phone_nbr,
     tableC.tmp_oper_div_cd 
     from Table A join Table B on A.id= B.iD
     left join table C on A.fc = C.fc
     left join table h on fc.qr = h.qr
     --h.hldx_cd=s.htl_cd
    where tableB.phone_nbr like  any 
    ('%35051111%' ,'%35051155%','%35051113%','%35051190%')
    and h.hldx_cd='TYOHC'

In the above query ,(pls ignore join flaws etc.,) hldx_cd and Phone_nbr values are from excel and phone_nbr values have no proper format . In the excel, we are given around 5 k hldx_cd with multiple phone numbers for each hotel code.

Please let me know the logic to achieve this. Thanks in advance.

Upvotes: 0

Views: 577

Answers (1)

JNevill
JNevill

Reputation: 50019

Create a temp table to hold your phone numbers:

CREATE MULTISET VOLATILE TABLE phones (pn VARCHAR(20), hldx_cd VARCHAR(20)) ON COMMIT PRESERVE ROWS;

In excel make an INSERT statement for each phone number:

="INSERT INTO phones VALUES ('" & A1 & "', '" & B1 & "');"

Assuming you list of phone numbers is in column A and it's hotel code is Column B here, copy that thing down to make 5000 insert statements. Then copy them into your sql client and run them. It will take a while and your DBA will hate you. To keep the DBA off your back consider making a regular table and using fastload, mload, tpt, or bteq to import your data. Obviously a little learning curve (although Teradata Studios would make it a snap) and assuming you have the security to CREATE TABLE in at least on database on the server.

Now you can join to get your matches:

SELECT DISTINCT tableA.x
    ,tableB.phone_nbr
    ,tableC.tmp_oper_div_cd
FROM TABLE A
JOIN TABLE B ON A.id = B.iD
LEFT JOIN TABLE C ON A.fc = C.fc
LEFT JOIN TABLE h ON fc.qr = h.qr
INNER JOIN phones ON tableB.phone_nbr = phones.pn AND h.hldx_cd = phones.hldx_cd;

You don't really have a whole lot of other good options here because your comparison data is big and stuck over in excel where it isn't doing you any good as far as Teradata is concerned.

Upvotes: 1

Related Questions