×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Convert Access SQL to SQL Server

Convert Access SQL to SQL Server

Convert Access SQL to SQL Server

(OP)
I have been trying to convert my old access sql to sql server but after several hours have given up for now. Is there a tool to do this or failing that can someone convert this to SQL Server sql

CODE

SELECT DISTINCT vouchers.ID as vid, vouchers.*, merchants.merchants_logo, merchants.merchants_id, merchants.merchants_name, merchants.merchants_display_name, voucher_type.*
FROM ((((merchantscategory 
INNER JOIN merchants ON merchantscategory.merchants_id = merchants.merchants_id) 
INNER JOIN vouchers ON merchants.merchants_name = vouchers.program_name) 
LEFT JOIN voucher_type_lookup ON vouchers.ID = voucher_type_lookup.ID) 
LEFT JOIN voucher_type ON voucher_type_lookup.voucher_type_id =voucher_type.voucher_type_id)
LEFT JOIN category ON merchantscategory.category_id = category.category_id 
WHERE (merchants.merchants_id = @merchants_id OR @merchants_id = -1) 
AND (IIF(ISNULL(vouchers.vouchers_category_id), merchantscategory.category_id = @category_id OR @category_id = -1, vouchers.vouchers_category_id = @category_id OR @category_id = -1))
AND ((program_name like '%'+@searchterm+'%' OR vouchers.description like '%'+@searchterm+'%' OR @searchterm='-1') AND (IIF(ISNULL(vouchers.vouchers_category_id), merchantscategory.category_id = @searchterm_category_id OR @searchterm_category_id = -1, vouchers.vouchers_category_id = @searchterm_category_id OR @searchterm_category_id = -1))) 
AND merchants.merchants_inactive = False 
AND end_date >= Date() 
AND start_date <= Date() 
ORDER BY start_Date DESC 

Web Development Manager
http://www.freemoneyoff.co.uk
http://www.freemoneyresource.co.uk
http://www.freemoneysurveys.co.uk

RE: Convert Access SQL to SQL Server

Looks like your statement should work, except for the IIF part
You may want to investigate CASE statement instead. Here is one post, and here is another about it. Google for more examples.


---- Andy

There is a great need for a sarcasm font.

RE: Convert Access SQL to SQL Server

Something like

CODE

SELECT DISTINCT vouchers.ID as vid
              , vouchers.*
              , merchants.merchants_logo
              , merchants.merchants_id
              , merchants.merchants_name
              , merchants.merchants_display_name
              , voucher_type.*
FROM merchantscategory 
INNER JOIN merchants           ON merchantscategory.merchants_id      = merchants.merchants_id) 
INNER JOIN vouchers            ON merchants.merchants_name            = vouchers.program_name 
LEFT  JOIN voucher_type_lookup ON vouchers.ID                         = voucher_type_lookup.ID) 
LEFT  JOIN voucher_type        ON voucher_type_lookup.voucher_type_id = voucher_type.voucher_type_id)
LEFT  JOIN category            ON merchantscategory.category_id       = category.category_id 
WHERE merchants.merchants_id = @merchants_id OR @merchants_id = -1
 AND (@category_id = -1 OR COALESCE(vouchers.vouchers_category_id, merchantscategory.category_id)= @category_id)
 AND (program_name            like '%'+@searchterm+'%'
      OR vouchers.description like '%'+@searchterm+'%'
      OR @searchterm='-1')
 AND (@searchterm_category_id = -1 OR COALESCE(vouchers.vouchers_category_id, merchantscategory.category_id)= @searchterm_category_id)
 AND merchants.merchants_inactive = False 
 AND end_date   >= GetDate() 
 AND start_date <= GetDate() 
ORDER BY start_Date DESC 

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server

RE: Convert Access SQL to SQL Server

Boris seem to have done a good job, you'll be the judge, as it's hardly decidable not having test data and being able to compare results

Aside of that:
1. You can use IIF in SQL Server since 2012 so that shorthand of CASE should be available: https://docs.microsoft.com/en-US/sql/t-sql/functio...
2. You can use 'false' in T-SQL instead of 0: SELECT CAST('false' as bit) or query ... WHERE bitfield='false' ( or 'true of course) - that is even older.

Bye, Olaf.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close