Avatar of Zack
ZackFlag for Australia

asked on 

SQL Code Duplicate Vouchers in Query

Hi EE,

I have the following query:

 SELECT    a.ACCOUNTNUM, CAST(a.TRANSDATE AS date) AS transdate, a.VOUCHER, a.INVOICE, a.CURRENCYCODE, a.TXT,
  CAST(a.DUEDATE AS date) AS duedate, a.AMOUNTCUR, a.SETTLEAMOUNTCUR, a.SETTLEAMOUNTMST, a.SETTLEAMOUNTMST AS ORIGSETTELEMST, a.AMOUNTMST
 , a.LASTSETTLEVOUCHER, f.INVOICEID AS lastsettleinvoice, a.TRANSTYPE
 , EXCHADJUSTMENT
 , CASE WHEN A.AMOUNTMST < 0 THEN A.AMOUNTMST ELSE 0 END AS MSTCREDIT
 , CASE WHEN A.AMOUNTCUR < 0 THEN A.AMOUNTCUR ELSE 0 END AS CURCREDIT  
 , CASE WHEN A.AMOUNTMST > 0 THEN A.AMOUNTMST ELSE 0 END AS MSTDEBET
 , CASE WHEN A.AMOUNTCUR > 0 THEN A.AMOUNTCUR ELSE 0 END AS CURDEBET
 , A.AMOUNTMST - A.SETTLEAMOUNTMST + A.EXCHADJUSTMENT  Balance
FROM         VENDTRANS AS a LEFT OUTER JOIN
                      VENDTABLE AS b ON a.ACCOUNTNUM = b.ACCOUNTNUM LEFT OUTER JOIN
                      --DIRPARTYTABLE AS c ON b.PARTY = c.RECID LEFT OUTER JOIN
                     -- VENDTABLE AS d ON a.ACCOUNTNUM = d.ACCOUNTNUM LEFT OUTER JOIN
                      VENDINVOICEJOUR AS f ON a.LASTSETTLEVOUCHER = f.LEDGERVOUCHER AND f.INVOICEACCOUNT = a.ACCOUNTNUM AND f.DATAAREAID = a.DATAAREAID AND f.INVOICEACCOUNT= CONVERT(VARCHAR, a.transdate, 103)
ORDER BY a.ACCOUNTNUM, a.TRANSDATE

Open in new window


The issue that I keep getting is that the same voucher number is duplicated multiple times.
How would I go about troubleshooting this, any assistance is welcome.

Thank you
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Zack
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

The issue that I keep getting is that the same voucher number is duplicated multiple times. 

I would think some of the conditions are not being joined there. Are you missing some join condition there?

to quickly resolve this, try add the Distinct clause.

such as:

SELECT DISTINCT   a.ACCOUNTNUM, CAST(a.TRANSDATE AS date) AS transdate, a.VOUCHER, a.INVOICE, a.CURRENCYCODE, a.TXT,....

Open in new window


If it's still "duplicate", then it would probably the data issue.
Avatar of Zack
Zack
Flag of Australia image

ASKER

Hi Ryan,

Ok adding DISTINCT:


 SELECT  DISTINCT a.ACCOUNTNUM, CAST(a.TRANSDATE AS date) AS transdate, a.VOUCHER, a.INVOICE, a.CURRENCYCODE, a.TXT,
  CAST(a.DUEDATE AS date) AS duedate, a.AMOUNTCUR, a.SETTLEAMOUNTCUR, a.SETTLEAMOUNTMST, a.SETTLEAMOUNTMST AS ORIGSETTELEMST, a.AMOUNTMST
 , a.LASTSETTLEVOUCHER, f.INVOICEID AS lastsettleinvoice, a.TRANSTYPE
 , EXCHADJUSTMENT
 , CASE WHEN A.AMOUNTMST < 0 THEN A.AMOUNTMST ELSE 0 END AS MSTCREDIT
 , CASE WHEN A.AMOUNTCUR < 0 THEN A.AMOUNTCUR ELSE 0 END AS CURCREDIT  
 , CASE WHEN A.AMOUNTMST > 0 THEN A.AMOUNTMST ELSE 0 END AS MSTDEBET
 , CASE WHEN A.AMOUNTCUR > 0 THEN A.AMOUNTCUR ELSE 0 END AS CURDEBET
 , A.AMOUNTMST - A.SETTLEAMOUNTMST + A.EXCHADJUSTMENT  Balance
FROM         VENDTRANS AS a LEFT OUTER JOIN
                      VENDTABLE AS b ON a.ACCOUNTNUM = b.ACCOUNTNUM LEFT OUTER JOIN
                      --DIRPARTYTABLE AS c ON b.PARTY = c.RECID LEFT OUTER JOIN
                     -- VENDTABLE AS d ON a.ACCOUNTNUM = d.ACCOUNTNUM LEFT OUTER JOIN
                      VENDINVOICEJOUR AS f ON a.VOUCHER = f.LEDGERVOUCHER AND f.INVOICEACCOUNT = a.ACCOUNTNUM AND f.DATAAREAID = a.DATAAREAID AND f.INVOICEACCOUNT= CONVERT(VARCHAR, a.transdate, 103)
ORDER BY a.ACCOUNTNUM, a.TRANSDATE, a.VOUCHER, a.INVOICE, a.CURRENCYCODE, a.TXT,
  a.DUEDATE, a.AMOUNTCUR, a.SETTLEAMOUNTCUR, a.SETTLEAMOUNTMST, a.AMOUNTMST
 , a.LASTSETTLEVOUCHER, f.INVOICEID , a.TRANSTYPE

Open in new window


I get the error Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

The order is correct from I can tell, please advise.

Thank you. 

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Zack
Zack
Flag of Australia image

ASKER

Hi Ryan,

Cheers for that I'm pretty sure it's  data issue there are multiple columns where the transdate is NULL

Thank you.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo