How can I use same SQL Query (which contains left join) for different database?
0 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Lakshmanan_Sakthivel
Posted On:   Monday, April 2, 2001 06:59 PM

I want to use following SQL Query to get results from MSSQLServer and Oracle. Its working fine with MSSQLServer. But oracle throws ORA-01722:invalid number error at table5.value as numeric. MSSQLServer and Oracle uses different way for joining tables. How can I use same query for different database? The query is ------------ SELECT DISTINCT table1.d_id, table1.index_value, table3.name, table4.name, table4.index_label FROM my_table1 table1, my_table2 val, my_table3 table3, my_table4 table4, my_table2 table5 WHERE table1.f_id = 1 AND table1.r_id = 16 AND table1.f_id = table4.f_id AND table1.d_id = table2.d_id AND table1.rbs_id = rbs.rbs_id AND table1.d_id = table5.d_id AN   More>>

I want to use following SQL Query to get results from MSSQLServer and Oracle.

Its working fine with MSSQLServer. But oracle throws ORA-01722:invalid number error at table5.value as numeric.

MSSQLServer and Oracle uses different way for joining tables. How can I use same query for different database?


The query is
------------

SELECT DISTINCT table1.d_id, table1.index_value, table3.name, table4.name,
table4.index_label FROM my_table1 table1, my_table2 val, my_table3 table3,
my_table4 table4, my_table2 table5 WHERE table1.f_id = 1 AND table1.r_id = 16
AND table1.f_id = table4.f_id AND table1.d_id = table2.d_id AND
table1.rbs_id = rbs.rbs_id AND table1.d_id = table5.d_id AND
(((table5.field_id = 185) AND (CAST(table5.value AS NUMERIC) BETWEEN 0 AND 200)))

-----------------------
where as table5.value is varchar(2000) type.


thanks in advance
Lakshmanan

   <<Less
About | Sitemap | Contact