We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
3 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
T-SQL Bogus Rows
Posted on 2013-01-07
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
4 Comments
could you please post the information in the question?
some people are not too excited about downloading xlsx files...
some people are not too excited about downloading xlsx files...
Sure! I will post all four items, as follows:
Here is B.xlsx:
Here is A.xlsx:
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.Refer
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.Origi
GL20000.ORDOCNUM = TRI_view_CashBasis4B.Apply
WHERE GL20000.SOURCDOC = 'PMCHK' AND GL00105.ACTNUMST LIKE '%C'
AND GL20000.DEBITAMT = TRI_view_CashBasis4B.Debit
GL20000.CRDTAMNT = TRI_view_CashBasis4B.Credi
and gl20000.ORMSTRID = 'FOR200' AND
GL20000.ORDOCNUM = '018859'
B.PNG
B.PNG
Here is B.xlsx:
Here is A.xlsx:
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.Refer
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.Origi
GL20000.ORDOCNUM = TRI_view_CashBasis4B.Apply
WHERE GL20000.SOURCDOC = 'PMCHK' AND GL00105.ACTNUMST LIKE '%C'
AND GL20000.DEBITAMT = TRI_view_CashBasis4B.Debit
GL20000.CRDTAMNT = TRI_view_CashBasis4B.Credi
and gl20000.ORMSTRID = 'FOR200' AND
GL20000.ORDOCNUM = '018859'
B.PNG
B.PNG
Disregard. I figured it out. Here is the solution:
The following clause needs to be put in "A":
and SUBSTRING(dbo.TRI_view_Cas
= SUBSTRING(GL00105.ACTNUMST
The following clause needs to be put in "A":
and SUBSTRING(dbo.TRI_view_Cas
= SUBSTRING(GL00105.ACTNUMST
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Hi all,
It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Suggested Courses
Course of the Month3 days, 23 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.