custom SQL in ODBC satge

dr46014 · Post by **dr46014** » Mon Mar 12, 2007 11:25 am

hi all
i have a table attr_fact where i am storing 52 weeks of history.I want to pull data from that table and put it in a sequential table.I need only 13 weeks(latest 13 weeks) of data.I am having a field week_code in that table which is being populated by look up on time_dim table.time_dim table is a table where for each fiscal date there is a week_code means 365 days are there and for each day corresponding week_code is there.
Fiscal_date week_code
20070101 200701
20070102 200701
....
20070110 200702
.....
like tis..........
week code ranges from 1 to 52/53 for a year..
200701,200702............200752
how can i write a SQL that would ask me data_date as a run time parameter and using that value i ll find the 13 distinct weeks from the time_dim and fetch data for those 13 weeks.
i am able to find the week_code from sysdate by using select (sysdate,'YYYYIW') from dual.but that value is different from the value in the table.So in my SQL i want to refer the table value.
select * from attr_fact where week_code between.........and............
1st blank should give me 13 distinct weeks back to the recent week and second blank will refer to recent week.is it possible to write a subquery there so that it will give the desired result.

[/list]

kcbland · Post by **kcbland** » Mon Mar 12, 2007 11:39 am

Subselect? select * from your table where somedate between (select startdate from xyz) and (select enddate from xyz)

ganesh123 · Post by **ganesh123** » Mon Mar 12, 2007 11:44 am

Why you want to do custom SQL ?

Use generated SQL and use a constraint in TX..

Code: Select all

sv1=right(in.week_code,2)
sv2= if sv1>38 and sv1<53 then 1 else 0

Use this as constraint in output link.

dr46014 · Post by **dr46014** » Mon Mar 12, 2007 11:48 am

actually i want to pull the data on the basis of week_code.
where week_code between recent week_code and week_code 13 weeks before.
i want to pass data_date and extract the week_code values refering to another table time_dim

dr46014 · Post by **dr46014** » Mon Mar 12, 2007 11:52 am

i think i didnot explain my requirements clearly..
the time_dim table contains week_code for each fiscal date.
and week code ranges from 200701.......200752,200801......200852 and so on.i need to run the job every month and every month i need latest 13 weeks of data.So its better to write a where condition in the data

kcbland · Post by **kcbland** » Mon Mar 12, 2007 12:04 pm

A 2? My first answer rates a 2? Your issue is how to write a query that uses subselect to fetch between values. Do you want me to write the SQL statement FOR you? I don't even know your database or all of your table and column names.

You need to know your current date so that you can compute 13 weeks prior. So, how would you write that SQL?

If you didn't like my first SQL, here's another way:

select * from attr_fact where week_code IN (select week_code from time_dim where time_dim_date between sysdate and sysdate - 13 weeks)

Does this rate higher than a 2?

DSguru2B · Post by **DSguru2B** » Mon Mar 12, 2007 12:09 pm

Pass your date in the format YYYYMMDD. Your query will look something like this

Code: Select all

select * from myTable
where week_code between 
(SELECT week_code FROM time_dim where
FISCAL_DATE = #dateparam#) AND
(SELECT week_code FROM time_dim where
FISCAL_DATE = TO_CHAR(TO_DATE(#dateparam#,'YYYYMMDD') - (7*13), 'YYYYMMDD'))

NOTE: Query written assuming your working with ORACLE.

dr46014 · Post by **dr46014** » Mon Mar 12, 2007 12:15 pm

this is what i was looking for...
thanks a lot for proving different approaches

DSguru2B · Post by **DSguru2B** » Mon Mar 12, 2007 12:16 pm

Actually it was all Ken. I just translated his suggestion in code. Thanks Ken

kumar_s · Post by **kumar_s** » Mon Mar 12, 2007 9:44 pm

Would you mind marking the topic as resolved, thanks.