Error in a SQL Server query ?
-
Hi,
When I compiled the below I get the following error :
The column prefix 'cat' does not match with a table name or alias name used in the query"
What might be the error cause ?
Please adivce.
Thanks,
Sam
CREATE VIEW colview
AS
SELECT
wi.wiciu,
wi.ttldfg,
wi.lngcdedfg,
lng.desccdedfgd AS LNGCDETXTSDGF,
wi.durhhdfg,
wi.durmmdfg,
wi.durssdfg,
CAST(DATEPART(YYYY, cprdtsdf) AS VARCHAR) + CAST(DATEPART(MM, cprdtsdf) AS VARCHAR) + CAST(DATEPART(DD, cprdtsdf) AS VARCHAR) AS CPRDTFXGV,
wi.cprnrsdf,
wi.recdindsdf,
wi.postdtsdf,
CAST(DATEPART(YYYY, wi.cretssdf) AS VARCHAR) + CAST(DATEPART(MM, wi.cretssdf) AS VARCHAR) + CAST(DATEPART(DD, wi.cretssdf) AS VARCHAR) AS CRETSFG,
wi.creuidg,
wi.lstupddtfdg,
wi.upddfguid,
'DOMDG' AS CAT,
cat.dessdfccde AS CATTXTSDF,
cat.lngsdf,
wi.discatdsf,
mdc.desccde AS DISCATTXTSDF,
wi.txtmusrelsdf,
tmr.desccdesdf AS TXTMUSRELTXT,
wi.musarrdsf,
mac.dessdfccde AS MUSARRTXT,
wi.lyrsdfcarr,
lac.dedsfsccde AS LYRCARRTXT,
wi.vrsdftyp,
vrt.dessdfccde AS VRTYPTXT,
wi.exrsdfptyp,
exrp.desdfsccde AS EXRPTYPTXT,
wi.cpssdfttyp,
cps.desdfsccde AS CPSTTYPTXT
FROM cds_lkup cat,
cds_lkup mdc RIGHT OUTER JOIN tbltitle wi ON mdc.txtcde = LTRIM(RTRIM(wi.discat))
AND mdc.lkupid = 'MUSDISCAT'
AND mdc.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup tmr ON tmr.txtcde = LTRIM(RTRIM(wi.txtmusrel))
AND tmr.lkupid = 'TXTMUSREL'
AND tmr.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup mac ON mac.txtcde = LTRIM(RTRIM(wi.musarr))
AND mac.lkupid = 'MUSARRCDE'
AND mac.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup lac ON lac.txtcde = LTRIM(RTRIM(wi.lyrcarr))
AND lac.lkupid = 'LYRADPCDE'
AND lac.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup vrt ON vrt.txtcde = LTRIM(RTRIM(wi.vrtyp))
AND vrt.lkupid = 'VRTYP'
AND vrt.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup exrp ON exrp.txtcde = LTRIM(RTRIM(wi.exrptyp))
AND exrp.lkupid = 'EXCPTYP'
AND exrp.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup cps ON cps.txtcde = LTRIM(RTRIM(wi.cpsttyp))
AND cps.lkupid = 'CPSTTYP'
AND cps.dmncde = 'WRK' RIGHT OUTER JOIN cds_lkup lng ON lng.txtcde = wi.lngcde
AND lng.lkupid = 'LNG'
AND lng.dmncde = 'WRK'
AND cat.txtcde = 'DOM'
AND cat.lkupid = 'CAT'
AND cat.dmncde = 'WRK'
WHERE (cat.lng = mdc.lng
OR mdc.lng IS NULL)
AND (cat.lng = tmr.lng
OR tmr.lng IS NULL)
AND (cat.lng = mac.lng
OR mac.lng IS NULL)
AND (cat.lng = lac.lng
OR lac.lng IS NULL)
AND (cat.lng = vrt.lng
OR vrt.lng IS NULL)
AND (cat.lng = exrp.lng
OR exrp.lng IS NULL)
AND (cat.lng = cps.lng
OR cps.lng IS NULL)
AND (cat.lng = lng.lng
OR lng.lng IS NULL)
-
I doubt whether this is the error :
I have defined 'CAT' as an alias to a table which is not involved in JOINED tables ( as you can see from the FROM clause of the query), but I have used this prefix in 'ON' clause in the query. Hence the error. But if I use the other prefix for the same table which involves in JOIN tables defined, then this error does not comes.
Eg :-
select sno from fr1 cat, fr1 mdc left join fr2 wi on cat.sno = wi.frsno
The above will cause error when prefix 'cat' is used in 'ON' clause, since it is not defined in JOINed tables.
But if I use like the below one, then there is no error :
select sno from fr1 cat, fr1 mdc left join fr2 wi on mdc.sno = wi.frsno
Am I right on the above said.
Please advice.
Thanks,
Sam
-
'DOMDG' AS CAT,
cat.dessdfccde AS CATTXTSDF,
cat.lngsdf,
ofcourse this is going to throw Error, as
what exactly r u doing, u r creating a runtime column and assigning it a name as cat ?
then how come r u using cat. ??
or something likethat
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
That is exactly the problem. The 'ON' clause has no knowledge of the aliasing done on other tables.
I did have a lengthy rewrite in progress here, based on the general idea that to look up value in a lookup table like cds_lkup you are better off using subqueries, rather than outer joining to n instances of it. But I got kinda bogged down in the intricacies of your db design. I would like to offer help, but I would need a db schema and some sample data - preferably without column names being arbitrarily obfuscated: that really doesn't help us help you.
A few quick hints: you probably shouldn't have 8 different lookup tables in one SQL table; or if you must, at least define views that expose each individual lookup. Is cds_lkup.dmncode always 'WRK'? Another candidate for a view. What is cds_lkup.lng? What does it join to in tbltitle? At the moment it looks like the CAT/DOM/WRK lookup has no relation to tbltitle, is this what you mean?
-
Oh my lord,
There is no need to say much on this error , While parsing a query the first parser will go for a replace for the alias names with original name of db.doo.do format ,
gota
regards
john
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply