Access to TSQL help please - SQL Server Forums

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

Transact-SQL (2000)

Access to TSQL help please

Author

Topic

Skydolphin
Starting Member

43 Posts

Posted - 2007-03-30 : 13:24:29

Hi, I was just handed an Access Query for a help ticket systme that I need to recreate in a stored procedure. I have no idea where to begin. Can someone help?

SELECT tblTicket.ID AS TicketID, tblVersion.strDesc AS Version, tblTicket.strSubject, tblTicket.strDescription, tblTicket.strAssigned AS AssignedTo, tblTicket.dtSubmitDate, tblTicket.dtCloseDate, tblTicketNote.strNote, tblTicketNote.dtDateInserted, tblTicket.strResolution, [CharToFind] AS SearchCriteria, IIf(InStr(1,[strDescription],[CharToFind])>0,"Description",IIf(InStr(1,[strResolution],[CharToFind])>0,"Resolution",IIf(InStr(1,[strNote],[CharToFind])>0,"Note","<unknown>"))) AS ResultFoundIn
FROM (tblTicket LEFT JOIN tblTicketNote ON tblTicket.ID = tblTicketNote.intTicketID) LEFT JOIN tblVersion ON tblTicket.intVersionID = tblVersion.ID
WHERE (((tblTicket.strDescription) Like "*" & [CharToFind] & "*")) OR (((tblTicket.strResolution) Like "*" & [CharToFind] & "*")) OR (((tblTicketNote.strNote) Like "*" & [CharToFind] & "*"))
ORDER BY tblTicket.ID, tblTicket.dtSubmitDate, tblTicketNote.dtDateInserted

Any help would be appreciated. Thanks.

Rhonda

Rhonda

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-30 : 13:36:43

Hey! Haven't seen you around in a loooooong while

Boy do I hate Access SQL...first thing I would do is to format it

No need for the Parathesis...and [CharToFind] is most likely an "input variable" that access will prompt yo for input..the IIF Function is acting like CASE but is much more limited and painful to read...the '*'s become '%' and the '&'s become'+'...~~also the GROUP BY is misleading because all they want here are DISTINCT Rows...it's an entry level (and wrong) way to go about it...because the QBE doesn't graphicallly tell you how to do that~~

Sorry, misread that, I just assumed

EDIT: And not to start the debate raging, I would simplify the alias's to 1 char each instead of the would tablename for readability


   SELECT tblTicket.ID AS TicketID
	, tblVersion.strDesc AS Version
	, tblTicket.strSubject
	, tblTicket.strDescription
	, tblTicket.strAssigned AS AssignedTo
	, tblTicket.dtSubmitDate
	, tblTicket.dtCloseDate
	, tblTicketNote.strNote
	, tblTicketNote.dtDateInserted
	, tblTicket.strResolution, [CharToFind] AS SearchCriteria
	, IIf(InStr(1,[strDescription],[CharToFind])>0
		,"Description",IIf(InStr(1,[strResolution],[CharToFind])>0
		,"Resolution",IIf(InStr(1,[strNote],[CharToFind])>0
		,"Note","<unknown>"))) AS ResultFoundIn
     FROM tblTicket 
LEFT JOIN tblTicketNote 
       ON tblTicket.ID = tblTicketNote.intTicketID
LEFT JOIN tblVersion 
       ON tblTicket.intVersionID = tblVersion.ID
    WHERE tblTicket.strDescription Like "*" & [CharToFind] & "*"
       OR tblTicket.strResolution Like "*" & [CharToFind] & "*"
       OR tblTicketNote.strNote Like "*" & [CharToFind] & "*"
 ORDER BY tblTicket.ID, tblTicket.dtSubmitDate, tblTicketNote.dtDateInserted

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-30 : 14:28:44

How about this


CREATE PROC mySproc99
	@CharToFind varchar(255)
AS

   SELECT t.ID AS TicketID
	, v.strDesc AS Version
	, t.strSubject
	, t.strDescription
	, t.strAssigned AS AssignedTo
	, t.dtSubmitDate
	, t.dtCloseDate
	, n.strNote
	, n.dtDateInserted
	, t.strResolution, @CharToFind AS SearchCriteria
	, CASE
		WHEN LEN(REPLACE([strDescription],@CharToFind) < LEN([strDescription])	THEN 'Description'
		WHEN LEN(REPLACE([strResolution],@CharToFind)  < LEN([strDescription])	THEN 'Resolution'
		WHEN LEN(REPLACE([strNote],@CharToFind)        < LEN([strDescription]) 	THEN 'Note'
		ELSE '<unknown>'
	  END AS ResultFoundIn
     FROM tblTicket 	t
LEFT JOIN tblTicketNote n 
       ON t.ID = n.intTicketID
LEFT JOIN tblVersion	v 
       ON t.intVersionID = v.ID
    WHERE t.strDescription Like '%' + @CharToFind + '%'
       OR t.strResolution  Like '%' + @CharToFind + '%'
       OR n.strNote 	   Like '%' + @CharToFind + '%'
 ORDER BY t.ID, t.dtSubmitDate, n.dtDateInserted

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-30 : 15:57:50

I recently posted an "Access-To-SQL cheatsheet" that might be helpful.

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

It's not all inclusive or anything, but it covers a lot of the basics.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources