CODEWITHSUNDEEP
sqloracle-database
SQL_Not_Freak_Yet
SQL_Not_Freak_Yet

Reputation: 9

SQL Query (Oracle SQL)

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?

Upvotes: 1

Views: 81

Answers (2)

Thieu
Thieu

Reputation: 44

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

Upvotes: 1

Yossi Vainshtein
Yossi Vainshtein

Reputation: 3985

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.

Upvotes: 0

Related Questions