• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

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
0
sammySeltzer
Asked:
sammySeltzer
  • 10
  • 6
  • 3
  • +2
3 Solutions
 
GaryCommented:
Whats the error message?
0
 
sammySeltzerAuthor Commented:
Hi again, Gary.

No errors that I can see.
0
 
GaryCommented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
john15nltCommented:
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
0
 
sammySeltzerAuthor Commented:
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.
0
 
GaryCommented:
The query is mind boggling!
So many nested selects
0
 
sammySeltzerAuthor Commented:
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.
0
 
sammySeltzerAuthor Commented:
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.
0
 
sammySeltzerAuthor Commented:
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.
0
 
GaryCommented:
Follow @john's advice and try running the constituent parts of the query and see if they are working.
0
 
sammySeltzerAuthor Commented:
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.
0
 
GaryCommented:
I was posting that as you posted your comment
0
 
john15nltCommented:
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?
0
 
sammySeltzerAuthor Commented:
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.
0
 
john15nltCommented:
I left join would solve just that problem.
0
 
Scott PletcherSenior DBACommented:
Doesn't look that bad to me, structure-wise; fairly straight-forward actually, matching on specific input values.

But [TRIM] is not valid in T-SQL, you need to use RTRIM and/or LTRIM; I'm assuming RTRIM only below, but oc you can change that.  Also, not exactly sure how to get to a specific WORKORDERID, but you can adjust the subquery if needed for that too:


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',
      a.[WORKORDERID],
      aa.[INITIATEDBY] As 'RequestorName',
...
     FROM
            (
            SELECT
                  [REQUESTID],
                  (SELECT TOP (1) rw.[WORKORDERID]
                   FROM [REQUESTWORKORDER] rw
                   WHERE
                       rw.REQUESTID = r.REQUESTID
                  ) AS WORKORDERID
,
                  [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
                        RTRIM((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
0
 
sammySeltzerAuthor Commented:
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.
0
 
GaryCommented:
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.
0
 
sammySeltzerAuthor Commented:
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.
0
 
Koen Van WielinkIT ConsultantCommented:
Hi Sammy,

A comparison between an int and a decimal would not throw an error stating a varchar conversion error, so it's not likely that that's the reason.
This might be stating the obvious, but when you ran the entire query, did you replace your '$requestID' string in the last where clause with a valid ID?

WHERE
	aa.[RequestID] = '$requestID'

Open in new window


If you run it "as is" obviously that's going to throw the error you mention, as the string '$requestID' is compared against the numerid RequestID.
0
 
sammySeltzerAuthor Commented:
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now