Converting Access query to MSSQL 2005

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

General SQL Server Forums

New to SQL Server Programming

Converting Access query to MSSQL 2005

Author

Topic

versionke
Starting Member

2 Posts

Posted - 2010-08-11 : 15:43:55

Hey, everyone. I'm familiar with Access but am needing to upgrade one of my application from an Access backend to a MS SQL 2005 backend which I have little experience with. I've run through the upscale wizard and that worked great. The last thing I'd like to do is to convert a few access queries to SQL views with the hopes that it'll speed things up.

I've converted some simple queries and they're working, however one query I'd like to convert uses Now() and IIf which I read need to be changed to GETDATE() and CASE. Below is the original Access generated SQL along with my attempt at converting it. Of course it errors however after staring at this for hours, I just don't know enough about the proper syntax to get it working...

Access SQL:
----------------------
SELECT [_tblCompany].CompanyNameID, IIf([NextFollowUpDate]<Now() And [tblLstCompanyStatus].[CompanyStatus]="Active" Or [NextFollowUpDate] Is Null And [tblLstCompanyStatus].[CompanyStatus]="Active","•","") AS Flag, [_tblCompany].CompanyName, [_tblCompany].MCID, Min([_tblCreditApp].AccountNumber) AS MinOfAccountNumber, [_tblCompany].NextFollowUpDate, qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived, qryMaxAppRequestDateCA.CreditAnalystName, tblLstCompanyStatus.CompanyStatus, qryMaxAppRequestDateCA.CountOfCreditAppID, [_tblCompany].CompanyNotes
FROM ((_tblCompany INNER JOIN tblLstCompanyStatus ON [_tblCompany].CompanyStatus=tblLstCompanyStatus.CompanyStatusID) LEFT JOIN qryMaxAppRequestDateCA ON [_tblCompany].CompanyNameID=qryMaxAppRequestDateCA.CompanyNameID) LEFT JOIN _tblCreditApp ON [_tblCompany].CompanyNameID=[_tblCreditApp].CompanyNameID
WHERE (((tblLstCompanyStatus.CompanyStatusID) Like "*"))
GROUP BY [_tblCompany].CompanyNameID, IIf([NextFollowUpDate]<Now() And [tblLstCompanyStatus].[CompanyStatus]="Active" Or [NextFollowUpDate] Is Null And [tblLstCompanyStatus].[CompanyStatus]="Active","•",""), [_tblCompany].CompanyName, [_tblCompany].MCID, [_tblCompany].NextFollowUpDate, qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived, qryMaxAppRequestDateCA.CreditAnalystName, tblLstCompanyStatus.CompanyStatus, qryMaxAppRequestDateCA.CountOfCreditAppID, [_tblCompany].CompanyNotes
ORDER BY [_tblCompany].CompanyName;

And my attempt at SQL:
----------------------
SELECT dbo._tblCompany.CompanyNameID, 'Flag' =
CASE
WHEN NextFollowUpDate < GETDATE() AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•'
WHEN NextFollowUpDate IS NULL AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•'
ELSE ''
END,
dbo._tblCompany.CompanyName, dbo._tblCompany.MCID, Min(dbo._tblCreditApp.AccountNumber) AS MinOfAccountNumber, dbo._tblCompany.NextFollowUpDate,
dbo.qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived, dbo.qryMaxAppRequestDateCA.CreditAnalystName, tblLstCompanyStatus.CompanyStatus,
dbo.qryMaxAppRequestDateCA.CountOfCreditAppID, dbo._tblCompany.CompanyNotes

FROM ((dbo._tblCompany INNER JOIN tblLstCompanyStatus ON dbo._tblCompany.CompanyStatus = tblLstCompanyStatus.CompanyStatusID) LEFT JOIN dbo.qryMaxAppRequestDateCA

ON dbo._tblCompany.CompanyNameID = dbo.qryMaxAppRequestDateCA.CompanyNameID) LEFT JOIN dbo._tblCreditApp ON dbo._tblCompany.CompanyNameID = dbo._tblCreditApp.CompanyNameID

WHERE (((tblLstCompanyStatus.CompanyStatusID) Like '%'))

GROUP BY dbo._tblCompany.CompanyNameID, 'Flag' =
CASE
WHEN NextFollowUpDate < GETDATE() AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•'
WHEN NextFollowUpDate IS NULL AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•'
ELSE ''
END,
dbo._tblCompany.CompanyName, dbo._tblCompany.MCID, dbo._tblCompany.NextFollowUpDate,
dbo.qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived, dbo.qryMaxAppRequestDateCA.CreditAnalystName, tblLstCompanyStatus.CompanyStatus,
dbo.qryMaxAppRequestDateCA.CountOfCreditAppID, dbo._tblCompany.CompanyNotes

ORDER BY dbo._tblCompany.CompanyName;

Any thoughts on this would be greatly appreciated!

Thanks,
Kelly

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 01:55:45

i've added some comments.
your query looks fine i just deleted the "'FLAG'= " in GROUP BY statement.

