Error in a 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)Try wrapping you as values in double quotes so more like this...

SELECT P.StatusID as "Stat" FROM Product P WHERE 1=1 AND StatusID = 11 AND TradeDate >= 'Jul 19 2004'
order by "Stat"|||Hi,

Wil this solve my problem, I don't think so. Since the alias names also works without that double quotes.

Please explain bit more.

Thanks,
Sam|||Sorry my mistake I was skim reading the question and not really paying much attention...

I suspect you have a problem because you are creating a table alias that is the same as a column alias...|||Hi,

Even the same alias name for a table and column is allowed in SQL Server. Try it out..Its working fine. Here I have a doubt whether I am doing things wrongly while joining the columns with prefix...But I am not sure of it...|||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