Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Unanswered: Help with syntax errors.

    I have been tasked with converting complex queries from Access to views within SQL Server.

    This statement is killing me:
    Code:
    SELECT BinID, [Definition] + 
    IF [Definition] = [Description]
    BEGIN
    ' '
    END ELSE ': '
     + [Description] AS FullDescription, Definition, Description, CurrentQuantity, MinPrice, ListPrice, AcquisitionCost, MinQuantity, MaxQuantity, NominalQuantity, IsActive, 
    IF [CurrentQuantity] > 0
    BEGIN
    [AcquisitionCost]/[CurrentQuantity] AS AvgCost
    END ELSE
    0 AS AvgCost
    END
    FROM  dbo.tblBinInventory
    When I try to verify this I am receiving these errors:
    • Incorrect syntax near the keyword 'IF'
    • Incorrect syntax near ' '
    • Incorrect syntax near 'AcquisitionCost'


    I have been working on this one for a couple of hours now. Can someone please help me a bit with this one?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT BinID, [Definition] + CASE 
          WHEN [Definition] = [Description] THEN ' '
          ELSE ': ' + [Description] 
       END AS FullDescription
    ,  Definition, Description, CurrentQuantity
    ,  MinPrice, ListPrice, AcquisitionCost
    ,  MinQuantity, MaxQuantity, NominalQuantity
    ,  IsActive, CASE
          WHEN [CurrentQuantity] > 0 THEN [AcquisitionCost] / [CurrentQuantity]
          ELSE 0 
       END AS AvgCost
    FROM  dbo.tblBinInventory
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2010
    Posts
    8
    Wow! I came in this morning and simply copied and pasted the statement from you and it works perfectly! THANK YOU THANK YOU THANK YOU!!

    I would like to ask another question about this if you don't mind, though. I thought I could use IF statements with SQL and I am really not sure why that didn't work.

    I am sorry for my ignorance. I swear I am trying to learn!

    EDIT---------------------------------------------------------------------------------------------------------------------------------

    I just realized that the reference I was using was for Jet SQL, which is apparently a bit different from T-SQL. I am very sorry the unecessary question about IF statements. Thanks again for your help.
    Last edited by Magian; 11-19-10 at 10:55.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    IF statements (and WHILE statements) do exist in T-SQL, but are exclusively used for flow control, instead of in queries.

  5. #5
    Join Date
    Jul 2010
    Posts
    8
    Hmm... thank you. I need to check on that.

    I am using the Transact SQL Reference on MSDN now so hopefully I will get this figured out soon.

    Thank you both!

Posting Permissions

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