none
Converting Rrom Access Syntax To Sql Syntax

    Question

  •  

    Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..

    Here is the part that I need to convert:

     

    SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

     IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

                 IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

                             IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

                                         IIf(Mid([proj_name],1,9)='9900-2787','Sales',

                                                     IIf(Mid([proj_name],1,9)='9910-2799','Sales',

                                                                 IIf(Mid([proj_name],1,9)='9920-2791','Sales',

                                                                                                                                                    

                                                                )

                                                    )

                                        )

                            )

                ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

       from................

     

    how can you convert it to sql syntax

     

    I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):

    select ID, FName, LName
    if(SUBSTRING(FirstName, 1, 4)= 'Mike')
    Begin
        Replace(FirstNam,'Mike','MikeTest')
           if(SUBSTRING(LastName, 1, 4)= 'Kong')
             Begin
                Replace(LastNam,'Kong,'KongTest')
                  if(SUBSTRING(Address, 1, 4)= '1245')
                 Begin
                     .........
        End
       End

    end

     

     

     

     Case Statement might be the solution but i could not do it.

     

     

     

     

     

     

    Your input will be appreciated

     

    Thank you

    Sunday, September 23, 2007 4:25 PM

Answers

All replies

  • you rightly said that CASE Statement is the right choice...

    check this....
    select ID, FName, LName,

     Case

    When SUBSTRING(FirstName, 1, 4)= 'Mike' Then  Replace(FirstName,'Mike','MikeTest')

    End  as FN,

    Case

    When SUBSTRING(LastName, 1, 4)= 'Kong' Then Replace(LastNam,'Kong,'KongTest')

    End as LN,

    Case
    When SUBSTRING(Address, 1, 4)= '1245') Then ...........

    End As Add

    From YourTableName Where <Conditions>

     

     

    Madhu

    Monday, September 24, 2007 5:00 AM
    Moderator
  • Thank you for you respond. This won't really work for one simple reason.

    It will execute each case statement. In other words if you have two people with the last name Kong both of their last names will change to KongTes. Even if you change it to which it will work

     

    Case

    When SUBSTRING(FirstName, 1, 4)= 'Mike' Then  Replace(FirstName,'Mike','MikeTest')

    End  as FN,

    Case

    When SUBSTRING(LastName, 1, 4)= 'Kong' and When SUBSTRING(FirstName, 1, 4)= 'Mike'

    Then Replace(LastNam,'Kong,'KongTest')

    End as LN,

    Case
    When SUBSTRING(Address, 1, 4)= '1245') Then ...........

    End As Add

    From YourTableName Where <Conditions>

     

     

     

    But  it is not equal to this

     

    SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

     IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

                 IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

                             IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

                                         IIf(Mid([proj_name],1,9)='9900-2787','Sales',

                                                     IIf(Mid([proj_name],1,9)='9910-2799','Sales',

                                                                 IIf(Mid([proj_name],1,9)='9920-2791','Sales',

                                                                                                                                                    

                                                                )

                                                    )

                                        )

                            )

                ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

     

     

    Here is what i am trying to do:

    select case

    when SUBSTRING(task_name, 1, 3)= 'PTO'

     then   Replace(task_name,'PTO','PTO_Holiday') and Sum(td_hours) AS SumOfHours  (this will give me an error)

    when SUBSTRING(task_name, 1, 7)= 'Holiday' and SUBSTRING(task_name, 1, 3)= 'PTO'

    then Replace(task_name,'Holiday','PTO_Holiday')and Sum(td_hours) AS SumOfHours  (this will give me an error)

    ELSE task_name

     

    This does not work i can't have two things happening after "Then"

    Do you think i can convertt this to sql  Syntax?

    thanks

     

    Monday, September 24, 2007 1:16 PM
  • RGoal,

    Do you still need help with this?

    Thank you!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, January 25, 2014 7:49 AM
    Owner
  • Great :-) Find me more question like this

    My latest WIKI article is Just about this :-)

    CONVERTING from ACCESS to SQL Server
    Or in other words
    Converting VBA to TRANSACT-SQL

    This is the time for any translation anyone need from VBA (ACCESS) to TRANSACT-SQL (SQL Server)! Ask and you shall receive :-)

    * I added now to the article, implementation of VBA IIF function, In SQL Server TRANSACT-SQL. so the answer is here now:

    http://social.technet.microsoft.com/wiki/contents/articles/22731.convert-vba-code-to-transact-sql.aspx


    [Personal Site] [Blog] [Facebook]signature

    Saturday, January 25, 2014 9:34 AM
    Moderator