Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: problem with query where column name is the same as a keyword

    hi


    I am having trouble with the following query within my store procedure.
    as you can see, i am making an union of 2 separate queries.

    in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"

    is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)


    CREATE PROCEDURE topcat.getTransHistory
    (
    @contact_id numeric(9)
    )
    AS
    BEGIN
    DECLARE @phone_no varchar(255)

    set @phone_no = (select top 1 phone_num from topcat.class_contact where _id = @contact_id)

    select cast(trans_new.trans_date as varchar(50)) date,
    '' code,
    cast(payment.date_paid as varchar(50)) datepaid,
    '' "desc",
    case payment.payment_type
    when 'cheque' then trans_new.item_total
    else ''
    end pledged,
    '' mail,
    case payment.payment_type
    when 'cheque' then ''
    else trans_new.item_total
    end received,
    '' receipt
    from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
    where trans_new.contact_id = @contact_id
    union
    select cast(trans_old.date as varchar(50)) "date",
    trans_old.code,
    cast(trans_old.datepaid as varchar(50)) "datepaid",
    trans_old.desc,
    cast(trans_old.pledged as varchar(128)),
    trans_old.mail,
    cast(trans_old.received as varchar(128)),
    trans_old.receipt
    from topcat.MMTRANS$ trans_old
    where phone = @phone_no

    END
    GO


    Cheers
    James

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    wrap your column name that is a keyword in square brackets eg [desc]

  3. #3
    Join Date
    Aug 2003
    Posts
    111
    thank you
    thank you
    thank you

    you are a life saver, i was goin to change the column name of the table if i couldn't find an alternative way of doing it.



  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    no worries,... try to avoid keywords in the future would be my advise though...

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i was on a contract once and there converted database had tables and columns all over the place named with keywords.

    i will never forget they had a column called select in a table called order.
    nightmare to say the least.

Posting Permissions

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