Hi All, I have created a view using the following syntax.
CREATE OR REPLACE VIEW tax_information (transaction_key, transaction_type, pid, tax_description_1, tax_amount1, tax_description_2, tax_amount2, tax_description_3, tax_amount3, tax_description_4, tax_amount4, tax_description_5, tax_amount5, tax_description_6, tax_amount6 ) AS SELECT DISTINCT a.transaction_key, a.transaction_type, a.pid, x1.tax_description_1, x1.tax_amount1, x2.tax_description_2, x2.tax_amount2, x3.tax_description_3, x3.tax_amount3, x4.tax_description_4, x4.tax_amount4, x5.tax_description_5, x5.tax_amount5, x6.tax_description_6, x6.tax_amount6 FROM (SELECT transaction_key, pid, tax_description AS tax_description_1, tax_amount AS tax_amount1 FROM rottaxdetail WHERE display_order = 1) x1, (SELECT transaction_key, pid, tax_description AS tax_description_2, tax_amount AS tax_amount2 FROM rottaxdetail WHERE display_order = 2) x2, (SELECT transaction_key, pid, tax_description AS tax_description_3, tax_amount AS tax_amount3 FROM rottaxdetail WHERE display_order = 3) x3, (SELECT transaction_key, pid, tax_description AS tax_description_4, tax_amount AS tax_amount4 FROM rottaxdetail WHERE display_order = 4) x4, (SELECT transaction_key, pid, tax_description AS tax_description_5, tax_amount AS tax_amount5 FROM rottaxdetail WHERE display_order = 5) x5, (SELECT transaction_key, pid, tax_description AS tax_description_6, tax_amount AS tax_amount6 FROM rottaxdetail WHERE display_order = 6) x6, rottaxdetail a WHERE a.transaction_key = x1.transaction_key(+) AND a.transaction_key = x2.transaction_key(+) AND a.transaction_key = x3.transaction_key(+) AND a.transaction_key = x4.transaction_key(+) AND a.transaction_key = x5.transaction_key(+) AND a.transaction_key = x6.transaction_key(+); When I run this query my system gets hanged and I am not getting any result. Totally there are 25360 records in rottaxdetail. when I run the following query alone it retreives 128 records however the execution time is 11 seconds (!!!) select TRANSACTION_KEY, TAX_DESCRIPTION as Tax_description_1, TAX_AMOUNT as Tax_Amount1 from ROTTAXDETAIL where display_order = 1 when I run the following query the system hangs.. select distinct A.TRANSACTION_KEY, x1.Tax_description_1, x1.Tax_Amount1, x2.Tax_description_2, x2.Tax_Amount2 from (select TRANSACTION_KEY, TAX_DESCRIPTION as Tax_description_1, TAX_AMOUNT as Tax_Amount1 from ROTTAXDETAIL where display_order = 1)x1, (select TRANSACTION_KEY, TAX_DESCRIPTION as Tax_description_2, TAX_AMOUNT as Tax_Amount2 from ROTTAXDETAIL where display_order = 2)x2, ROTTAXDETAIL A where A.TRANSACTION_KEY = x1.TRANSACTION_KEY(+) and A.TRANSACTION_KEY = x2.TRANSACTION_KEY(+); Where did I go wrong, can any one, help me out? Regards, Karthik... --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
