Link to home
Start Free TrialLog in
Avatar of sammySeltzer
sammySeltzerFlag 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
SOLUTION
Avatar of Gary
Gary
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sammySeltzer

ASKER

Hi again, Gary.

No errors that I can see.
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
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

User generated image
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.
The query is mind boggling!
So many nested selects
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.
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.
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.
Follow @john's advice and try running the constituent parts of the query and see if they are working.
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.
I was posting that as you posted your comment
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?
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.
I left join would solve just that problem.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.