Using SQL query directly in DI

[Former Member]

Hi,

I have a five long SQL query that extracts columns from 2 tables based on conditions and creates 5 new tables with new columns. The query looks some thing like this:

select a.num_1, b.unid, c.tycod, min(b.csec) - min(a.csec) turnout into

#tab1

from un_hi a

left join un_hi b on a.num_1 = b.num_1

and ((a.unid = b.unid and left(b.unid,1) not in ('E','Q') and

left(a.unid,1) not in ('E','Q'))

or (right(a.unid,len(a.unid)-1) = right(b.unid,len(b.unid)-1)

and left(b.unid,1) in ('E','Q') and left(a.unid,1) in ('E','Q')))

and b.unit_status = 'ER'

left join aeven c on a.num_1 = c.num_1 and c.rev_num = 1

where a.num_1 like 'F011%' and a.unit_status in ('DP','XE')

group by a.num_1, b.unid, c.tycod;

Is there any way i can directly incorporate the above query in DI and run? The source file is excel sheet and output is SQL server.

Should i use the script in the work flow to execute the query?

Appreciate your inputs.

Thanks,

Arun

[Former Member]

Yes, you an execute this query statement using the SQL script (icon with the word "SQL", can be found under Transformation tab). Just copy your query & paste it into the script area.

[Former Member]

Thank you Yoong.

My source file is Excel, so in that case does SQL transfrom work? I was thinking that the SQL transform needs a source file from database.

Arun

[Former Member]

Does it mean your source file contain actual data or contain the SQL script?

[Former Member]

I have an excel source file with around 11 tabs and over 50 columns. I also have 5 SQL query, which extracts data from different columns and tabs of the excel, generating 1 table with 6 columns as output. The query extracts data as well as creates output tables.

I was wondering, whether this is possible in DI.

Like, i will keep an excel file as a source and insert the SQL query (i do not know where i will give the query. In SQL transform or Scripts?) that generates one table and 6 columns from excel sheet on to SQL server.

Arun

[Former Member]

I am not sure if BODI can execute SQL directly from Excel sheet as source...

Though, you may try to create a File Format for your excel sheet (& multiple tabs), set it as source then use the Query Transform to apply your SQL?

As stated in the Designer Guide:

The Query transform can perform the following operations:

u2022 Choose (filter) the data to extract from sources

u2022 Join data from multiple sources

u2022 Map columns from input to output schemas

u2022 Perform transformations and functions on the data

u2022 Perform data nesting and unnesting

u2022 Add new columns, nested schemas, and function results to the output

schema

u2022 Assign primary keys to output columns

Hope this helps!

[Former Member]

Thanks Yoong. Will try that.

Arun