Recent content by aravindk

this is what i got so far: select * from cust_sales a, cust_promo b where a.customer_key = b.customer_key and b.promo_dt = (select max(promo_dt) from cust_promo x where x.customer_key = b.customer_key and a.invoice_dt <= x.promo_dt) and I get 56 inv1 2010-05-15 00:00:00.000 56 promo2...

Hello all, I need help with a correlated query. I am trying to join two tables on a key and get a record that is the latest from the other table. Let me try to explain this with an example. I have a cust_sales table with multiple records per customer. I also have promo table that have multiple...

Hello, We usually build the cube on windows ftp the .mdc file to solaris unix server. I would like to take advantage of the 'copy and activate' feature in 8.4 and wondering how do I add the unix location in Windows Transformer. I know this is possible since the user guide talks about it. Has...

Can someone help me understand how I can retrieve data from a xml data type? In this example below, I want to retrieve AddressNo & Area from the table. Sample code below. I am using Sql server 2005. I need a select statement returns the values from the column. Any help that you can provide is...

Looks like I can actually make this work. Thanks a lot!

I need to join two tables where when the join condition is true, it should return YES and returns NO if it doesnt. Let me try to give an example: I have BOOK_RENTALS which contains customer names and books: JOHN SMITH, BOOKA JOHN SMITH, BOOKB JOHN SMITH, BOOKC and I have a BOOK_MASTER: BOOKA...

I will try reorganizing the index and try the query again. Also thanks for forwarding me the metalink document. I appreciate it.

Santa, "I tried this in another model environment." - thats what I said in the beginning of my prior posting. Pl. go back and read. Btw, Thanks for your valuable input.

So apparently some threshold was crossed whereby the optimizer concluded the index would actually slow things down." I am trying to find out what that threshold is and adjust it so that the optimizer helps my query rather than hurt it. By the way, the I added a use_nl hint to the query and...

A) How can a FTS be more efficient when it returns only .1% of the table? B) When it used the index (before analyze) it ran the query in 200 msecs and when it did a FTS (after analyze) it took 13 secs for returning the same output. So obviously FTS was not more efficient than using the index...

I tried this in another model environment. When I first tried the same query, the explain plan used the index as expected. I did not use any hints. Now I looked at the stats for the table and realized that the table was never analyzed. Now I went ahead and analyzed the table and retried the...

Here is the actual query: select sum(mtd_written_prm_am) /*+ index(t5 covsumm_monthsk), index(t1 idx_tdate_year_month_ct)*/ from "MISP"."TCOVERAGES_SUMMARY" T5, "MISP"."TDATE" T1 where (T5."MONTH_SK" = T1."DATE_SK") and (T1."YEAR_MONTH_CT" = 200312) In this the TCOVERAGES_SUMMARY has nearly...

Well, thats my job ;-)

LAST_ANALYZED 2/7/2007 11:24:24 AM I ran the analyzer few times today :-( hoping that'd help.

Hi all, I have two tables using simple inner join. For example, SELECT SUM(ACTUAL_AM) FROM SALES_SUMMARY SS, CALENDAR_TABLE CT WHERE SS.MONTH_KEY = CT.CALENDAR_KEY AND CT.YEARMONTH = 200612 I have an index on the sales_summary table on the month_key column. However, when i see the explain...