apitech
asked on
T-SQL How to Find the Value in One Field Based on the Same Value in Another
Hello:
I have a table called RM20201. That table contains accounts receivable documents showing payments, debit memos, and credit memos applied to invoices.
I need to extract an invoice number from this table based on the payment number that is applied to that invoice and the payment's corresponding debit memo.
The field for payment (APFRDCNM) is going to be the same value for the invoice and the debit memo.
The field for the invoice and the debit memo is APTODCNM. The field for the "document type" for the invoice is APTODCTY. That value is "1" for the invoice's document type.
Below are the values for the invoice, the debit memo, and the payment, respectively:
INV00031038
DEBIT00000145
PYMNT00027374
Again, both the invoice and the debit memo contain the same payment number of PYMNT00027374.
I have tried to develop a simple "select" statement that will ultimately give me the value "INV00031038". Again, this is the invoice. That's what I want.
I tried doing this in various ways such as with COUNT, HAVING, etc. But, no luck.
Conceivably, the document type that I mentioned can be used in the select statement to pull in the invoice that I need. But, I don't know how to make this all work.
Ideas?
Apitech
I have a table called RM20201. That table contains accounts receivable documents showing payments, debit memos, and credit memos applied to invoices.
I need to extract an invoice number from this table based on the payment number that is applied to that invoice and the payment's corresponding debit memo.
The field for payment (APFRDCNM) is going to be the same value for the invoice and the debit memo.
The field for the invoice and the debit memo is APTODCNM. The field for the "document type" for the invoice is APTODCTY. That value is "1" for the invoice's document type.
Below are the values for the invoice, the debit memo, and the payment, respectively:
INV00031038
DEBIT00000145
PYMNT00027374
Again, both the invoice and the debit memo contain the same payment number of PYMNT00027374.
I have tried to develop a simple "select" statement that will ultimately give me the value "INV00031038". Again, this is the invoice. That's what I want.
I tried doing this in various ways such as with COUNT, HAVING, etc. But, no luck.
Conceivably, the document type that I mentioned can be used in the select statement to pull in the invoice that I need. But, I don't know how to make this all work.
Ideas?
Apitech
do you mean this ?
select i.apfrdcnm
from rm20201 as p
inner join (select * from rm20201
where aptodcty=1)
as i
on p.apfrdcnm=i.apfrdcnm
where p.apfrdcnm-xxxx
if not polease supply and example of the rows in the table (are there 3?)
select i.apfrdcnm
from rm20201 as p
inner join (select * from rm20201
where aptodcty=1)
as i
on p.apfrdcnm=i.apfrdcnm
where p.apfrdcnm-xxxx
if not polease supply and example of the rows in the table (are there 3?)
ASKER
OK. The query should not reference a specific payment number. True, I gave an example of a specific payment number. But, the query needs to be more dynamic that that. The payment number could be anything.
I just want to be able to extract the APTODCNM for an invoice where the APFRDCNM is the same as the APFRDCNM for the debit memo (which is also an APTODCNM).
I'm surprised that there are no articles online that teach how to extract a value based on another vlaue for that same record where the value matches another record.
Apitech
I just want to be able to extract the APTODCNM for an invoice where the APFRDCNM is the same as the APFRDCNM for the debit memo (which is also an APTODCNM).
I'm surprised that there are no articles online that teach how to extract a value based on another vlaue for that same record where the value matches another record.
Apitech
ASKER
Shaun Kline's script works well. But, there is no telling ongoing what the specific payment number is going to be. If that script could be made to be dynamic, that woudl be great! :)
ASKER
I think that I may have it, based on what Shaun gave me. Does this work? It seems to.
select APTODCNM FROM RM20201 where APFRDCNM = RM20201.APFRDCNM and APTODCTY = '1'
ORDER BY APTODCNM
select APTODCNM FROM RM20201 where APFRDCNM = RM20201.APFRDCNM and APTODCTY = '1'
ORDER BY APTODCNM
the articles you want will be talking about joins or self joins for a table.
no
select APTODCNM FROM RM20201 where APFRDCNM = RM20201.APFRDCNM and APTODCTY = '1'
ORDER BY APTODCNM
all the statement is doing is comparing the column to itself,,
your getting invoices purely because of the aptodcty condition.
if you want to relate the references together you need to join the table to itself ,,,
so that you can display the relevant data from the related columns...
select i.aptodcnm,d.aptodcnm
from rm20201 as i
inner join rm20201 as d
on i.apfrdcnm=d.apfrdcnm
where i.aptodcty=1
and d.aptodcty = whatever value indicates a debit....
no
select APTODCNM FROM RM20201 where APFRDCNM = RM20201.APFRDCNM and APTODCTY = '1'
ORDER BY APTODCNM
all the statement is doing is comparing the column to itself,,
your getting invoices purely because of the aptodcty condition.
if you want to relate the references together you need to join the table to itself ,,,
so that you can display the relevant data from the related columns...
select i.aptodcnm,d.aptodcnm
from rm20201 as i
inner join rm20201 as d
on i.apfrdcnm=d.apfrdcnm
where i.aptodcty=1
and d.aptodcty = whatever value indicates a debit....
ASKER
I'm afraid that I'm still not getting anywhere. Let me give some background on what I'm trying to accomplish.
The table RM20201 is one where you can have a debit memo "attached" to an invoice (sort of). And, both documents are in the same field--APTODCNM, I need that invoice number "in" a big query that I have developed as I need for the query to grab a field called ME50405.USERDEF1. In my query that I have pasted below, I have called this field "PropertyManagement".
PropertyManagement is not attached to debit memos--only invoices. My query, irconically, reports on just debit memos but I need the field PropertyManagement to display and, again, this field only comes from the debit memo's corresponding invoice.
The only way that I know to pull the invoice number, so that I can get PropertyManagement, is to tie it to the only field that it and the debit memo have in common--the payment number field. If there were another way of doing it, I would simply do it.
So, I want to embed the query that I'm asking for here on this forum into my big query so that I can get my debit memos plus the PropertyManagement field.
My query is and always has been successfully pulling the debit memos. But, I cannot get this blame PropertyManagement field because the field is only attached to invoices.
I placed Lowfatspread's query into my query. But, it is returning 0 results. Again, my query without that "subquery" or whatever in the world it's called does return results--it just does not display the PropertyManagement field.
Helpl!!
select
CASE WHEN OAKVA..GL20000.SOURCDOC IN ('PMTRX', 'GJ')
THEN CONVERT(VARCHAR(20), OAKVA..GL20000.JRNENTRY)
WHEN OAKVA..GL20000.SOURCDOC = 'SJ' THEN
OAKVA..GL20000.ORCTRNUM ELSE '' END as [DocumentNumber],
OAKVA..RM20101.DOCDATE as [DocumentDate],
OAKVA..GL20000.TRXDATE as [PostDate],
LEFT(OAKVA..RM20101.CUSTNM
OAKVA..RM20101.CUSTNMBR as [ClientNumber],
OAKVA..RM00101.CUSTNAME as [ClientName],
CASE WHEN LEFT(OAKVA..GL20000.ORCTRN
WHEN LEFT(OAKVA..GL20000.ORCTRN
ELSE OAKVA..GL20000.SOURCDOC END as [DocumentType],
COALESCE(OAKVA..RM20101.SL
COALESCE(OAKVA..RM20101.TA
COALESCE(OAKVA..RM20101.OR
OAKVA..GL20000.ORTRXSRC as [Source],
OAKVA..GL00105.ACTNUMST as [PostingAccount],
RIGHT(rtrim(OAKVA..GL00105
OAKVA..GL00100.ACTDESCR as [Description],
OAKVA..ME50405.USERDEF1 as [PropertyManagement],
OAKVA..GL20000.DEBITAMT as [Debit],
OAKVA..GL20000.CRDTAMNT as [Credit],
OAKVA..GL20000.CRDTAMNT - OAKVA..GL20000.DEBITAMT as [Total2]
from OAKVA..GL20000
INNER JOIN OAKVA..RM20101 on OAKVA..GL20000.ORCTRNUM = OAKVA..RM20101.DOCNUMBR
INNER JOIN OAKVA..RM20201 on OAKVA..RM20201.APTODCNM = OAKVA..RM20101.DOCNUMBR
INNER JOIN OAKVA..SOP30200 on OAKVA..RM20201.APTODCNM = OAKVA..SOP30200.SOPNUMBE
INNER JOIN OAKVA..RM00101 on OAKVA..RM00101.CUSTNMBR = OAKVA..RM20101.CUSTNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..ME50411 on OAKVA..SOP30200.SOPNUMBE = OAKVA..ME50411.DOCNUMBR
INNER JOIN OAKVA..ME50405 on OAKVA..ME50411.ME_Contract
where OAKVA..RM20101.RMDTYPAL = '3'
and SUBSTRING(OAKVA..GL00105.A
and EXISTS (select i.aptodcnm,d.aptodcnm
from rm20201 as i
inner join rm20201 as d
on i.apfrdcnm=d.apfrdcnm
where i.aptodcty=1
and d.aptodcty = 3)
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' and '2011-08-31 00:00:00.000'
The table RM20201 is one where you can have a debit memo "attached" to an invoice (sort of). And, both documents are in the same field--APTODCNM, I need that invoice number "in" a big query that I have developed as I need for the query to grab a field called ME50405.USERDEF1. In my query that I have pasted below, I have called this field "PropertyManagement".
PropertyManagement is not attached to debit memos--only invoices. My query, irconically, reports on just debit memos but I need the field PropertyManagement to display and, again, this field only comes from the debit memo's corresponding invoice.
The only way that I know to pull the invoice number, so that I can get PropertyManagement, is to tie it to the only field that it and the debit memo have in common--the payment number field. If there were another way of doing it, I would simply do it.
So, I want to embed the query that I'm asking for here on this forum into my big query so that I can get my debit memos plus the PropertyManagement field.
My query is and always has been successfully pulling the debit memos. But, I cannot get this blame PropertyManagement field because the field is only attached to invoices.
I placed Lowfatspread's query into my query. But, it is returning 0 results. Again, my query without that "subquery" or whatever in the world it's called does return results--it just does not display the PropertyManagement field.
Helpl!!
select
CASE WHEN OAKVA..GL20000.SOURCDOC IN ('PMTRX', 'GJ')
THEN CONVERT(VARCHAR(20), OAKVA..GL20000.JRNENTRY)
WHEN OAKVA..GL20000.SOURCDOC = 'SJ' THEN
OAKVA..GL20000.ORCTRNUM ELSE '' END as [DocumentNumber],
OAKVA..RM20101.DOCDATE as [DocumentDate],
OAKVA..GL20000.TRXDATE as [PostDate],
LEFT(OAKVA..RM20101.CUSTNM
OAKVA..RM20101.CUSTNMBR as [ClientNumber],
OAKVA..RM00101.CUSTNAME as [ClientName],
CASE WHEN LEFT(OAKVA..GL20000.ORCTRN
WHEN LEFT(OAKVA..GL20000.ORCTRN
ELSE OAKVA..GL20000.SOURCDOC END as [DocumentType],
COALESCE(OAKVA..RM20101.SL
COALESCE(OAKVA..RM20101.TA
COALESCE(OAKVA..RM20101.OR
OAKVA..GL20000.ORTRXSRC as [Source],
OAKVA..GL00105.ACTNUMST as [PostingAccount],
RIGHT(rtrim(OAKVA..GL00105
OAKVA..GL00100.ACTDESCR as [Description],
OAKVA..ME50405.USERDEF1 as [PropertyManagement],
OAKVA..GL20000.DEBITAMT as [Debit],
OAKVA..GL20000.CRDTAMNT as [Credit],
OAKVA..GL20000.CRDTAMNT - OAKVA..GL20000.DEBITAMT as [Total2]
from OAKVA..GL20000
INNER JOIN OAKVA..RM20101 on OAKVA..GL20000.ORCTRNUM = OAKVA..RM20101.DOCNUMBR
INNER JOIN OAKVA..RM20201 on OAKVA..RM20201.APTODCNM = OAKVA..RM20101.DOCNUMBR
INNER JOIN OAKVA..SOP30200 on OAKVA..RM20201.APTODCNM = OAKVA..SOP30200.SOPNUMBE
INNER JOIN OAKVA..RM00101 on OAKVA..RM00101.CUSTNMBR = OAKVA..RM20101.CUSTNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..ME50411 on OAKVA..SOP30200.SOPNUMBE = OAKVA..ME50411.DOCNUMBR
INNER JOIN OAKVA..ME50405 on OAKVA..ME50411.ME_Contract
where OAKVA..RM20101.RMDTYPAL = '3'
and SUBSTRING(OAKVA..GL00105.A
and EXISTS (select i.aptodcnm,d.aptodcnm
from rm20201 as i
inner join rm20201 as d
on i.apfrdcnm=d.apfrdcnm
where i.aptodcty=1
and d.aptodcty = 3)
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' and '2011-08-31 00:00:00.000'
The script I provided would be for use in a stored procedure. Removing the specific payment number would all the script to be used in a view or straight SQL. The addition of aptodcty=1 was missed when I first posted.
The script was specific to bringing back the invoice value where another record had the same payment number. If you want to pull back both the invoice and the debit memo record, then you need to perform a self-join as Lowfatspread indicated above. It all depends on what you are trying to return from you SQL.
The script was specific to bringing back the invoice value where another record had the same payment number. If you want to pull back both the invoice and the debit memo record, then you need to perform a self-join as Lowfatspread indicated above. It all depends on what you are trying to return from you SQL.
SELECT APTODCNM FROM RM20201 WHERE APFRDCNM = <Payment #> AND EXISTS (SELECT 1 FROM RM20201 as A WHERE APFRDCNM = RM20201.APFRDCNM and APTODCTY <> 1) and APTODCTY = 1ASKER
Guys:
If it helps, I have attached a screenshot of what I am trying to do. Please offer some direction. I'm on a time crunch, on this.
Thanks!
Apitech
Easy-Peesy.docx
If it helps, I have attached a screenshot of what I am trying to do. Please offer some direction. I'm on a time crunch, on this.
Thanks!
Apitech
Easy-Peesy.docx
i didn't say anything about exists...
more like this
more like this
select
CASE WHEN OAKVA..GL20000.SOURCDOC IN ('PMTRX', 'GJ')
THEN CONVERT(VARCHAR(20), OAKVA..GL20000.JRNENTRY)
WHEN OAKVA..GL20000.SOURCDOC = 'SJ'
then OAKVA..GL20000.ORCTRNUM
ELSE ''
END as [DocumentNumber],
I.APTODCNM as [InvoiceNumber],
OAKVA..RM20101.DOCDATE as [DocumentDate],
OAKVA..GL20000.TRXDATE as [PostDate],
LEFT(OAKVA..RM20101.CUSTNMBR, 3) as [ClientCode],
OAKVA..RM20101.CUSTNMBR as [ClientNumber],
OAKVA..RM00101.CUSTNAME as [ClientName],
CASE LEFT(OAKVA..GL20000.ORCTRNUM, 5)
WHEN 'CREDT' THEN 'Credit Memo'
WHEN 'DEBIT' THEN 'Debit Memo'
ELSE OAKVA..GL20000.SOURCDOC
END as [DocumentType],
COALESCE(OAKVA..RM20101.SLSAMNT, 0) as [Sales],
COALESCE(OAKVA..RM20101.TAXAMNT, 0) as [Tax],
COALESCE(OAKVA..RM20101.ORTRXAMT, 0) as [Total1],
OAKVA..GL20000.ORTRXSRC as [Source],
OAKVA..GL00105.ACTNUMST as [PostingAccount],
RIGHT(rtrim(OAKVA..GL00105.ACTNUMST), 6) as [NaturalAccount],
OAKVA..GL00100.ACTDESCR as [Description],
OAKVA..ME50405.USERDEF1 as [PropertyManagement],
OAKVA..GL20000.DEBITAMT as [Debit],
OAKVA..GL20000.CRDTAMNT as [Credit],
OAKVA..GL20000.CRDTAMNT - OAKVA..GL20000.DEBITAMT as [Total2]
from OAKVA..GL20000
INNER JOIN OAKVA..RM20101 on OAKVA..GL20000.ORCTRNUM = OAKVA..RM20101.DOCNUMBR
INNER JOIN (select * from OAKVA..RM20201
where aptodcty = 3
) as d
on D.APTODCNM = OAKVA..RM20101.DOCNUMBR
Left Outer JOIN (select * from OAKVA..RM20201
where aptodcty = 1
) as i
on i.apfrdcnm=d.apfrdcnm
INNER JOIN OAKVA..SOP30200 on D.APTODCNM = OAKVA..SOP30200.SOPNUMBE
INNER JOIN OAKVA..RM00101 on OAKVA..RM00101.CUSTNMBR = OAKVA..RM20101.CUSTNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..ME50411 on OAKVA..SOP30200.SOPNUMBE = OAKVA..ME50411.DOCNUMBR
INNER JOIN OAKVA..ME50405 on OAKVA..ME50411.ME_Contract_Number = OAKVA..ME50405.ME_Contract_Number
where OAKVA..RM20101.RMDTYPAL = '3'
and SUBSTRING(OAKVA..GL00105.ACTNUMST, CHARINDEX('-', ACTNUMST) + 1, 1) = '4'
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000'
-- missing last day ? time component should be '23:59:59.997' 00:00 is start of day
and '2011-08-31 00:00:00.000'ASKER
No results were returned. I should have had two records (debit memos) returned.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was it!!!!
Thank you so much, Lowfatspread!!!!! You're awesome!
John
Thank you so much, Lowfatspread!!!!! You're awesome!
John
ok not sure if the left joins are necessary or in the right places , but it dhouldn't make any difference to the actual results.
you need to gain a fuller understanding of what relational database processing is about and how you navigate the various types of relationship ...
it may help you to consider the rm20201 table as a union (set) of the three distinct datatypes debit,payment,invoice
so try accessing the table via subqueries specifying each individual type sp that you can join the data...
you need to gain a fuller understanding of what relational database processing is about and how you navigate the various types of relationship ...
it may help you to consider the rm20201 table as a union (set) of the three distinct datatypes debit,payment,invoice
so try accessing the table via subqueries specifying each individual type sp that you can join the data...
Open in new window