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...
November 30, -0001
View Thread Other 7 Replies on scn.sap.com
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!
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? For Ex: ------------------------+ DEPTNO| ENAME | JOB | ------|-------| --------| 10 | JAMES | MANAGER | 10 | BLAKE | CLERK | 10 | FORD | SALESMAN| 20 | SCOTT | MANAGER | 20 | ADAMS | CLERK | 20 | KING |
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
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
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
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
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
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
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 :- 184.108.40.206.0 Thanks In Advance RamitI mean to say that the query is made by
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
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
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_
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
Database version Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production PL/SQL Release 18.104.22.168.0 - Production CORE 22.214.171.124.0 Production TNS for Linux: Version 126.96.36.199.0 - Production NLSRTL Version 188.8.131.52.0 - Production Ques
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
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
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-
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
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