Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    14

    Unanswered: select... into query

    Can somebody help me with my below select... into query?

    I have created a temp table and want to select some values in to it but need to use a CASE statement. This is how I have done it:

    CREATE TABLE #tempAS_trans ( ticket char(8), fc money, code tinyint null, rate float null )

    select ts.ticket, ts.face, ts.ratecode, CASE
    WHEN ts.ratecode = 0 THEN ts.intrate
    WHEN ts.ratecode IN (1, 2) THEN null
    ELSE ts.intrate
    END
    into #tempAS_trans
    from tableA ts, tableB rr
    where rr.ticket = ts.ticket


    However, I'm getting and error saying that column 4 in table #tempAS has a null column name. Null column names are not allowed

    Can anybody see what my problem is?

    Thanks for your help...
    Wallace

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem is, SELECT/INTO attempts to create the table which is named in the INTO clause, and it's failing because you haven't assigned a column alias to the CASE expression

    even if you did assign a column name, the SELECT would probably fail anyway, since the table already exists



    i think what you really want is INSERT/SELECT, not SELECT/INTO
    Code:
    INSERT
      INTO #tempAS_trans
    SELECT ts.ticket
         , ts.face
         , ts.ratecode
         , CASE 
            WHEN ts.ratecode = 0 THEN ts.intrate
            WHEN ts.ratecode IN (1, 2) THEN null
            ELSE ts.intrate
            END
      FROM tableA ts
    INNER
      JOIN tableB rr
        ON rr.ticket = ts.ticket
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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