Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    37

    Question Unanswered: Case Statement Error in an Insert Statement

    Hi All,
    I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
    My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

    Here is my code:
    Insert into myTblA
    (TblA_ID,
    mycasefield =
    case
    when mycasefield = 1 then 99861
    when mycasefield = 2 then 99862
    when mycasefield = 3 then 99863
    when mycasefield = 4 then 99864
    when mycasefield = 5 then 99865
    when mycasefield = 6 then 99866
    when mycasefield = 7 then 99867
    when mycasefield = 8 then 99868
    when mycasefield = 9 then 99855
    when mycasefield = 10 then 99839
    end,
    alt_min,
    alt_max,
    longitude,
    latitude
    (
    Select MTB.LocationID
    MTB.model_ID
    MTB.elevation, --alt min
    null, --alt max
    MTB.longitude, --longitude
    MTB.latitude --latitude
    from MyTblB MTB
    );

    The error I'm getting is:
    Incorrect syntax near '='.

    I have tried various versions of the case statement based on examples I have found but nothing works.
    I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Blimey - lots of errors

    1) 2 opening parentheses, 1 closing
    2) Fields in the select not separated by commas
    3) The area where you have your case statement is where you specify the destination fields. The bit after the select clause is where yuou define your data so....
    4) 7 destination fields, 6 source fields

    I advise you read INSERT in BoL. Construct your SQL without the case and only include it once you have it working.

    HTH

    EDIT - 4 is wrong - can't count
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is some pretty messed up syntax. I strongly encourage you to (re-)read the BOL sections on INSERT statements and the CASE function.

    This is the basic syntax of an INSERT statement. You can't perform logic in the column list; only in the SELECT clause.

    I left out the CASE statement because it is unclear how you want it to work. Do you want it to reference model_ID?
    Code:
    Insert into myTblA
    	(TblA_ID,
    	alt_min,
    	alt_max,
    	longitude,
    	latitude)
    Select	MTB.LocationID,
    	MTB.elevation, --alt min
    	null, --alt max
    	MTB.longitude, --longitude
    	MTB.latitude --latitude
    from	MyTblB MTB
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2003
    Posts
    37

    Wink Case Statement Error

    Thanks for the input. Sorry about the "incompleteness" of the sql, I was trying to edit it down, in reality it is larger than what I posted.
    In ref to the parentheses, they're all there in the original, I just missed putting it in the sample.
    The fields in the select are separated by commas, except for the case portion and for information purposes I put commas after each Case statement but it still errors.
    I do have a matching number of Select fields for my Insert, again, it's an error on my part when trying to just give the pertinent issues.
    Lastly, I did run the sql statement w/o the case statement and it works (yeah, I know, it probably shouldn't but hey, I'm not going to complain).

    I'll try to clean up the query and repost it to see if that helps in identifying why the case won't work.
    Thanks for the input.

  5. #5
    Join Date
    Oct 2003
    Posts
    37
    Here is the sql again, this time I tried to ensure all the basic stuff is correct (i.e. matching selected to inserted, commas, etc).
    Insert into operation
    (LocationID,
    instance_id =
    case
    when instance_id = 1 then 99861
    when instance_id = 2 then 99862
    when instance_id = 3 then 99863
    when instance_id = 4 then 99864
    when instance_id = 5 then 99865
    when instance_id = 6 then 99866
    when instance_id = 7 then 99867
    when instance_id = 8 then 99868
    when instance_id = 9 then 99855
    when instance_id = 10 then 99839
    end,
    altitude_minimum,
    altitude_maximum,
    longitude,
    latitude)
    (
    Select l.Locationid,
    (SELECT Equipment.ModelID
    FROM Assignment INNER JOIN Equipment ON Assignment.EquipmentID = Equipment.EquipmentID
    INNER JOIN EquipmentModel ON Equipment.ModelID = EquipmentModel.ModelID
    INNER JOIN Location ON Assignment.LocationID = Location.LocationID
    INNER JOIN Product ON Assignment.AssignmentID = ProductDataFile.AssignmentID),
    l.elevation, --alt min
    null, --alt max
    l.longitude, --longitude
    l.latitude --latitude
    from Location l
    );

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Point 3 still stands
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2003
    Posts
    37

    Question Case Statement in an Insert Statement

    Hey PootleFlump, I don't think I follow your 3rd point but would like to clarify.
    I'm well aware that the case statement is in the destination fields, I don't want to insert the value coming from the Selected Records, I need to change it (hence the case statement). Are you saying to move the case statement down into the Select?

    Thanks for any assistance.....

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can't have a CASE statement in an INSERT column list...it needs to be in the SELECT

    And since it doesn't make any sense, I don't know how to help
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does INSTANCE ID = MODEL ID?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2003
    Posts
    37

    Smile

    Hey Brett,
    Yes it does. Thanks for the input about the Case Statement not being permitted in an Insert. I'll look for another avenue for inserting the records from one table to another.

    Thanks!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a flat out, shot in the dark, but it could be what you need:
    Code:
    INSERT INTO operation (
       LocationID, instance_id, altitude_minimum
    ,  altitude_maximum, longitude, latitude)
       Select l.Locationid,
    ,  (SELECT
          CASE Equipment.ModelID
             WHEN  1 THEN 99861
             WHEN  2 then 99862
             WHEN  3 then 99863
             WHEN  4 then 99864
             WHEN  5 then 99865
             WHEN  6 then 99866
             WHEN  7 then 99867
             WHEN  8 then 99868
             WHEN  9 then 99855
             WHEN 10 then 99839
          END
          FROM Assignment
          INNER JOIN Equipment
             ON Assignment.EquipmentID = Equipment.EquipmentID
          INNER JOIN EquipmentModel
             ON Equipment.ModelID = EquipmentModel.ModelID 
          INNER JOIN Location
             ON Assignment.LocationID = Location.LocationID 
          INNER JOIN Product
             ON Assignment.AssignmentID = ProductDataFile.AssignmentID)
    ,  l.elevation		--alt min
    ,  null			--alt max
    ,  l.longitude		--longitude
    ,  l.latitude		--latitude 
       FROM Location AS l 
    );
    -PatP

Posting Permissions

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