Solved

view results showing duplicates

Posted on 2013-06-18
11
158 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 48

Accepted Solution

by:
PortletPaul earned 500 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 48

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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 48

Expert Comment

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

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 48

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 48

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

777 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