topic Re: Query in tDBInput to select multiple files in Data Quality

Query in tDBInput to select multiple files

Anonymous — Sat, 16 Nov 2024 07:10:10 GMT

Hi.

I hope you can help me. I'm new working with TOS and with databases

The source of my data are some files (.DBF format) in a folder located in my PC (C:\), it's names can change so I need that my query take every file in that folder. Now, as I know the name files, I have this query:

"SELECT * FROM 20180222AL
union all
SELECT * FROM 20180223AL
union all"

Where "20180222AL" and "20180223AL" are the names of my files.

But, as I said before, in the real life, the names are going to change and I need that my query take all the files in the folder.

¿What can I do?

Thanks!!

Re: Query in tDBInput to select multiple files

vapukov — Fri, 30 Nov 2018 05:08:05 GMT

you could split job into 2 parts - prepare sql, and run sql

part 1:

tFileList - and iterational prepare statements (need fix final code):

in tJavaFlex code:

if (context.sql_text.equals("")){
	context.sql_text="SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE")); 
	}
else {
	context.sql_text=context.sql_text+" \nUNION ALL \n"+"SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE"));
	};

after first part context.sql_text will contain sql code:

SELECT * FROM cp1251.dbf 
UNION ALL 
SELECT * FROM dbase_30.dbf 
UNION ALL 
SELECT * FROM dbase_8b.dbf 
UNION ALL 
SELECT * FROM dbase_f5.dbf

for all files in folder

in a second part just put instead sql code

context.sql_code

without double quotes around

UPD:

instead of ((String)globalMap.get("tFileList_1_CURRENT_FILE")), you can use ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")) for include full path for files

Re: Query in tDBInput to select multiple files

Anonymous — Tue, 04 Dec 2018 16:27:11 GMT

Hi vapukov,

Thank you for your answer. I have been trying to solve my problem with your suggestion since you answered me but I just couldn’t, so here I am again.
I put in my job the tFileList component with these basic settings:

Then, the tJavaFlex with these basic settings:

When I run the job, I get this error (sql_text cannot be resolved or is not a field):

What I’m doing wrong?

In addition, I don't know how to set the component tFixedFlowInput. I did it like this, but I don’t know if I did it right:

I'm really sorry to be so confused, but as I was saying, I'm just beginning to learn from TOS and java programming. Thanks for your help!

Re: Query in tDBInput to select multiple files

vapukov — Tue, 04 Dec 2018 18:48:33 GMT

hi,

sql_text cannot be resolved or is not a field

it means - sql_text context variable (and any other), must be declared before used

you must define sql_text in context tab:

Re: Query in tDBInput to select multiple files

Anonymous — Mon, 10 Dec 2018 20:54:57 GMT

Thank you! You were very helpful.