Solved

Another complicated query mess

Posted on 2014-12-02
21
109 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 29

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 29

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 29

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 29

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 29

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
 
LVL 29

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 29

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:
Scott Pletcher 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 29

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 29

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 13

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 29

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 5 57
ms sql + help with query 2 44
SQL Server 2005 running VERY slowly on new hardware 22 61
T-SQL: Wrong Result 7 38
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …

732 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