0

I´ve to create a SELECT over many tables. The output has to be specified as it is (output via SQLCMD in a file)

The problem is, that there are too many lines in the output.

the code (from MS SQL Server Management Express):

SELECT DISTINCT 
                      AORDER.cOrderId AS 'order-id', APOS.cOrderItemId AS 'order-item-id', 'CustomerReturn' AS 'adjustment-reason-code', AORDER.cCurrency AS currency, 
                      CONVERT(DECIMAL(16, 2), ROUND(GSPOS.fVKPreis * GSPOS.nAnzahl, 2)) AS 'item-price-adj', CONVERT(DECIMAL(16, 2), 
                      CASE WHEN GSPOS.nAnzahl = APOS.nQuantityPurchased THEN ROUND(APOS.fShippingPrice, 2) ELSE '0.00' END) AS 'shipping-price-adj', 
                      GSPOS.nAnzahl AS quantity
FROM         tgutschriftpos AS GSPOS INNER JOIN
                      tgutschrift AS GS ON GSPOS.tGutschrift_kGutschrift = GS.kGutschrift INNER JOIN
                      tbestellung AS B ON GS.kRechnung = B.tRechnung_kRechnung INNER JOIN
                      pf_amazon_bestellung AS AORDER ON B.cInetBestellNr = AORDER.cOrderId INNER JOIN
                      pf_amazon_bestellungpos AS APOS ON AORDER.kAmazonBestellung = APOS.kAmazonBestellung AND GSPOS.cArtNr = APOS.cArtNr
WHERE     (APOS.cOrderItemId IS NOT NULL) AND (GS.cStatus IS NULL) AND (NOT (GSPOS.tArtikel_kArtikel = 0)) AND (NOT (GSPOS.cArtNr IS NULL))
GROUP BY GSPOS.kGutschriftPos, AORDER.cOrderId, GSPOS.nAnzahl, GSPOS.fVKPreis, APOS.cOrderItemId, AORDER.cCurrency, APOS.nQuantityPurchased, 
                      APOS.fShippingPrice

The output as it is:

123456789   12345   CustomerReturn  EUR 14,95   0,00    1,0000
123456789   12345   CustomerReturn  EUR 29,90   3,87    2,0000
123456789   6789    CustomerReturn  EUR 14,95   1,93    1,0000
123456789   6789    CustomerReturn  EUR 29,90   0,00    2,0000

The output as it should be:

123456789   12345   CustomerReturn  EUR 14,95   0,00    1,0000
123456789   6789    CustomerReturn  EUR 14,95   1,93    1,0000

I hope someone can help me?

Thanks,

Rene

carexcer
  • 1,407
  • 2
  • 15
  • 27
albossum
  • 1
  • It looks like you are joining to multiple lines with one of your table joins. First, you will need to identify which table is causing the duplicates...I use trial and error for it, comment out one of your tables (and the columns from that table in your select line), then run your script. Keep doing this until you only have the two rows you think you should have. Once you've identified which table they are coming from, you can then build logic into your script to handle them. – Twelfth Jan 13 '14 at 18:13
  • Thanks!!! The problem might be the table GSPOS. If I eliminate the fields from that table from the SELECT, I get the two desired lines - but without the desired values from that table, of course. How can I modify my query to solve this problem? I tried for 3 hours, asking Google & Co., but I can´t get it... :( P.S. I also tried to swap the table GSPOS and GS and tried "INNER JOIN (SELECT ... FROM tgutschriftpos ) AS GSPOS ON ... – albossum Jan 13 '14 at 18:22
  • This is really up to your business needs. You need to figure out to join both tables to get only one record of the GSPOS per GS. Maybe you need to add another column to the join, maybe you need to get the min GSPOS by something... that can be a business rule rather than a technical problem. – saamorim Jan 13 '14 at 18:28
  • @saamorim What do you mean with "Maybe you need to add another column to the join, maybe you need to get the min GSPOS by something." Can you explain or give an example? – albossum Jan 13 '14 at 18:38
  • I agree with @saamorim... if there is a 1-to-many relationship, how do you properly know which data you want? Looking at your example, how do you know you want the data for 14,95 euro and not 29,90 euro? – Mike M Jan 13 '14 at 18:52
  • You can change a table to a select statement and make that select statement only pick up the one row you want. select * from (select * from table) as GSPOS. You will need valid logic to reduce that to one row though – Twelfth Jan 13 '14 at 21:54

0 Answers0