Convert Access SQL to SQL Server 1

[stinkybee]

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

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

 

[Andrzejek]

Looks like your statement should work, except for the [tt]IIF[/tt] 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.

 

[stinkybee]

Thanks for the reply, hopefully that is a good starting point. I'll let you know if I manage to sort it.

Web Development Manager

http://www.freemoneyoff.co.uk

http://www.freemoneyresource.co.uk

http://www.freemoneysurveys.co.uk

 

[bborissov]

Something like

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

 

[stinkybee]

That's got it, apart from changing "false" to 0

Thanks

Web Development Manager

http://www.freemoneyoff.co.uk

http://www.freemoneyresource.co.uk

http://www.freemoneysurveys.co.uk

 

[Olaf Doschke]

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/functions/logical-functions-iif-transact-sql

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.