ansaurus

Question

SQL duplicate data for excel dynamic table

Answer 1

+1 A:

Have you tried using DISTINCT:

SELECT DISTINCT
       FA960.*,
       UMCONVPZ.UMFR,
       UMCONVPZ.UMCONF AS Piezas,
       UMCONVPL.UMCONF AS PL, 
       UMCONVCJ.UMCONF AS Cajas
  FROM FA960 FA960
  JOIN UMCONV UMCONVPZ ON UMCONVPZ.UMRESR = FA960.RECURV 
                      AND UMCONVPZ.UMFR = FA960.RMUMSR
                      AND UMCONVPZ.UMTO = 'PZ' 
  JOIN UMCONV UMCONVPL ON UMCONVPL.UMRESR = FA960.RECURV 
                      AND UMCONVPL.UMTO = 'PL'
  JOIN UMCONV UMCONVCJ ON UMCONVCJ.UMRESR = FA960.RECURV
                      AND UMCONVCJ.UMTO = 'CJ'

It's tough to say without knowing more about the data.

OMG Ponies 2010-10-25 18:31:00

I just try your solution and still bring a second register, in which is trying to do the CJ - to PL and other with CJ to PZ

Enrique 2010-10-25 18:35:19

@Enrique: Then there are other fields that have different values - I'm skeptical that the "CJ" relationship is your only issue. We need to know more detail about your data to help you solve the issue.

OMG Ponies 2010-10-25 18:37:56

Give me a second I would edit

Enrique 2010-10-25 18:40:50

@Enrique: Address a comment to me, like how I am to you, when you're finished editing.

OMG Ponies 2010-10-25 18:44:41

Done, I added some result, and the conversion table.

Enrique 2010-10-25 18:48:04

@OMG Ponies: He meant include the @ part so it'll show up in his inbox.

Sorax 2010-10-25 18:54:51

@OMG Ponies: sorry done

Enrique 2010-10-25 18:59:05

Answer 2

+2 A:

How does this work for you:

SELECT DISTINCT f.*,
       f.RMUMSR as De,
       COALESCE((SELECT UMCONF FROM UMCONV WHERE UMRESR = f.RECURV AND UMFR = f.RMUMSR AND UMTO = 'PZ'), 1) AS Piezas,
       COALESCE((SELECT UMCONF FROM UMCONV WHERE UMRESR = f.RECURV AND UMFR = f.RMUMSR AND UMTO = 'PL'), 1) AS Palet,
       COALESCE((SELECT UMCONF FROM UMCONV WHERE UMRESR = f.RECURV AND UMFR = f.RMUMSR AND UMTO = 'CJ'), 1) AS Cajas
FROM FA960 f

Sorax 2010-10-25 19:58:50

Edited to correct the sub query and added COALESCE to return 1 for the conversion factor column that is equal to De.

Sorax 2010-10-25 20:31:21

@Sorax :p You where faster than me, I am sorry what does the COALESCE does and they 1?

Enrique 2010-10-25 20:32:10

@Enrique: Haha Yeah I just realized I wrote the subqueries incorrectly. Without the additional filter on the UMFR column it will return more than one item. COALESCE is a SQL Server fucntion that returns the first argument that is not NULL. In this case, I added it so the conversion factor to the identical unit would be 1. I figured that would be most logical, but you can replace 1 if another value is more appropriate.

Sorax 2010-10-25 20:40:21

ansaurus

tags:

views:

answers:

SQL duplicate data for excel dynamic table

related questions