| Outer joint using upper and trim [message #7552] |
Fri, 20 June 2003 08:40  |
Sushil Tripathee
Messages: 1
Registered: June 2003
|
Junior Member |
|
|
I tryint to joint two tables usint trim and upper function and it didn't like it.
Here is my code:
SELECT
P.WORK_ITEM_HEADER_ID,
P.WORK_TYPE_NM,
P.PRIORITY_CD,
P.LOCATION_NM,
P.CREATED_DT,
P.TICKET_ID,
P.WORK_REQUEST_DSC,
P.WDS_APPLICATION_CD,
E.REGION,
C.CREATED_USER_NM,
C.CREATED_DT AS COMPLETED_DT
FROM WDS_PM_CM P, WDS_COMPLETE C, FIELD_EMPLOYEE E
WHERE P.WORK_ITEM_HEADER_ID = C.WORK_ITEM_HEADER_ID
AND TRIM(UPPER(C.CREATED_USER_NM)) = TRIM(UPPER(E.USER_ID)) (+)
AND STATUS = 'Completed';
Here is the error message I got.
ERROR at line 16:
ORA-00933: SQL command not properly ended
|
|
|
|
| Re: Outer joint using upper and trim [message #7553 is a reply to message #7552] |
Fri, 20 June 2003 08:47  |
Martin Chadderton
Messages: 35
Registered: May 2003
|
Member |
|
|
Hello.
Yes, i've noticed this before, it's because you have to put the (+) next to the column not the function on the column, i.e.
SQL> SELECT *
2 FROM
3 t1, t2
4 WHERE TRIM(t1.a) = TRIM(t2.a) (+);
WHERE TRIM(t1.a) = TRIM(t2.a) (+)
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
But the following works :
SQL> SELECT *
2 FROM
3 t1, t2
4 WHERE TRIM(t1.a) = TRIM(t2.a (+))
5 /
no rows selected
Of course, the 9i ANSI OUTER JOIN syntax works ok like this :
SQL> SELECT *
2 FROM
3 t1 LEFT OUTER JOIN t2 ON ( TRIM(t1.a) = TRIM(t2.a) );
no rows selected
Regards
M.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
