CODEWITHSUNDEEP
sqloracle-database
user3805423
user3805423

Reputation: 23

ERROR at line 3: ORA-00933: SQL command not properly ended

I am running the below SELECT statement to:

  1. Return prices from a standard price list customer = 0
  2. If the customer has been quoted a special price customer = X then use that price instead

I am getting the error message:

ERROR at line 3:
ORA-00933: SQL command not properly ended

Oracle version is: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production.

SELECT     glas_daten_basis.idnr, glas_daten_basis.gl_bez, NVL(p2.zum2, p1.zum2)
FROM       glas_daten_basis
JOIN       os_przu p1 ON p1.idnr = glas_daten_basis.idnr
LEFT JOIN  os_przu p2 ON p2.idnr = glas_daten_basis.idnr AND p2.kunr = 63
WHERE      p1.kunr = 0;

Line 3 is the JOIN, is there something wrong here?

Update: There are 137 rows in the standard price list, so I should be given 137 rows regardless of whether the price is from customer = 0 or customer = X. The answers so far give me a ~60 rows for some reason.

SELECT     os_przu.idnr, os_przu.zum2
FROM       os_przu
WHERE      os_przu.kunr = 0;
...
137 rows selected.

Upvotes: 0

Views: 1566

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

As @a_horse-with_no_name said, ANSI joins don't work in 8i; they weren't added until 9i. So if you're really stuck on this ancient and unsupported version you're stuck with the old Oracle-specific syntax:

SELECT     glas_daten_basis.idnr, glas_daten_basis.gl_bez, NVL(p2.zum2, p1.zum2)
FROM       glas_daten_basis, os_przu p1, os_przu p2 
WHERE      p1.idnr = glas_daten_basis.idnr
AND        p1.kunr = 0
AND        p2.idnr (+) = glas_daten_basis.idnr
AND        p2.kunr (+) = 63;

Which is pretty similar to @nelucon's answer, except that only had one (+) left-join marker and it was on the wrong side of the condition.

SQL Fiddle.

The (+) is the Oracle-specific outer-join operator, and it has to be applied to each condition for the outer-joined table - if one is missed then the rest are ignored and it effectively becomes an inner join again. (One of the reasons ANSI joins are easier to work with, though you can still get that wrong by referring to the joined table in the where clause as well as the on.)

Upvotes: 4

nelucon
nelucon

Reputation: 326

something like this, i didn't test the code but you got it. (+) means the left join. for more infos, google for "oracle 8 left join"

SELECT     glas_daten_basis.idnr, glas_daten_basis.gl_bez, NVL(p2.zum2, p1.zum2)
FROM       glas_daten_basis, os_przu p1, os_przu p2 
WHERE      p1.kunr = 0
AND        p1.idnr = glas_daten_basis.idnr
AND        p2.idnr = glas_daten_basis.idnr (+)
AND        p2.kunr = 6;

Upvotes: 0

Related Questions