everything else should work.


SELECT 
	dbo._tblCompany.CompanyNameID 
	,'Flag' =	CASE
	WHEN NextFollowUpDate < GETDATE() AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•'
	WHEN NextFollowUpDate IS NULL AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•' 
	ELSE ''	END
	,dbo._tblCompany.CompanyName
	,dbo._tblCompany.MCID
	,Min(dbo._tblCreditApp.AccountNumber) AS MinOfAccountNumber
	,dbo._tblCompany.NextFollowUpDate
	,dbo.qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived 
	,dbo.qryMaxAppRequestDateCA.CreditAnalystName 
	,tblLstCompanyStatus.CompanyStatus,
	,dbo.qryMaxAppRequestDateCA.CountOfCreditAppID 
	,dbo._tblCompany.CompanyNotes 

FROM	dbo._tblCompany 
	INNER JOIN tblLstCompanyStatus 
	ON dbo._tblCompany.CompanyStatus = tblLstCompanyStatus.CompanyStatusID 
	LEFT JOIN dbo.qryMaxAppRequestDateCA 
	ON dbo._tblCompany.CompanyNameID = dbo.qryMaxAppRequestDateCA.CompanyNameID
	 LEFT JOIN dbo._tblCreditApp ON dbo._tblCompany.CompanyNameID = dbo._tblCreditApp.CompanyNameID

WHERE tblLstCompanyStatus.CompanyStatusID Like '%'

GROUP BY 
		dbo._tblCompany.CompanyNameID
	,CASE -- DELETED THE 'FLAG'
	WHEN NextFollowUpDate < GETDATE() AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•'
	WHEN NextFollowUpDate IS NULL AND tblLstCompanyStatus.CompanyStatus='Active' THEN '•' 
	ELSE ''END
	,dbo._tblCompany.CompanyName
	,dbo._tblCompany.MCID
	,dbo._tblCompany.NextFollowUpDate
	,dbo.qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived
	,dbo.qryMaxAppRequestDateCA.CreditAnalystName
	,tblLstCompanyStatus.CompanyStatus
	,dbo.qryMaxAppRequestDateCA.CountOfCreditAppID
	,dbo._tblCompany.CompanyNotes

ORDER BY dbo._tblCompany.CompanyName

versionke
Starting Member

2 Posts

Posted - 2010-08-12 : 11:49:26

Thanks. I was able to get it working last night. The main issue I was having was trying to do the GROUP BY on an ntext field. By CASTing it to a varchar (which is fine for me in this case, I was able to get it working. The working code was:


SELECT     dbo._tblCompany.CompanyNameID, CASE WHEN NextFollowUpDate < GETDATE() AND 
                      dbo.tblLstCompanyStatus.CompanyStatus = 'Active' THEN '•' WHEN NextFollowUpDate IS NULL AND 
                      dbo.tblLstCompanyStatus.CompanyStatus = 'Active' THEN '•' ELSE '' END AS Flag, dbo._tblCompany.CompanyName, dbo._tblCompany.MCID, 
                      MIN(dbo._tblCreditApp.AccountNumber) AS MinOfAccountNumber, dbo._tblCompany.NextFollowUpDate, 
                      dbo.qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived, dbo.qryMaxAppRequestDateCA.CreditAnalystName, 
                      dbo.tblLstCompanyStatus.CompanyStatus, dbo.qryMaxAppRequestDateCA.CountOfCreditAppID, 
                      CAST(dbo._tblCompany.CompanyNotes AS varchar(4000)) AS CompanyNotes
FROM         dbo._tblCompany INNER JOIN
                      dbo.tblLstCompanyStatus ON dbo._tblCompany.CompanyStatus = dbo.tblLstCompanyStatus.CompanyStatusID LEFT OUTER JOIN
                      dbo._tblCreditApp ON dbo._tblCompany.CompanyNameID = dbo._tblCreditApp.CompanyNameID LEFT OUTER JOIN
                      dbo.qryMaxAppRequestDateCA ON dbo._tblCompany.CompanyNameID = dbo.qryMaxAppRequestDateCA.CompanyNameID
GROUP BY dbo._tblCompany.CompanyNameID, dbo._tblCompany.CompanyName, dbo._tblCompany.MCID, CASE WHEN NextFollowUpDate < GETDATE() AND 
                      dbo.tblLstCompanyStatus.CompanyStatus = 'Active' THEN '•' WHEN NextFollowUpDate IS NULL AND 
                      dbo.tblLstCompanyStatus.CompanyStatus = 'Active' THEN '•' ELSE '' END, dbo._tblCompany.NextFollowUpDate, 
                      dbo.qryMaxAppRequestDateCA.MaxOfDateAppRequestReceived, dbo.qryMaxAppRequestDateCA.CreditAnalystName, 
                      dbo.tblLstCompanyStatus.CompanyStatus, dbo.qryMaxAppRequestDateCA.CountOfCreditAppID, 
                      CAST(dbo._tblCompany.CompanyNotes AS varchar(4000))

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 15:38:21

great! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources