Solved

Another complicated query mess

Posted on 2014-12-02
21
103 Views
Last Modified: 2014-12-03
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
Comment
Question by:sammySeltzer
  • 10
  • 6
  • 3
  • +2
21 Comments
 
LVL 58

Assisted Solution

by:Gary
Gary earned 200 total points
ID: 40477111
Whats the error message?
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477137
Hi again, Gary.

No errors that I can see.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40477160
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
 
LVL 1

Expert Comment

by:john15nlt
ID: 40477172
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477173
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
 
LVL 58

Expert Comment

by:Gary
ID: 40477177
The query is mind boggling!
So many nested selects
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477180
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477187
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477199
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
 
LVL 58

Expert Comment

by:Gary
ID: 40477200
Follow @john's advice and try running the constituent parts of the query and see if they are working.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477234
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
 
LVL 58

Expert Comment

by:Gary
ID: 40477237
I was posting that as you posted your comment
0
 
LVL 1

Expert Comment

by:john15nlt
ID: 40477292
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477305
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
 
LVL 1

Expert Comment

by:john15nlt
ID: 40477340
I left join would solve just that problem.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 200 total points
ID: 40477363
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477606
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
 
LVL 58

Expert Comment

by:Gary
ID: 40477612
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40477621
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
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 100 total points
ID: 40477835
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
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40478435
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now