SQL QUERY

[Former Member]

Hi All,

I need to implement the below SQL Query inside dataflow in BODS and Load the data into a Temporary Table. Can anyone suggest me step by step process.

select 'Eval_Revenue' as APPLICATION_NAME,

'On Track' AS KPI_TYPE,NULL AS KPI_VALUE, NULL as PTYPE,

(convert(decimal(15,0), (sum(NetRevenue)/1000)*0.08) -isnull(A.KPI_NUMBERS,0)) as KPI_NUMBERS,

b.customername from AlexDM.dbo.vwSVEvalMasterDashBoard B,(select

convert(decimal(15,0), sum(NetRevenue)/1000) as KPI_NUMBERS,customername from AlexDM.dbo.vwSVEvalMasterDashBoard

where EvalStatusName IN ( 'Complete') and

EvalQuarterSK between DATEADD(q, DATEDIFF(q, 0, getdate()), 0) and

DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, getdate()) + 1, 0)) group by customername)A

where

EvalQuarterSK between DATEADD(q, DATEDIFF(q, 0, getdate()), 0) and DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, getdate()) + 1, 0))

and A.customername = B.customername

group by B.customername,A.KPI_NUMBERS

regards

karthik

[akhileshkiran · Accepted solution]

Hi

Can you post some sample data and post the database result output query.

Regards,

Akhilesh Kiran.

[Former Member · Accepted solution]

The Above attached is the result of View from Database its continuation of columns from first image to second image.

Find below the SQL query output result

regards

karthik

[akhileshkiran · Accepted solution]

Hi

Source:

ETL DF Design:

Query logic follows below:

Create a NEW OUTPUT Column as APPLICATION_NAME and Map it as 'Eval_Revenue'. Same follows for KPI_TYPE

KPI_TYPE:

For KPI_VALUE and PTYPE map it with null

KPI_NUMBERS Logic: Create a NEW OUTPUT COLUMN as KPI_NUMBERS with Data Type Decimal(15,0)

ifthenelse( SQL_QUERY.NETREVENUE is null, 0 ,((sum(SQL_QUERY.NETREVENUE)/1000))*0.08 )

Drag and Drop the CUSTOMERNAME Directly from the Source.

WHERE Class Logic

((SQL_QUERY.EVALQUATERSK >= to_date(to_char(((((year(sysdate()) || '.') || month(sysdate())) || '.') || '01'), 'yyyy.mm.dd'), 'yyyy.mm.dd'))
AND
(SQL_QUERY.EVALQUATERSK <= to_date(add_months(last_date(sysdate()), 2), 'yyyy.mm.dd')))

Example : Output as EVALQUATERSK>= 2016-04-01 and 2016-06-30

Group By Tab:

Query_1_1 logic follows below:

Create a NEW OUTPUT Column as KPI_NUMBERS with Data Type :decimal (15,0)

Where Condition :

SQL_QUERY_1_1.EVALSTATUSNAME IN ( 'Complete')
AND
((SQL_QUERY_1_1.EVALQUATERSK >= to_date(to_char(((((year(sysdate()) || '.') || month(sysdate())) || '.') || '01'), 'yyyy.mm.dd'), 'yyyy.mm.dd'))
AND
(SQL_QUERY_1_1.EVALQUATERSK <= to_date(add_months(last_date(sysdate()), 2), 'yyyy.mm.dd')))

Group By Tab:

QUERY_1(Joining the two Query Transforms): Map all the fields to the Output.

Join Pairs: you need to specify the type of join and join condition.

Final Output:

Regards,

Akhilesh Kiran.

[Former Member]

Akhilesh,

This doesnt help to fetch the required Output.

regards

Karthik

[Former Member]

Hi Akhilesh,

There was a change in the database on the table and now the above SQL query results with the below output.

but wen i use the above SQL logic in BODS as you Guided me previously am fetching 7 records in the Target.

Can you pls help me out to resolve this issue. wherein my data in the Target should be only one Record to be loaded.

[akhileshkiran]

Hi

Try to run the job in debug mode and Analyze the data where its overflowing or you are missed any logic in the query level where condition.

Regards,

Akhilesh Kiran.

[Former Member]

Hi Akhillesh,

Find the below query and the logic missed.

select 'Eval_Revenue' as APPLICATION_NAME,

'On Track' AS KPI_TYPE,NULL AS KPI_VALUE, NULL as PTYPE,

(convert(decimal(15,0), (sum(NetRevenue)/1000)*0.08) -isnull(A.KPI_NUMBERS,0)) as KPI_NUMBERS,

b.customername from AlexDM.dbo.vwSVEvalMasterDashBoard B,(select

convert(decimal(15,0), sum(NetRevenue)/1000) as KPI_NUMBERS,customername from AlexDM.dbo.vwSVEvalMasterDashBoard

where EvalStatusName IN ( 'Complete') and

EvalQuarterSK between DATEADD(q, DATEDIFF(q, 0, getdate()), 0) and

DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, getdate()) + 1, 0)) group by customername)A

where

EvalQuarterSK between DATEADD(q, DATEDIFF(q, 0, getdate()), 0) and DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, getdate()) + 1, 0))

and A.customername = B.customername

group by B.customername,A.KPI_NUMBERS.

From the Above Query the sub query with Bold letters should be Minus with

-isnull(A.KPI_NUMBERS,0)).

can you help me how do we define this in BODS and implement this logic.

regards

Karthik

[akhileshkiran]

Hi

see the below logic is implemented. It is not possible me to check without having a proper data and your code. Share your ETL code as .ATL. So that i can have a view on you ETL Design. or-else you can IM me.

Regards,

Akhilesh Kiran.

[Former Member]

Akhilesh,

Can i share my screen with you thru skype

regards

karthik

[akhileshkiran]

Sure

Regards,

Akhilesh Kiran.

[Former Member]

your skype ID pls

regards

karthik

[akhileshkiran]

Skype ID: akhileshkiran0609

[Former Member]

I have sent you the request in skype

[akhileshkiran]

Hi

for KPI NUMBERS Logic.

Regards,

Akhilesh Kiran.

[Former Member]

Akhilesh Am very Thankful to you... you have helped me a lot and the step by step process worked succesfully.

regards

karthik

[akhileshkiran]

Hi ,

Can you please close the thread by marking it helpful or answered to the threads. So that it will be helpful for others.

Regards,

Akhilesh Kiran.

[Former Member]

How do i define the below in BODS totally blank.

select 'Eval_Revenue' as APPLICATION_NAME,

'On Track' AS KPI_TYPE,NULL AS KPI_VALUE, NULL as PTYPE,

(convert(decimal(15,0), (sum(NetRevenue)/1000)*0.08) -isnull(A.KPI_NUMBERS,0)) as KPI_NUMBERS,

[former_member211387]

This is all field mapping in the output schema of the query transform. Pull in the original fields and then rename the fields in the output schema mapping of the query transform.

[Former Member]

can any one help me, i can share my screen and guide me step by step process to achieve this task. Its emergency

[akhileshkiran]

Skype ID: akhileshkiran0609

[former_member211387]

Use the SQL transform with the relevant datastore. The fields can then be mapped to a target table either with a query transform or directly.

R

[Former Member]

I Have the View as my Source in Table form and i should not use SQL Transform.I need to achieve this task without SQL Transform

[former_member211387]

In which case you can bring in the view into the dataflow twice as source and then map each to separate query transforms and then join it in a third query transform which can be loaded to a target table. Something like the screenshot below

R

[Former Member]

How do i use a Join Condition when its transferred to a Third query Transfom

[Former Member]

Am New to BODS to use join condition in BODS

[akhileshkiran]

Hi

If you open the Query Transform. You can find the options in the From Tab.

Where you can define the join condition.

Regards,

Akhilesh Kiran.