Solved

view results showing duplicates

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

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 37
SQL Transaction logs 8 11
Increasing Identity length in sql server 4 20
Update in Sql 7 12
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now