1

I need a little help with the following SQL Query:

SELECT   AUF_EK.FIRM,   AUF_EK.customer,   AUF_EK.cnr,   k.name1,  
 k.name2,   k.name3,   k.street,   k.pin,   k.loc,   r.name1,  
 r.name2,   r.name3,   lo_gpname1,

.....

First the selected columns, I get that so far.

 FROM   AUFT,   PARTNER r,   PARTNER k, ....   skp_ARTIKEL_Z,

then we have the from clause, but why is there an Partner r and Partner k? Does it mean the table name is "Partner r" or is the a other reason I don't know yet?

 WHERE   (
     AUF.FIRM BETWEEN 123 AND 456   AND AUF.FIRM                   = k.FIRM   AND AUF.customer                   = k.gpnr   AND AUF.FIRM  
 = k.FIRM   AND AUF.customer                   = k.gpnr   AND AUF.art_nr = ART.art_nr

The where clause makes sense to me till here, so it basically matches columns from the selected tables...

 AND EK_POS.art_nr              =
 ARTIKEL_Z.art_nr(+)   AND NVL
  (POS.pos_nr_zuo, 0) = (NVL (
      _EK_POS_PREIS.pos_nr_zuo(+), 0))  )

But here the part I don't get. I'm not an SQL Pro.... What does the (+) is doing? And what is ,0 meaning?

StanislavL
  • 56,971
  • 9
  • 68
  • 98
SQL_Not_Freak_Yet
  • 9
  • 5
  • Does this query work? Is there any problem with it? You are asking basic questions that any book or SQL site can answer very easily. Sorry for being harsh, but you have to do more research yourself before asking a question here to get an answer. Example. `NVL` is a function that says if the first value is `NULL` then replace it with 0 `NVL(POS.pos_nr_zuo, 0)` – SS_DBA Nov 08 '17 at 15:04
  • 1
    Well it does not work. The columns from Partner r,k seems to be problematic. The second thing is I have no clue and I never saw something like where x.c=y.c(+). So a name/definition for the (+) and what it does would be a big help. – SQL_Not_Freak_Yet Nov 08 '17 at 15:09
  • Understood. When posting a question you should tell us what doesn't work and what you don't understand. SQL does vary among platforms, but the basic syntax should be common across all of them. Good luck! – SS_DBA Nov 08 '17 at 15:14

2 Answers2

1

Partner r and Partner k

You can refer this answer What's the best way to join on the same table twice?

What does the (+) is doing?

Refer the answer of OMG Ponies

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

SELECT ...
FROM a
LEFT JOIN b ON b.id = a.id

It should also be noted that even though the (+) works, Oracle recommends not using it

And what is ,0 meaning?

NVL(POS.pos_nr_zuo, 0) if value of POS.pos_nr_zuo is null, it return 0 as default value

Thieu
  • 44
  • 3
0

You asked several questions: about the PARTNER r:

In your query you want to reference the table PARTNER twice, so you give it an alias, first one is r,second is k.

It's hard to say what exactly it's used for because I can't see the whole query. But a simple example is : suppose you have a ADDRESSES table and an ORDERS table. ORDERS has two references to people : BILLING_ADDR_ID and SHIPPING_ADDR_ID. If you want to display both addresses in a query, you need two aliases.

About the (+) - It's an old syntax for LEFT OUTER JOIN. About the ',0 - It's part of the NVL(...,0) expression.

Yossi Vainshtein
  • 3,845
  • 4
  • 23
  • 39