convert ORACLE syntax to SQL syntax?

[LMCRYER]

I have a query coming off of ORACLE which works fine. For multiple reasons, I need to take this and modify so that it comes off of SQL (as a stored procedure).

I am having difficulty with the JOINS - cant seem to get them to behave the right way.

The ORACLE version =

SELECT "PACT"."EMPLOYEE_ID", "PACT"."ORDER_NBR", "PACT"."DEPT_ID", "PACT"."ACTIVITY_DATE", "PACT"."ACT_RSRC_USED", "PACT"."CONTROL_ID", "BIDCAT_CC"."CC"
FROM "MRPOWNER"."BIDCAT_CC" "BIDCAT_CC", "CSIOWNER"."SOPN_ELK" "SOPN", "CSIOWNER"."PACT_ELK" "PACT"
WHERE ("BIDCAT_CC"."BIDCAT"="SOPN"."PO_OPERATION_CLASS_CODE") AND (((("SOPN"."ALTERNATE_SEQ_NBR"="PACT"."ALT_SEQ_NBR" (+)) AND ("SOPN"."MAJOR_SEQ_NBR"="PACT"."MAJ_SEQ_NBR" (+))) AND ("SOPN"."MINOR_SEQ_NBR"="PACT"."MIN_SEQ_NBR" (+))) AND ("SOPN"."ORD_NBR"="PACT"."ORDER_NBR" (+))) AND "PACT"."DEPT_ID"='20' AND ("PACT"."ACTIVITY_DATE">={ts '2004-07-08 00:00:00'} AND "PACT"."ACTIVITY_DATE"<{ts '2004-07-09 00:00:00'})
ORDER BY "BIDCAT_CC"."CC", "PACT"."EMPLOYEE_ID"


Now, what I'm trying to do is make these JOINS come in the right way in SQL (TAB inner join SOPN, SOPN left join PACT (on 4 fields), PACT left join EMP)

Any help?

 

[LokiDba]

Hi,

Try this

SELECT "PACT"."EMPLOYEE_ID",
"PACT"."ORDER_NBR",
"PACT"."DEPT_ID",
"PACT"."ACTIVITY_DATE",
"PACT"."ACT_RSRC_USED",
"PACT"."CONTROL_ID",
"BIDCAT_CC"."CC"
FROM "MRPOWNER"."BIDCAT_CC" "BIDCAT_CC",
"CSIOWNER"."SOPN_ELK" "SOPN",
"CSIOWNER"."PACT_ELK" "PACT"
WHERE ("BIDCAT_CC"."BIDCAT"="SOPN"."PO_OPERATION_CLASS_CODE") AND
(((("SOPN"."ALTERNATE_SEQ_NBR" *= "PACT"."ALT_SEQ_NBR" ) AND
("SOPN"."MAJOR_SEQ_NBR" *= "PACT"."MAJ_SEQ_NBR" )) AND
("SOPN"."MINOR_SEQ_NBR" *= "PACT"."MIN_SEQ_NBR" )) AND
("SOPN"."ORD_NBR" *= "PACT"."ORDER_NBR" )) AND
"PACT"."DEPT_ID"='20' AND ("PACT"."ACTIVITY_DATE">={ts '2004-07-08 00:00:00'} AND
"PACT"."ACTIVITY_DATE"<{ts '2004-07-09 00:00:00'})
ORDER BY "BIDCAT_CC"."CC", "PACT"."EMPLOYEE_ID"

*= is an older version of Outer Join

Andy (LokiDBA)

 

[LokiDba]

You'll need to Converts on the date strings as well if the columns are of datetime types in SQL Server.

Andy

 

[LMCRYER]

It's telling me :

Query contains an outer-join request that is not permitted.

Am I not allowed to do these joins in SQL?

LMC

 

[SantaMufasa]

Lisa,

Please forgive my dimness on your thread. When you say, "I am having difficulty with the JOINS - cant seem to get them to behave the right way." Could you confirm what behaviour the joins are exhibiting? (Frankly, I am not familiar with the syntax that uses the curly braces "{ }".) In any case, could you please post precisely what your code does (perhaps with a copy-and-paste of your existing results) from Oracle and how you want that behavior to change?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:14 (08Jul04) UTC (aka "GMT" and "Zulu"), 09:14 (08Jul04) Mountain Time)

 

[LokiDba]

LMCRYER,

I'm assuming you are converting to SQL Server. *= is a lot like (+) in that the star needs to be on the side where the nulls will occur. ie it can either be *= or =*. You get the outer join request not permitted where you have more than one outer join and but they are not linier (can't spell). Not describing this to well have a look in Books on Line, Search for Outer Join and selected the WHERE Clause topic in the Transact-SQL Ref. It gives a better description.

Hope of some use
Andy

Heres the start of it:

<B>WHERE Clause</B>
Specifies a search condition to restrict the rows returned.

Syntax
[ WHERE < search_condition > | < old_outer_join > ]

< old_outer_join > ::=
column_name { * = | = * } column_name

Arguments
< search_condition >

Restricts the rows returned in the result set through the use of predicates. There is no limit to the number of predicates that can be included in a search condition. For more information about search conditions and predicates, see Search Condition.

< old_outer_join >

Specifies an outer join using the nonstandard product-specific syntax and the WHERE clause. The *= operator is used to specify a left outer join and the =* operator is used to specify a right outer join.

This example specifies a left outer join in which the rows from Tab1, that do not meet the specified condition, are included in the result set:

SELECT Tab1.name, Tab2.id
FROM Tab1, Tab2
WHERE Tab1.id *=Tab2.id



Note Using this syntax for outer joins is discouraged because of the potential for ambiguous interpretation and because it is nonstandard. Instead, specify joins in the FROM clause.


It is possible to specify outer joins by using join operators in the FROM clause or by using the non-standard *= and =* operators in the WHERE clause. The two methods cannot both be used in the same statement