Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

view results showing duplicates

Posted on 2013-06-18
11
Medium Priority
?
169 Views
Last Modified: 2013-08-15
Hello I have written a view which has to show two diffrent display flags and if the hierarchy on this view is a rate flag
i have created 3 union statements, whilst I am getting all the results in my test database I am missing a condition. to stop the duplicate results based on the two display flags. view is attached.
union-view.txt
0
Comment
Question by:Amanda Walshaw
[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
  • 6
  • 5
11 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39258456
it seems to me you don't need 4 subqueries (with union in between)

the FROM clauses appear to be identical
but the WHERE clauses differ
I'd suggest revising it into a single (compound) where clause
in summary:

where ( all of the first where clause )
or  ( all of the 2nd where clause )
or  ( all of the 3rd where clause )
or  ( all of the 4th where clause )

some elements are common and can be simplified further - but that's how I would go about it
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258668
I would collapse all those queries together as I can find no differences in the from clauses. Thsi will also place all the where conditions together which should be easier to maintain - as well as performing better overall.
SELECT
      a.Orderno AS [order No]
    , b.CaseType AS [Case Type]
    , CASE WHEN b.rate = - 1 THEN 'True' ELSE 'False' END AS [rate of Service]
    , a.Branch_No AS [Branch No]
    , a.Branch_phone AS [Branch Phone]
    , b.salesperson AS [SalesName Name]
    , b.crewid AS [crew id]
    , b.Tradingname AS [Trading Name]
    , c.Address1 AS [Address Line 1]
    , c.Address2 AS [Address Line 2]
    , c.Salutation AS [Salutation]
    , c.CustomerFirstname AS [Customer Firstname]
    , b.displayflag1 AS [Flag1]
    , b.displayflag2 AS [flag2]
    , a.order_id a.order_id AS order_id
    , b.position AS Rack
FROM
ORDER a
INNER JOIN sales b ON a.Order_ID = b.Order_ID
INNER JOIN Storelocation c ON a.store_ID = c.store_ID
INNER JOIN customer d ON a.order_ID = d.Order_ID
LEFT JOIN customer_Role e ON d.role_ID = e.role_ID
INNER JOIN customerDetails f1 ON d.detail_ID = f1.deail_ID
INNER JOIN CustomerDetails f ON a.detail_ID = f.detail_ID
WHERE ( b.CaseType IN ('Metals', 'Black steals', 'Sundry', 'TBA', 'Variation Order')
        AND (
            b.orderDate >= dbo.basedate(getdate())
            OR b.deliverdateDate >= dbo.basedate(getdate())
            )
        ) 
OR /* #2 */
    ( 
     e.ROLE = 'my contact'
        AND (
            b.ServiceDate >= dbo.basedate(getdate())
            OR b.CommittalDate >= dbo.basedate(getdate())
            )
        AND (
            b.displayflag1 = 1
            AND displayflag2 = 1
            )
        OR (
            b.displayflag1 = 1
            AND displayflag2 = 0
            )
        AND b.CaseType IN ( 'Consignment' , 'Double Booking' , 'Superior Sale' )        
     ) 
OR /* #3 */
    (
     e.ROLE = 'my contact'
        AND (
            b.ServiceDate >= dbo.basedate(getdate())
            OR b.CommittalDate >= dbo.basedate(getdate())
            )
        AND (
            b.displayflag1 = 1
            AND displayflag2 = 1
            )
        OR (
            b.displayflag1 = 1
            AND displayfalg2 = 0
            )
        AND b.CaseType IN ( 'Consignment' , 'Double Booking' , 'Superior Sale' )        
    ) 
OR /* # 4 */
    (
     e.ROLE = 'my contact'
        AND (
            b.ServiceDate >= dbo.basedate(getdate())
            OR b.CommittalDate >= dbo.basedate(getdate())
            )
        AND (
            b.displayflag1 = 0
            AND displayflag2 = 1
            )
        AND b.CaseType IN ( 'Consignment' , 'Double Booking' , 'Superior Sale' )        
    )

Open in new window

I have not tried to simplify further. Please indicate if this helps with this issue as it is - before trying for simplification.
0
 

Author Comment

by:Amanda Walshaw
ID: 39258681
ahh now the light has gone on I will try this.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Amanda Walshaw
ID: 39258689
but what about the fields I have to null out. that is why I needed the union when the case type is true I need to NULL out all the fields but it needs to be displayed.
When Displayflag2 is 1 only I need to null out the contact details.

only when you have this condition
displayflag1 =1 and displayflag2 =1 or displayflag1 = 1 and displayflag2 = 0
can I show everything... contact role is related to displayflag1.
0
 

Author Comment

by:Amanda Walshaw
ID: 39258690
sorry correction the rateflag is the hierarchy flag not the case type.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258694
Is this for a report? What is the reporting tool?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258695
anyway, try the combine query please
0
 

Author Comment

by:Amanda Walshaw
ID: 39258720
Appolgies its been a long day, here is the corrected view I cannot send the real data but thread is the same, the third union I have to null out the fields when display2 is selected as you don't want to see the customer contact details as well I have corrected the date fields.
union-view.txt
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39258824
Do you understand the difference between UNION and UNION ALL?

You have 4 subqueries, each have identical FROM clauses.
There are differences in the SELECT clauses
and whilst there are 4 different WHERE clauses 3 of them are quite similar

However, due to the differences introduced to the rows with the selection lists - you will not achieve the desired "simplification" using UNION

just a tiny difference in rows is considered unique by UNION (or DISTINCT)

I believe you are trying to replicate or produce a "report" - SQL is not a report writer.

I cannot "picture" what you are trying to do exactly - what are your "expected results"
Q-28161257.xlsx
0
 

Author Comment

by:Amanda Walshaw
ID: 39259114
I will come back to  you on this I was asked to combine two views into one, I had disagreed as I knew I would come across this issue of duplication.
they are extract scripts from sql to be displayed on websites.
but I am using dummy data to on exchange to explain the problem.
I cannot use company data, but I will consult with a sql developer and see what they say.
thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39259135
you should not expect SQL to do this form of manipulation - it should be performed within the programmic logic layer - all you will end-up with is a quite a mess

truly - this is not a good idea in SQL

does the site use PHP? .Net? - it can (and should) be done there
- this manipulation of data for presentation is NOT an sql task
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

704 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