SQL Conversion from Access to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Conversion from Access to SQL Server

Author

Topic

eliasct
Starting Member

3 Posts

Posted - 2002-07-03 : 06:59:31

IIf(IsNull([bgs_invoice_history]![approx_sterling_inv_val]),[bgs_orders]![net_GBP],[bgs_invoice_history]![approx_sterling_inv_val]) AS GBP_NET,

The above works fine in Access, but when it gets to SQL Server, this shows error.... The entire SQL statement is given below...

Can anyone help me in knowing how to convert this so that it works in SQL server?

SELECT bgs_orders.customer_id, bgs_orders.billing_company_id, bgs_issues.year, magazines.magazine, bgs_issues.issue, bgs_ads.colour, bgs_space_defs.space_description, bgs_currencies.currency_abbrev, bgs_ads.position, bgs_sales_execs.initials, bgs_ads.ad_total, bgs_ads.agency_discount, IIf(IsNull([bgs_invoice_history]![approx_sterling_inv_val]),[bgs_orders]![net_GBP],[bgs_invoice_history]![approx_sterling_inv_val]) AS GBP_NET, bgs_invoice_history.invoice_id, bgs_orders.notes FROM ((((((bgs_ads INNER JOIN [bgs_link_ad-issue] ON bgs_ads.ad_id = [bgs_link_ad-issue].ad_id) INNER JOIN (bgs_orders INNER JOIN magazines ON bgs_orders.magazine_id = magazines.magazine_id) ON bgs_ads.order_id = bgs_orders.order_id) LEFT JOIN bgs_invoice_history ON ([bgs_link_ad-issue].ad_id = bgs_invoice_history.ad_id) AND ([bgs_link_ad-issue].issue_id = bgs_invoice_history.issue_id)) INNER JOIN bgs_issues ON [bgs_link_ad-issue].issue_id = bgs_issues.[issue-id]) INNER JOIN bgs_space_defs ON bgs_ads.space_id = bgs_space_defs.space_id) INNER JOIN bgs_currencies ON bgs_orders.currency_id = bgs_currencies.currency_id) INNER JOIN bgs_sales_execs ON bgs_orders.exec_id = bgs_sales_execs.exec_id WHERE (((bgs_orders.customer_id)=21877)) ORDER BY bgs_issues.year DESC , magazines.magazine, bgs_issues.issue_date DESC

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 07:14:13

Eliasct,

Firstly, the following are Access form field names: (you need to replace them with scaler values or use parameters from a stored proc)

[bgs_invoice_history]![approx_sterling_inv_val]
[bgs_orders]![net_GBP]

Secondly, the Immediate If function (IIF) is not supported in T-SQL. You need to replace these with T-SQL CASE statements.

For example:

IIF(a=1,b,c)

becomes

CASE a WHEN 1 THEN b ELSE c END

Thirdly, Access does a great job of throwing in excess brackets left, right and center. This just adds to confusion - I've only had a quick glance over your query but I think you can lose them all.

HTH,

macka.

eliasct
Starting Member

3 Posts

Posted - 2002-07-03 : 07:27:10

Well, This says that there is an error near case.. Sorry if I am disturbing you

GBP_NET : CASE bgs_invoice_history.[approx_sterling_inv_val] is null WHEN true THEN [bgs_orders].[net_GBP] ELSE [bgs_invoice_history].[approx_sterling_inv_val] END

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 07:36:47

Try changing it to:

CASE WHEN bgs_invoice_history.[approx_sterling_inv_val] is null THEN [bgs_orders].[net_GBP] ELSE [bgs_invoice_history].[approx_sterling_inv_val] END

macka.

eliasct
Starting Member

3 Posts

Posted - 2002-07-03 : 07:43:28

Again the error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CASE'.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-07-03 : 07:57:08

SELECT
bo.customer_id,
bo.billing_company_id,
bi.[year],
m.magazine,
bi.issue,
ba.colour,
bsd.space_description,
bc.currency_abbrev,
ba.position,
bse.initials,
ba.ad_total,
ba.agency_discount,
CASE
WHEN bih.approx_sterling_inv_val is null THEN bo.net_GBP ELSE bih.approx_sterling_inv_val
END
FROM bgs_ads ba
INNER JOIN bgs_link_ad-issue bla
ON ba.ad_id = bla.ad_id
INNER JOIN bgs_orders bo
INNER JOIN magazines
ON bo.magazine_id = m.magazine_id
ON bgs_ads.order_id = bgs_orders.order_id
LEFT JOIN bgs_invoice_history bih
ON bla.ad_id = bih.ad_id
AND bla.issue_id = bih.issue_id
INNER JOIN bgs_issues bi
ON bla.issue_id = bi.[issue-id]
INNER JOIN bgs_space_defs bsd
ON ba.space_id = bsd.space_id
INNER JOIN bgs_currencies bc
ON bo.currency_id = bc.currency_id
INNER JOIN bgs_sales_execs bse
ON bo.exec_id = bse.exec_id
WHERE bo.customer_id = 21877
ORDER BY bi.[year] DESC,
m.magazine,
bi.issue_date DESC

Run this in QA and tell me what error it gives you...

Peace

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources