December 13, 2013 at 8:21 am
ChrisM@Work (12/13/2013)
Greg Edwards-268690 (12/13/2013)
Chris -I agree with a lot of your Why's. See this all the time.
If you could look at this, run the view for next of kin info, and tell me a better way, I'd appreciate it.
Not exactly the same as what the poster has, but I think close enough in concept.
Thanks.
I've never been a big fan of views Greg, a device designed to simplify coding almost always ends up doing the exact opposite through overuse -mostly ignorance. Your approach is nice though. The views could easily fit into a query which visibly models the business case. Here's the other side of that coin:
"What are you trying to do in this crazy 12,000 line stored procedure?"
"This and this and that and that and then something else then back to the beginning and repeat for each branch, and then go on to the next month, and that's only this year! It's really really complicated and it's taking like 48 nested cursors to process each piece!"
"What are you really trying to do?"
"Get last year's gross and net sales figures aggregated by branch and month and compare with what we've got so far for this year".
😀
To me, the view can be a way to break down the coding at times. They (when used properly) can keep business logic in 1 place, where sometimes queries are replicated several times, and usually end up tweaked at some point, giving different results.
Part of my view usage comes from the DW background, and usually we would have a table with 1 current record, and older records. Many times only the current record was of interest, so that was a view exposed to the users. As you can see, it can greatly simplify things when you have outer join needs, but have to select a certain record type out of the right hand file.
Tried your cross apply, but was seeing a cartesian join giving more results.
Since you have my sample, if you have a few minutes, I would be very interested in seeing a query giving the same results off the base tables without the views. I know I have a lot to learn.
Felt the same pains as you many times.
Your last year to this year was something we ran across many times.
At the end of the day, usually what they want is to be able to predict are we coming in ahead or behind the prior year.
Our answer was daily rates in most cases, especially as our fiscal calendar was a 4 4 5 week arrangement, with 52 weeks in every year.
Why over complicate? Some think it's job security. To me, some of the most elegant solutions are the simplest.
December 13, 2013 at 9:07 am
Here you are Greg, I'll leave you to draw your own conclusions.
-- Original query using views reads employee table 5 times, child tables twice
SELECT a.EKey, b.Emp_First, b.Emp_Last, c.Address, d.Phone, e.Email
FROM dbo.v_Next_of_Kin_Keys AS a
LEFT OUTER JOIN dbo.Employee AS b
ON a.EKey = b.EKey
LEFT OUTER JOIN dbo.v_Next_of_Kin_Address AS c
ON a.EKey = c.EKey
LEFT OUTER JOIN dbo.v_Next_of_Kin_Phone AS d
ON a.EKey = d.EKey
LEFT OUTER JOIN dbo.v_Next_of_Kin_Email AS e
ON a.EKey = e.EKey
-- Sub in the views to see what the resultant query looks like
SELECT a.EKey, b.Emp_First, b.Emp_Last, c.Address, d.Phone, e.Email
FROM (
SELECT DISTINCT a.EKey
FROM dbo.Employee AS a
LEFT OUTER JOIN dbo.Email AS b
ON a.EKey = b.EKey
LEFT OUTER JOIN dbo.Phone AS c
ON a.EKey = c.EKey
LEFT OUTER JOIN dbo.Address AS d
ON a.EKey = d.EKey
GROUP BY a.EKey
HAVING (SUM(
CASE b.Next_of_Kin WHEN 'TRUE' THEN 1 ELSE 0 END +
CASE c.Next_of_Kin WHEN 'TRUE' THEN 1 ELSE 0 END +
CASE d.Next_of_Kin WHEN 'TRUE' THEN 1 ELSE 0 END
) > 0)
) AS a
LEFT OUTER JOIN dbo.Employee AS b ON a.EKey = b.EKey
LEFT OUTER JOIN (
SELECT a.EKey, b.Address
FROM dbo.Employee AS a
INNER JOIN dbo.Address AS b ON a.EKey = b.EKey
WHERE b.Next_of_Kin = 'true'
) AS c ON a.EKey = c.EKey
LEFT OUTER JOIN (
SELECT a.EKey, b.Phone
FROM dbo.Employee AS a
INNER JOIN dbo.Phone AS b ON a.EKey = b.EKey
WHERE b.Next_of_Kin = 'true'
) AS d ON a.EKey = d.EKey
LEFT OUTER JOIN (
SELECT a.EKey, b.Email
FROM dbo.Employee AS a
INNER JOIN dbo.Email AS b ON a.EKey = b.EKey
WHERE b.Next_of_Kin = 'true'
) AS e ON a.EKey = e.EKey
-- Alternative query reads each table only once
SELECT e.EKey, e.Emp_First, e.Emp_Last, a.Address, p.Phone, m.Email
FROM dbo.Employee e
LEFT OUTER JOIN dbo.Email m
ON m.EKey = e.EKey AND m.Next_of_Kin = 'true'
LEFT OUTER JOIN dbo.Phone p
ON p.EKey = e.EKey AND p.Next_of_Kin = 'true'
LEFT OUTER JOIN dbo.Address a
ON a.EKey = e.EKey AND a.Next_of_Kin = 'true'
WHERE (m.EKey IS NOT NULL OR p.EKey IS NOT NULL OR a.EKey IS NOT NULL)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 13, 2013 at 9:16 am
So this gets around the limitation that normally turns the outer join into an inner join.
LEFT OUTER JOIN dbo.Email m
ON m.EKey = e.EKey AND m.Next_of_Kin = 'true'
Mostly used a query tool against DB2 on an AS400.
Had a GUI that wouldn't allow something like this.
Thanks, learned something new!
December 13, 2013 at 9:59 am
Greg Edwards-268690 (12/13/2013)
So this gets around the limitation that normally turns the outer join into an inner join.LEFT OUTER JOIN dbo.Email m
ON m.EKey = e.EKey AND m.Next_of_Kin = 'true'
Mostly used a query tool against DB2 on an AS400.
Had a GUI that wouldn't allow something like this.
Thanks, learned something new!
Heh anytime! Are you still using DB2? I'm onto it in a couple of weeks...dragging stuff into SQL2k12.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 13, 2013 at 10:53 am
Not working right now - took a voluntary leave back in April. :hehe:
Been plenty busy though with family and things I couldn't get to while working.
Liked DB2 and the AS400. Very stable platform.
We kept our base on there, and replicated to SQL to build cubes and expose through Sharepoint / PerformancePoint / SSRS.
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply