Using SQL query directly in DI


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
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.


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
u2022 Assign primary keys to output columns
Hope this helps!

November 30, -0001

Using SQL query directly in DI

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 fr

How to hide repeated details using SQL Query?

How to hide repeated details using SQL Query? For Ex: ------------------------+ DEPTNO| ENAME | JOB | ------|-------| --------| 10 | JAMES | MANAGER | 10 | BLAKE | CLERK | 10 | FORD | SALESMAN| 20 | SCOTT | MANAGER | 20 | ADAMS | CLERK | 20 | KING |

Retreving Hexadecimal Values from a table using SQL query.

Hi, I'm looking for a query to retreive Hexadecimal values contained in a column using SQL query. The column contains the values of all types, alphanumeric, numeric and hexadecimal. I need to retreive only hexadecimal values. any help to me in this r

How to get tax break up of TDS using SQL query ?

Hi all, We are developing a TDS report using SQL query Report will contain VendorCode,Date(ap inv date),Vendor name, Bill value,TDS Amount, Bill Value – 100.000, TDS (2%) - 2.000, TDS Surcharge(10% on TDS) - 0.2, TDS Cess(2%(TDS+TDS Surcharge)) - 0.0

How to pull Roles and Policies from backend using SQL query in OIA

Hello, I have Roles and Policies defined in OIA with mapping each other and there is no direct extract report from OIA Web console. Is there any oracle SQL query by which we can get the data and filter the Policies based on the role ? Note: We have o

Creating logical view using sql query

hi i'm new in OBIEE, would like to know if its possible to create a custom view (logical table) by writing a sql query (without use the existing tables in the business model). how can i do that? i didnt find the place where i can write the sql manual

Expecting the output in specific manner by using sql query

Right now i am executing the query that is showing an output like this FILTER_CATEGORY NAME alarm     orgObjectTypeRaw alarm     eventType obj_alm     OSS_NAME obj_alm     TYPE obj_imp OOF INDICATOR obj_imp     PORT TYPE obj_nwk     OSS_NAME obj_nwk 

Deploy project using sql query

hi i am trying to deploy project in ssis catalog using below query. Exec [email protected]_name [email protected],@project_name [email protected],@Project_Stream [email protected],@operation_id [email protected]_id out getting error: (1 row(s) affected) Msg 2

Using SQL Query for page item source does not insert populated value

Hi All, Following are the attribute details of a certain page item on an edit form: Display As: Text Field Page Item Source Type: SQL Query Source value Expression: SELECT a from tab where id = 'ABC' Although the field appears populated on the screen

How to use SQL query in ADF

Hi Team, I want to fire one sql query to retrive some data. I cant make VO of that query as its from many DB link. Query is like SELECT query. Can anyone suggest me My Jdev :- Thanks In Advance RamitI mean to say that the query is made by

How to display contineous 10 dates from today date using SQL query

Hi All I need a requirement of displaying 10 dates and numbers contineously, I have written a query for getting 10 numbers like this select rownum from dual where rownum < 10 connect by rownum = level; But i am not able to get the dates, Can any one

Fault cases identification using Sql query

Hi , i have data in below format.using below data to extract the fault cases. operator machine fromdate todate 1491 QC03 2014-09-02 02:51:00.000 2014-09-02 06:10:00.000 1427 QC03 2014-09-02 06:11:00.000 2014-09-02 07:17:00.000 1491 QC03 2014-09-02 11

Charts using sql query

The table on whichn chart is build as a columns namely cen_name,track_name,quantity dispatch_date.My requirement is to build a chart which shows me monthwise the tracks dispatched,so i wrote the query select null the_link, ce_cur_tk_cen_track_id the_

Data Template using sql query

Hi All, I'm trying generate xml data using data template approach. For this do i need to use XSQL to generate xml data? Can some one please tell me what is this XSQL and how it works? Thanks in advance,Hi Srini, Thank you very much for the link, its

How to read repeated tag values using sql query

Database version Oracle Database 11g Enterprise Edition Release - 64bit Production PL/SQL Release - Production CORE Production TNS for Linux: Version - Production NLSRTL Version - Production Ques

How to create a Matrix table using this data in SQL Query Analyzer

Hello all, I have a problem while I am trying to represent my Sql Table namely table1 in Matrix form my table Format is city1 city2 Distance-------------------------------------------------------- Mumbai Delhi 100 Delhi Banaras 50 Mumbai Rajasthan 70

Pl/sql function body returning SQL query - Print function

Hello all, I have pl/sql function body returning SQL query for my reports for my new project that I am developing. We dont have any BI tool or anything for APEX so we use Oracle reports to get the same reports to be printed in PDF format. I had been

MS SQL query to get Subscription list from Operations Manager

Hi Experts, Is there anyway get Subscription list from Operations Manager DB using SQL query?  I lost subscription list, and need to create subscription as it was. Very urgent. Thanks in advance. Regards Karthick MHi, Hope the powershell command Get-

Setting Page Items from SQL Query

Hi, </br></br> I am using "SQL Query (PL/SQL function body returning SQL query)" for a report. In the body of the sql query, after calling a function, I have the following statement: </br></br> APEX_UTIL.SET_SESSION_STATE

How to write sql query to parse xml?

Hi, I am new to this parsing xml. I need to extract value from xml using sql query. Please find the sample xml file. Kindly help me. <?xml version="1.0" encoding="UTF-8"?> <MaterialUpdates>   <Materials>      <Sale