OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Syntax help!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Syntax help!! [message #372149]

Tue, 16 January 2001 13:59

elsie reed
Messages: 7
Registered: January 2001

Junior Member

this was working in microsoft sql server but won't work with oracle. can someone pls help me.

SELECT DISTINCT ARBOR_COMMON_DATA_LAYOUT.ACCOUNT_CATEGORY
FROM ARBOR_COMMON_DATA_LAYOUT LEFT
JOIN ACCOUNT_CATEGORY ON ARBOR_COMMON_DATA_LAYOUT.ACCOUNT_CATEGORY
= ACCOUNT_CATEGORY.ACCOUNT_CATEGORY_ID
WHERE ACCOUNT_CATEGORY.ACCOUNT_CATEGORY_ID IS NULL.

Report message to a moderator

Re: Syntax help!! [message #372150 is a reply to message #372149]

Tue, 16 January 2001 14:12

Deanna
Messages: 9
Registered: December 2000

Junior Member

In Oracle you can assign an alias to a table. So in the example below I select one field from table 1 and two fields from table 2. Then in the where clause I joined them together (PK to FK)

SELECT a.field1, b.field2, b.field3
FROM table1 a, table2 b
WHERE a.pk = b.fk
and a.field1 IS NULL

Hope this helps

Report message to a moderator

Re: Syntax help!! [message #372151 is a reply to message #372149]

Tue, 16 January 2001 14:16

Andrew again...
Messages: 270
Registered: July 2000

Senior Member

Your left join is an Oracle Outer-Join.

Try something like:

SELECT DISTINCT ARBOR_COMMON_DATA_LAYOUT.ACCOUNT_CATEGORY
FROM ARBOR_COMMON_DATA_LAYOUT, ACCOUNT_CATEGORY
where ARBOR_COMMON_DATA_LAYOUT.ACCOUNT_CATEGORY (+) = ACCOUNT_CATEGORY.ACCOUNT_CATEGORY_ID
and ACCOUNT_CATEGORY.ACCOUNT_CATEGORY_ID IS NULL;

or maybe the other way around...

SELECT DISTINCT ARBOR_COMMON_DATA_LAYOUT.ACCOUNT_CATEGORY
FROM ARBOR_COMMON_DATA_LAYOUT, ACCOUNT_CATEGORY
where ARBOR_COMMON_DATA_LAYOUT.ACCOUNT_CATEGORY
= ACCOUNT_CATEGORY.ACCOUNT_CATEGORY_ID (+)
and ACCOUNT_CATEGORY.ACCOUNT_CATEGORY_ID IS NULL.

or using aliases to simplify...

SELECT DISTINCT x.ACCOUNT_CATEGORY
FROM ARBOR_COMMON_DATA_LAYOUT x, ACCOUNT_CATEGORY y
where x.ACCOUNT_CATEGORY = y.ACCOUNT_CATEGORY_ID (+)
and y.ACCOUNT_CATEGORY_ID IS NULL.

Report message to a moderator

Re: Syntax help!! [message #372152 is a reply to message #372150]

Tue, 16 January 2001 14:30

elsie reed
Messages: 7
Registered: January 2001

Junior Member

thanks it worked

Report message to a moderator

Previous Topic:	Optimization Required for this query
Next Topic:	user_dump_dest

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 22:58:07 CDT 2024