Home » SQL & PL/SQL » SQL & PL/SQL » sql to alter (oracle 10g,unix)
sql to alter [message #447512] Mon, 15 March 2010 10:46 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
HI aLL

please find the below sql code.Here i am getting values for certain columns by matching to a key in a look up table but i am taking a alias and doing a join mulliple times hence please advice how to avoid it...

select 
      a.Order_Num,
      a.Order_Type_KEY,
      a.Order_Version_Num,
      a.Order_Date,
      a.Order_Item_Num,
      b.Parent_order_num,
      a.action_key Order_Item_Action_code,
      a.Product_key,
      e.product_name,
      a.Asset_key,
      c.edw_desc order_status,
      d.edw_desc Order_Item_Status,
      b.CURRENT_ORDER_STATUS_DATE Order_Status_Date,
      a.CURRENT_ORDER_ITEM_STATUS_DATE Order_Item_Status_Date,
      a.Cust_agreed_date,
      b.Sales_Channel,
      h.edw_desc Cessation_Reason,
      g.edw_desc Cancellation_Reason,
      a.Telephone_num,
      b.Source_System
      from int_owner_il.l_edw_order b,
      $view.l_edw_order_item a,
      $view.LOOKUP_ORDER c,
      $view.LOOKUP_ORDER  d,
      $view.LOOKUP_ORDER  g,
      $view.LOOKUP_ORDER  h,
       $view.product e
      where 
       b.Cust_Class='Customer Group' and 
       a.Order_num=b.Order_num and
       a.Order_Type_key=b.Order_Type_key and
       a.Order_version_num=b.Order_version_num and
       a.Order_Date=b.Order_Date and
       e.orig_product_code=a.Product_key and
       c.edw_lkp_key=b.CURRENT_STATUS_KEY and
       d.edw_lkp_key=a.CURRENT_STATUS_KEY and
       g.edw_lkp_key=b.CANCELLATION_REASON_DESC_KEY and
       h.edw_lkp_key=a.ORDER_ITEM_CESS_REASON_KEY



Thanks

Re: sql to alter [message #447514 is a reply to message #447512] Mon, 15 March 2010 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT a valid ORACLE SQL statement.

As you retrieve information from your lookup table by different keys, you cannot avoid multiple joins.

Regards
Michel
Re: sql to alter [message #447533 is a reply to message #447514] Mon, 15 March 2010 20:17 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

Why this is not a valid sql statement?

and is there no way out other than fetching data from look up like this.

If not whether this will be a correct way
Re: sql to alter [message #447534 is a reply to message #447533] Mon, 15 March 2010 20:19 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>Why this is not a valid sql statement?

post proof it is valid.

sqlplus throws errors when given posted code.
ok [message #447535 is a reply to message #447534] Mon, 15 March 2010 21:22 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
ok.. some syntax errors ,i will change it accordingly but from performance point of view and from data retriving perspective if i follow this method whether this is ok...as i have to refer the table multiple times...
Re: ok [message #447536 is a reply to message #447535] Mon, 15 March 2010 22:09 Go to previous message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
If you get the desired result set, consider yourself fortunate.

Some thing slow & correct is better than wrong results quickly
Previous Topic: search spaces in column
Next Topic: datafile for External table
Goto Forum:
  


Current Time: Mon Sep 26 07:50:20 CDT 2016

Total time taken to generate the page: 0.12937 seconds