Converting a query with *= to ansi conform join operators

Question

Post reply

Converting a query with *= to ansi conform join operators

BrekerS

Grasshopper

Points: 19
More actions
November 8, 2007 at 12:40 am

#180127

i have some problems to converting the following sql statement to ansi conform join operators.
select ma_idx, ma_kurztext_deu, kd.kd_preis, kl.kd_preis
from debitor, material, kondition kd, kondition kl
where db_idx = 9009000 and db_ko_vkpreis = kd.kd_pl_idx and
db_vkorg = kd.kd_vkorg and kd.kd_name = 'PR00' and
ma_idx = kd.kd_ma_idx and GetDate() between
kd.kd_gueltig_von and kd.kd_gueltig_bis and
db_ko_ldpreis *= kl.kd_pl_idx and
db_vkorg *= kl.kd_vkorg and ma_idx *= kl.kd_ma_idx and
kl.kd_name = 'ZPL1' and GetDate() between
kl.kd_gueltig_von and kl.kd_gueltig_bis

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

Do you know what *= means?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BrekerS Grasshopper Points: 19 More actions · Answer 2

Yes, i know. In a compatibility level SQL 2000 the statement works correctly. I need this statement for compatibility level SQL 2005.

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

Before we can suggest anything at all, you need to prefix ALL columns with proper table name or table alias.

N 56°04'39.16"
E 12°55'05.25"

BrekerS Grasshopper Points: 19 More actions · Answer 4

Here is the statement with full qualified name:

select ma.ma_idx, ma.ma_kurztext_deu, kd.kd_preis, kl.kd_preis

from debitor db, material ma, kondition kd, kondition kl

where db.db_idx = 9009000 and db.db_ko_vkpreis = kd.kd_pl_idx and

db.db_vkorg = kd.kd_vkorg and kd.kd_name = 'PR00' and

ma.ma_idx = kd.kd_ma_idx and GetDate() between

kd.kd_gueltig_von and kd.kd_gueltig_bis and

db_ko_ldpreis *= kl.kd_pl_idx and

db.db_vkorg *= kl.kd_vkorg and ma.ma_idx *= kl.kd_ma_idx and

kl.kd_name = 'ZPL1' and GetDate() between

kl.kd_gueltig_von and kl.kd_gueltig_bis

Thanks

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 5

Try thisselectma.ma_idx,

ma.ma_kurztext_deu,

kd.kd_preis,

kl.kd_preis

fromdebitor as db

inner joinkondition as kd on kd.kd_pl_idx = db.db_ko_vkpreis

and kd.kd_vkorg = db.db_vkorg

inner joinmaterial as ma on ma.ma_idx = kd.kd_ma_idx

left joinkondition as kl on kl.kd_pl_idx = {table alias here}.db_ko_ldpreis

and kl.kd_vkorg = db.db_vkorg

and kl.kd_ma_idx = ma.ma_idx

and kl.kd_name = 'ZPL1'

and GetDate() between kl.kd_gueltig_von and kl.kd_gueltig_bis

wheredb.db_idx = 9009000

and kd.kd_name = 'PR00'

and GetDate() between kd.kd_gueltig_von and kd.kd_gueltig_bis

N 56°04'39.16"
E 12°55'05.25"

Ken McKelvey SSCoach Points: 18942 More actions · Answer 6

As the kl filters are after the join in the where clause, I suspect the following:

SELECT ma_idx, ma_kurztext_deu, kd.kd_preis, kl.kd_preis

FROM debitor D

&nbsp&nbsp&nbsp&nbspJOIN kondition kd

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON D.db_ko_vkpreis = kd.kd_pl_idx

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND D.db_vkorg = kd.kd_vkorg

&nbsp&nbsp&nbsp&nbspJOIN material M

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON kd.kd_ma_idx = M.ma_idx

&nbsp&nbsp&nbsp&nbspJOIN kondition kl

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON D.db_ko_ldpreis = kl.kd_pl_idx

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND D.db_vkorg = kl.kd_vkorg

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND M.ma_idx = kl.kd_ma_idx

WHERE D.db_idx = 9009000

&nbsp&nbsp&nbsp&nbspAND kd.kd_name = 'PR00'

&nbsp&nbsp&nbsp&nbspAND GETDATE() BETWEEN kd.kd_gueltig_von AND kd.kd_gueltig_bis

&nbsp&nbsp&nbsp&nbspAND kl.kd_name = 'ZPL1'

AND GETDATE() BETWEEN kl.kd_gueltig_von AND kl.kd_gueltig_bis

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 7

BrekerS (11/8/2007)
i have some problems to converting the following sql statement to ansi conform join operators.
select ma_idx, ma_kurztext_deu, kd.kd_preis, kl.kd_preis
from debitor, material, kondition kd, kondition kl
where db_idx = 9009000 and db_ko_vkpreis = kd.kd_pl_idx and
db_vkorg = kd.kd_vkorg and kd.kd_name = 'PR00' and
ma_idx = kd.kd_ma_idx and GetDate() between
kd.kd_gueltig_von and kd.kd_gueltig_bis and
db_ko_ldpreis *= kl.kd_pl_idx and
db_vkorg *= kl.kd_vkorg and ma_idx *= kl.kd_ma_idx and
kl.kd_name = 'ZPL1' and GetDate() between
kl.kd_gueltig_von and kl.kd_gueltig_bis

The easiest way to do that is simply create a new view, but don't add any tables. Paste your code into the sql window and then execute your query. SQL Server will automatically convert your code to the ANSI version. This works in both 2000 and 2005.