Problem converting table self reference from TransactSQL to ANSI SQL
- From: "lennyw@xxxxxxxxx" <lennyw@xxxxxxxxx>
- Date: 21 Aug 2006 11:51:22 -0700
Hi
I have a Transact SQL query that I'm trying to re-cast as an equivalent
ANSI SQL query and so far I have not been able to formulate it so it
has no syntax errors. The reason I wish to translate it is I need to
(once it's translated succesfully) add new outer joins to it which
cannot be formulated using Tranact SQL.
The Transact SQL query works fine. It joins a table to itself twice and
also specifies partcular values for specific correlation names as part
of the join.
There are 431 entries in the table where the column "NM" has a value
'STGY', 431 for a NM column value of 'SUB-STGY' and 431 for a NM column
value of 'DL'. As you see, I get a result row if 4 columns from t3
match t1 and 4 columns from t3 match t2 and the NM column has the right
value matches.
Here it is ( I have used generic table and column names to protect the
privacy of my clients database).
select t1.AVALUE as X1, t2.AVALUE as X2, t3.AVALUE as X3, t3.TKR,
t3.ST, t3.BYS, t3.CC
from USER_TABLE t1, USER_TABLE t2, USER_TABLE t3
where t1.NM = 'STGY' and t2.NM = 'SUB_STG' and t3.NM = 'DL'
and t3.TKR =t1.TKR and t3.ST = t1.ST and t3.BYS = t1.BYS and t3.CC=
t1.CC
and t3.TKR =t2.TKR and t2.ST = t1.ST and t2.BYS = t1.BYS and t3.CC =
t2.CC
Here is my best (but as yet not parsable) guess of an ANSI equivalent:
select
t1.AVALUE as X1, t2.AVALUE as X2, t4.AVALUE as X3, t4.TKR,
t4.ST t4.BYS, t4.CC
from
USER_TABLE as t3 inner join USER_TABLE as t1
on t3.TKR =t1.TKR and t3.ST = t1.ST and t3.BYS = t1.BYS and
t3.CC=t1.CC and
t1.NM = 'STG' and t3l.NM = 'DL'
join
USER_TABLE as t4 inner join USER_TABLE as t2
on t4.TKR =t2.TKR and t4.ST = t2.ST and t4.BYS = t2.BYS and t4.CC =
t2.CC and
t2.NM = 'SUB_STG' and t4.NM = 'DL'
One big difference is that I need to use 4 correlation names here
instead of the 3 in the Transact SQL because of correlation name
scoping limits imposed by the ANSI syntax.
Sybase.From this formulation I get an "Incorrect syntax near '=' " error from
Commenting out the 2 lines where I do a comparison to constant strings
('STG'/'DL') and 'SUB-STG' / 'DL') results in an error message
"Incorrect syntax near rhe word 'CC' " error from Sybase. (that would
be the "t4.CC = t2.CC" predicate).
I've also tried various combinations of parentheses (mostly in the
second join) but these invariably cause a "Incorrect syntax near ')'
error, even when the paretheses are carefully matched.
My 2 questions are:
1. How do I formulate an ANSI SQL a 3 way self join ( join a table to
itself twice) also using multiple column matches in the join?
-OR phrased another way-
2. How do I correct the ANSI SQL shown above so it achieves the goal of
a 3 way self join without getting syntax errors from Sybase?
Thanks in advance for any advice!
Lenny Wintfeld
ps - (I've got this also posted at sybase.public.sqlanywhere.general.
Please excuse the duplication)
.
- Prev by Date: Re: Sybase ASE Linux for AMD Opteron 64 bit
- Next by Date: Re: Sybase Database
- Previous by thread: Sybase ASE Linux for AMD Opteron 64 bit
- Next by thread: ASA9 and multiple databases: best practise?
- Index(es):
Relevant Pages
|
|
