Solved

T-SQL Bogus Rows

Posted on 2013-01-07
4
415 Views
Last Modified: 2013-01-13
The programming within the attached Notepad file B is a view called Tri_view_CashBasis4B.  This view is contained within the programming in the view in Notepad file A.

Attached are the results of running each in the SQL Studio.  "A" is supposed to be the final result.  Rows 4 and 6 in A, though, are wrong.  The results of the two views are to correspond based on the third string of the account number field in each.  And, A should only have the debit amounts in B.

How do I fix the linking or whatever in A so that it pulls the correct information from B?
B.txt
A.txt
B.xlsx
A.xlsx
0
Comment
Question by:TBSupport
  • 3
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38754711
could you please post the information in the question?
some people are not too excited about downloading xlsx files...
0
 
LVL 1

Author Comment

by:TBSupport
ID: 38755913
Sure!  I will post all four items, as follows:

Here is B.xlsx:
Here is the image of A.xlsx (Hope this helps)

Here is A.xlsx:
Here is the image of B.xlsx (Hope this helps)
Here is B.txt:
SELECT DISTINCT
                      dbo.GL20000.TRXDATE AS GL_Date, dbo.GL00105.ACTNUMST AS Account_Number, dbo.GL20000.DEBITAMT AS Debit,
                      dbo.GL20000.CRDTAMNT AS Credit, dbo.GL20000.ORMSTRID AS Originating_Master_ID, dbo.PM30600.VCHRNMBR AS Originating_Master_No,
                      dbo.GL20000.ORDOCNUM AS Originating_Doc_No, dbo.PM30200.DOCNUMBR + dbo.PM30200.TRXDSCRN AS Reference,
                      dbo.PM30300.APFRDCNM AS Apply_From_Doc_No, dbo.PM30200.DOCAMNT AS Applied_Amount, dbo.PM30200.DOCNUMBR AS REFERENCE1
FROM         dbo.PM30200 LEFT OUTER JOIN
                      dbo.PM30300 ON dbo.PM30200.VENDORID = dbo.PM30300.VENDORID AND dbo.PM30200.DOCNUMBR = dbo.PM30300.APTODCNM LEFT OUTER JOIN
                      dbo.PM30600 ON dbo.PM30600.VCHRNMBR = dbo.PM30300.APTVCHNM AND dbo.PM30600.VENDORID = dbo.PM30300.VENDORID LEFT OUTER JOIN
                      dbo.GL20000 ON dbo.PM30600.VENDORID = dbo.GL20000.ORMSTRID AND dbo.PM30600.VCHRNMBR = dbo.GL20000.ORCTRNUM AND
                      dbo.PM30600.PSTGDATE = dbo.GL20000.TRXDATE LEFT OUTER JOIN
                      dbo.GL00105 ON dbo.GL20000.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                      dbo.GL00100 ON dbo.GL00105.ACTINDX = dbo.GL00100.ACTINDX
WHERE     (dbo.PM30200.DOCTYPE = '1')
AND GL20000.ORMSTRID = 'FOR200'
AND PM30300.APFRDCNM = '018859'


Here is A.txt:
SELECT DISTINCT
GL20000.TRXDATE AS [GL_Date], GL00105.ACTNUMST AS [Account_Number],
GL20000.DEBITAMT AS [Debit], GL20000.CRDTAMNT AS [Credit],
GL20000.ORMSTRID AS [Originating_Master_ID],
PM30600.VCHRNMBR AS [Originating_Master_No],
GL20000.ORDOCNUM AS [Originating_Doc_No],
TRI_view_CashBasis4B.Reference AS [Reference],
GL20000.ORDOCNUM AS [Apply_From_Doc_No], PM30200.DOCAMNT AS [Applied_Amount]
FROM  GL20000
LEFT OUTER JOIN
PM30600 ON PM30600.VENDORID = GL20000.[ORMSTRID] AND PM30600.VCHRNMBR = GL20000.ORCTRNUM AND
PM30600.PSTGDATE = GL20000.TRXDATE
LEFT OUTER JOIN
PM30300 ON PM30600.VCHRNMBR = PM30300.APTVCHNM AND PM30600.VENDORID = PM30300.VENDORID
LEFT OUTER JOIN
PM30200 ON GL20000.ORMSTRID = PM30200.[VENDORID] AND GL20000.ORDOCNUM = PM30200.[DOCNUMBR]
LEFT OUTER JOIN
GL00105 ON GL20000.[ACTINDX] = GL00105.[ACTINDX] INNER JOIN
GL00100 ON GL00105.[ACTINDX] = GL00100.[ACTINDX]
INNER JOIN
TRI_view_CashBasis4B ON GL20000.ORMSTRID = TRI_view_CashBasis4B.Originating_Master_ID AND
GL20000.ORDOCNUM = TRI_view_CashBasis4B.Apply_From_Doc_No
WHERE     GL20000.SOURCDOC = 'PMCHK' AND GL00105.ACTNUMST LIKE '%C'
AND GL20000.DEBITAMT = TRI_view_CashBasis4B.Debit AND
GL20000.CRDTAMNT = TRI_view_CashBasis4B.Credit
and gl20000.ORMSTRID = 'FOR200' AND
GL20000.ORDOCNUM = '018859'
B.PNG
B.PNG
0
 
LVL 1

Accepted Solution

by:
TBSupport earned 0 total points
ID: 38756393
Disregard.  I figured it out.  Here is the solution:

The following clause needs to be put in "A":

and SUBSTRING(dbo.TRI_view_CashBasis4B.Account_Number, 11, 5)
= SUBSTRING(GL00105.ACTNUMST, 11, 5)
0
 
LVL 1

Author Closing Comment

by:TBSupport
ID: 38771616
I simply needed to determine how to configure "substring" to tie the general ledger account numbers together.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

19 Experts available now in Live!

Get 1:1 Help Now