Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

view results showing duplicates

Posted on 2013-06-18
11
Medium Priority
?
173 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
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

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!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

877 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