| sql query failing with order by [message #293629] |
Mon, 14 January 2008 05:34  |
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
|
Member |
|
|
I'm trying to use STAR transformation ;
SELECT x.*
FROM
dim_products p,
dim_distribution d,
nisa_times t,
fact_trans x
WHERE 1=1
AND p.npc=x.product_key
AND d.member_branch=x.distribution_key
AND x.dated = t.dated
AND p.supplier_db_name='ACDOCO'
--AND d.last_trans_date IS NOT null
AND t.dated BETWEEN To_Date('01-JAN-06') AND To_Date('31-DEC-07')
ORDER BY
d.retail_or_wholesale, d.group_name, d.tv_region, d.member_name,
p.comm_grp_text, p.comm_sub_grp_text, p.comm_sub_sub_grp_text, p.bottom_descpack,
x.dated
;
Explain plan shows it using a Temp Table Transformation AND'ing the BITMAP indexes but when I actually run it I get : "ORA-03113: end-of-file on communication channel". Remove the Order By and it works OK. ALTER SESSION SET star_transformation_enabled=false and it works OK. But it doesnt like the star transform with the Order By. Any Ideas ?
|
|
|
|
|
|
| Re: sql query failing with order by [message #293664 is a reply to message #293629] |
Mon, 14 January 2008 08:26  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| mwansalovewell wrote on Mon, 14 January 2008 06:34 |
AND t.dated BETWEEN To_Date('01-JAN-06') AND To_Date('31-DEC-07')
|
First thing you should do would be to use proper syntax for the TO_DATE function. The TO_DATE function requires a format mask.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
