Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: 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)

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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"

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  5. #5
    Join Date
    Mar 2004
    Posts
    205
    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...

  6. #6
    Join Date
    Mar 2004
    Posts
    205
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •