Avatar of sammySeltzer
sammySeltzer
Flag for United States of America asked on

Another complicated query mess

Trust me, I didn't write this but I am trying to make changes to it and I am having some problems.

Please this loooong code:

SELECT
	aa.[REQUESTID] AS 'RequestID',
	convert(char(10),aa.[DATETIMEINIT],101) AS 'RequestInitiatedDate',
	aa.[STATUS] AS 'RequestStatus',
	convert(char(10),aa.[DATETIMECLOSED],101) AS 'RequestClosedDate',
	aa.[DATEDISPATCHTO] AS 'WO InitiatedDate',
	aa.[INITIATEDBY] As 'RequestorName',
	aa.[RequestorDept] AS 'RequestorDepartment',
	aa.[RequestorWorkPhone],
	aa.[RequestorEmail],
	aa.[DESCRIPTION] AS 'RequestDescription',
	aa.[DETAILS] AS 'RequestComments',
	aa.[PROBLOCATION] AS 'RequestLocation',
	bb.[PrimarySiteContactDisplay] AS 'PrimarySiteContact',
	bb.[SecondarySiteContactDisplay] AS 'SecondarySiteContact'

FROM
	(
	SELECT
		*
	FROM
		(
		SELECT
			[REQUESTID],
			[INITIATEDBY],
			[DESCRIPTION],
			[DETAILS],
			convert(char(10),[DATETIMEINIT],101) as 'DATETIMEINIT',
			REPLACE((REPLACE((REPLACE((REPLACE([STATUS],'CLOSED','Closed')),'OPEN','Open')),'CANCEL','Canceled')),'INPROGRESS','In Progress') AS 'STATUS',
			[PROBLOCATION],
			[DISPATCHTO],
			[DATEDISPATCHTO],
			[DATETIMECLOSED]
		FROM
			[REQUEST]
		WHERE
			[INITIATEDBY]
		IN
			(
			SELECT
				[TRIM](ISNULL([LASTNAME],'')+', '+ISNULL([FIRSTNAME],''))+' '+ISNULL([MIDDLEINITIAL],''))
			FROM
				[EMPLOYEE]
			WHERE
				ISNULL([ORGANIZATION],'') =
					(
					SELECT
						ISNULL([ORGANIZATION],'')
					FROM
					[EMPLOYEE]
					WHERE
						lower([LOGINNAME]) = lower('$loginName')
					AND
						[DOMAINID] = 4
					)
			)
		) a
LEFT JOIN
	(

	SELECT
		[TRIM](ISNULL([LASTNAME],'')+', '+ISNULL([FIRSTNAME],''))+' '+ISNULL([MIDDLEINITIAL],'')) AS 'RequestorFullName',
		[WORKPHONE] AS 'RequestorWorkPhone',
		[EMAIL] AS 'RequestorEmail',
		[ORGANIZATION] AS 'RequestorDept'
	FROM
		[EMPLOYEE]
	WHERE
		[DOMAINID] = 4
	) AS b
ON
	a.[INITIATEDBY] = b.[RequestorFullName]
	) aa
LEFT JOIN
	(
	SELECT
		[REQUESTID],
		[dbo].[TRIM](ISNULL([LASTNAME],'')+', '+ISNULL([FIRSTNAME],''))+' '+ISNULL([MIDDLEINITIAL],'')) AS 'SiteContactFullName',
		[WORKPHONE] AS 'SiteContactWorkPhone',
		[CELLPHONE] AS 'SiteContactCellPhone',
		[EMAIL] AS 'SiteContactEmail',
		ISNULL([LASTNAME], '')
		+', '+
		ISNULL([FIRSTNAME], '')
		+', Title: '+
		ISNULL([TITLE], '')
		+', Work: '+
		ISNULL([WORKPHONE], '')
		+', Mobile: '+
		ISNULL([CELLPHONE], '')
		+', Email: '+
		ISNULL([EMAIL], '')	AS 'PrimarySiteContactDisplay',
		[COMMENTS] AS 'SecondarySiteContactDisplay'
	FROM
		[CUSTOMERCALL]
	) bb
ON
	aa.[REQUESTID] = bb.[REQUESTID]
WHERE
	aa.[RequestID] = '$requestID'
ORDER BY
	aa.[DATETIMEINIT] DESC

Open in new window


I am trying to make one change and that is to replace this:

      aa.[DATEDISPATCHTO] AS 'WO InitiatedDate',

with this:
WorkOrderID.

To do so, I changed lines 23 to 35 to these lines and highlighted the changes I made. Could you please tell me what I am doing wrong?

            SELECT
                  r.[REQUESTID],
                  rw.[WORKORDERID],
                  r.[INITIATEDBY],
                  r.[DESCRIPTION],
                  [DETAILS],
                  convert(char(10),[DATETIMEINIT],101) as 'DATETIMEINIT',
                  REPLACE((REPLACE((REPLACE((REPLACE(r.[STATUS],'CLOSED','Closed')),'OPEN','Open')),'CANCEL','Canceled')),'INPROGRESS','In Progress') AS 'STATUS',
                  [PROBLOCATION],
                  [DISPATCHTO],
                  [DATEDISPATCHTO],
                  [DATETIMECLOSED]
FROM
[REQUESTWORKORDER] rw
INNER JOIN [REQUEST] r on rw.REQUESTID = r.REQUESTID
INNER JOIN [WORKORDER] w on rw.WORKORDERID = w.WORKORDERID


I would have loved to rewrite entire query but I must admit that I don't understand the query and no comments on the code.

Thanks a lot in advance
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
sammySeltzer

8/22/2022 - Mon
SOLUTION
Gary

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sammySeltzer

ASKER
Hi again, Gary.

No errors that I can see.
Gary

that I don't understand the query
Mind boggling

I don't know, it all looks OK
Are you running this through code, can you try a direct input query, the error message would help
john15nlt

If you just run the snippet of code from 23-35 in that part of the select statement and not execute the whole thing do you get what you desire?

This might help

how joins work
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
sammySeltzer

ASKER
Mind boggling that I said I don't understand the query?

Well, I don't. If I did, I would have rewritten it because I think it is badly written.

I am not really badly off with queries but if I had written this, at the very least, I would have put in a comment as to what the code is supposed to be doing.
Gary

The query is mind boggling!
So many nested selects
sammySeltzer

ASKER
John,

That was very funny.

I KNOW joins.

It doesn't change the fact that this query is unnecessarily complex.

I am sure if it wasn't someone would have rewritten it by now.

I am not saying that's what I am asking for.

I just need help with the one fieldname I am trying to fit into the query.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sammySeltzer

ASKER
Yes, I agree Gary.

I actually contacted the guy that wrote it and asked if he could explain what the query is supposed to be doing and his reply was that it was too complex to explain.

What an answer given that he wrote it according to him.
sammySeltzer

ASKER
Ok, apparently, the code I posted works.

The only issue is that if there is no match, my php app can't spit out an error message.
Gary

Follow @john's advice and try running the constituent parts of the query and see if they are working.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
sammySeltzer

ASKER
Gary, as stated in my last post, my original query that I posted here works and you are correct in stating that you don't see anything wrong with my original code.

The only issue is that there is no match, the app doesn't tell you, "no match",no errors.

It just sits there.

That's what gave me the impression that it doesn't work.
Gary

I was posting that as you posted your comment
john15nlt

I wasn't trying to be funny but at least you didn't get mad, I was just making sure you knew what you were trying to get and how to get there.

If the sub-part returns the results you want but the whole query does not have you considered that you have added an alias to the sub-select query and that the join does not recognize it?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sammySeltzer

ASKER
John,

I don't get mad when someone is trying to help me.

I only get mad when someone was just being mischievous with no desire to assist.

Ok, back to the query issue. I stated earlier that I didn't write this query and I don't understand it at all.

The fact that I am even able to make the change I made is a testament to the fact that I am not faint-hearted when it comes to sql.

There are just folks like you guys much better than me.

I am wondering if a LEFT JOIN on REQUEST table will solve the problem of the query doing nothing when no match is found.
john15nlt

I left join would solve just that problem.
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sammySeltzer

ASKER
Hi Scott,

Actually, there is function that combines rtrim and ltrim and that function is called Trim.

So, that part works.

Also, the query itself does work.

However, just running entire code again in SSMS,  produced the following error:

Error converting data type varchar to numeric

I can't think of what is causing this problem other than the fact that Request table has requestId as int data type.

This table is joined with RequestWorkOrder table on RequestId but requestId is defined as decimal on this table.

My theory then is that if there is no RequestId match between Request table and RequestWorkOrder table, then this error occurs.

That's just my theory. You experts probably know better.

Sorry Gary and John. I did not get this error initially that's because I probably overlooked something.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Gary

The error means you are doing a string comparison against a number
Check your comparison values in the inner joins are of the same data type.
sammySeltzer

ASKER
There is no string to number comparison in the WHERE clause.

I just said what I thought could be the problem.

RequestId on one table is integer and is decimal on another table.

So, in a situation where there is no match, ie, one table has the key and the other doesn't, we have this issue.

I am digging to try and find a solution to try and see if that fixes it.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sammySeltzer

ASKER
It turns out that a non-numeric value was saved to the database.

This boggles the mind because it is not simply possible.

They enter a value like 12 33 with space between 12 and 33.

As for the app doing nothing if there is no match, I used left join and now it seems to be working.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.