This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / 请教一个SQL 的问题：Column I in table A is CHAR, data like this: 12345, YUK-ming, SETT.28/03, Paul’s payment……
Column II in table B is NUMBER, data like this: 12345, 23456, 2345, 23455…..
I want put the comparison in the WHERE Claus like this: where tableA.columnI = tableB.column II (i.e. I want the data related to 12345 back)
I tried: where tableA.columnI = to_char(tableB.column II, ‘99999’), it returns 0 rows, so it is not work that way.
Does anyone has any better ideas on this? Thanks very much!
-ohoracle(叶满秋池);
2008-7-22
{497}
(#4571701@0)
-
你没有说明你用的数据库和版本。如果是老版的Oracle，你可以试验一下把to_char() 放到select 里面并起一个别名，例如 test, where tableA.column I= test
-cunny(Ken);
2008-7-22
(#4571758@0)
-
By using to_char(tableB.column II, ‘99999’) you get a leading space so '12345' <> ' 12345'. Please use to_char(tableB.column II, ‘FM99999’) instead.
-cloud2001(cozumel);
2008-7-22
(#4571879@0)
-
Thank you Ken and cozumel. I tried both and I still get 0 row back. By the way, my database is 9i Oracle.
-ohoracle(叶满秋池);
2008-7-22
(#4572029@0)
-
Please use TRIM for the first column because of CHAR and remove 99999: where TRIM(tableA.columnI) = to_char(tableB.column II)
-guanshui88(约定);
2008-7-22
(#4572063@0)
-
why not convert column a to number?
-c1xwy(洪兴罩俺去战斗);
2008-7-22
(#4572071@0)
-
Column I in table A is CHAR, data like this: 12345, YUK-ming, SETT.28/03, Paul’s payment……
-guanshui88(约定);
2008-7-22
(#4572082@0)
-
Thank you Guanshui88. I will try that.
-ohoracle(叶满秋池);
2008-7-22
(#4572225@0)