0

First thanks for reading me and sorry for my bad english.

I have the following code on sql server 2008 r2

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

Output:

PERAA PERMM NUMLI RMDESC RMUMSR RECURV RMMIS2 RMWGHT RMNETW CONCA Cont_x_CjPz PesoNt_x_WhPz PesoLg_WhLPz PesoNt_x_WhCj PesoLg_WhLCj PESOPZNT PESOPZBR Pz_Emb Cj_Emb UMFR Piezas PL Cajas -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2010 1 40 MAYONESA LIMONES SQUEEZE 12 OZ CJ 405300 12 4.460000 3.840000 12.000000 12.0000000000000000000000 0.3200000000000000000000 0.3716660000000000000000 3.8400000000000000000000 4.4599920000000000000000 0.320000000000 0.371666666666 112476.000000 9373.000000 CJ 12.000000 130.000000 130.000000 2010 1 40 MAYONESA LIMONES SQUEEZE 12 OZ CJ 405300 12 4.460000 3.840000 12.000000 12.0000000000000000000000 0.3200000000000000000000 0.3716660000000000000000 3.8400000000000000000000 4.4599920000000000000000 0.320000000000 0.371666666666 112476.000000 9373.000000 CJ 12.000000 130.000000 12.000000

This is a view, the UMCONV table is a conversion of measures that goes from palets to boxes, to pieces.

Now my problem is that CJ (Boxes) go to both PZ (Pieces) and PL (Palets) thus making me a duplicate entry that bring "trash data".

 SELECT [UMFR] ,[UMTO] ,[UMRESR] ,[UMWHSE] ,[UMLOCN] ,[UMVNNO] ,[UMSEQN] ,[UMCONF] ,[UMCALC] ,[UMQYF1] ,[UMQYT1] ,[UMQYF2] ,[UMQYT2] ,[UMQYF3] ,[UMQYT3] ,[UMQYF4] ,[UMQYT4] ,[UMQYF5] ,[UMQYT5] ,[UMDTMT] ,[UMBLNK] FROM [LOGISTICA].[dbo].[UMCONV] 

 UMFR UMTO UMRESR UMWHSE UMLOCN UMVNNO UMSEQN UMCONF UMCALC UMQYF1 UMQYT1 UMQYF2 UMQYT2 UMQYF3 UMQYT3 UMQYF4 UMQYT4 UMQYF5 UMQYT5 UMDTMT UMBLNK PL CJ 405300 1 130.000000 M 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 1020627 CJ PL 405300 1 130.000000 D 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 1020627 PZ CJ 405300 1 12.000000 D 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 1041016 CJ PZ 405300 1 12.000000 M 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 1041016

Any idea of how to solve it? so far I think force it to convert and do a rock paper scissors

Any other idea?

This is the

Improve this question
5
  • So the View is intended to have Rows that contain all the item's info from FA960 and then its conversion factors from Pieces to Boxes and to Palets, which are all contained within UMCONV? Commented Oct 25, 2010 at 19:06
  • Correct, but is having a duplicate register always, while this doesnt affect the data at all... its ugly for the administration department. Commented Oct 25, 2010 at 19:18
  • 1
    Please don't use tabs to separate data that you post to SO - really makes a mess of the formatting. Commented Oct 25, 2010 at 19:18
  • @OMG Ponies I am sorry whats the correct way to put a table on SO? So I dont make the mistake again in the future. Commented Oct 25, 2010 at 20:35
  • Data separated by tabs -- \t causes grief when formatting on SO. If possible, please replace them before posting to SO. Commented Oct 25, 2010 at 20:39

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Edited to correct the sub query and added COALESCE to return 1 for the conversion factor column that is equal to De.
p You where faster than me, I am sorry what does the COALESCE does and they 1?
@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.
1

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.

Improve this answer

5 Comments

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: 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.
@Enrique: Address a comment to me, like how I am to you, when you're finished editing.
Done, I added some result, and the conversion table.
@OMG Ponies: He meant include the @ part so it'll show up in his inbox.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.