January 31, 2004 at 3:33 pm
Hello
I have a table of invoicing that keeps in the lines the invoices and the credit notes
When we make a credit note, the table keeps the invoice nr on which the credit note is made (field:fi.fnoft)
My purpose is to have the value os the field "Ecusto" of the credit note and also the value of the field "Ecusto" of the invoice from wich the credit note was made in order to compare values.
I have the following TSQL expression , but this one is returned always to me with only the value of the field "Ecusto" of the credit note.
My expression is :
select fi.fno as 'nr.NotaDevol', fi.fnoft, fi.ndocft, ref, epv, qtt, fi.ecusto, fi.epcp, (ft.ecusto/fi.qtt) as 'EcustoFT_Unitario', dif=round(fi.ecusto*fi.qtt,2), (select fii.ecusto from fi fii where fii.fnoft=fi.fnoft and fii.ref=fi.ref) as factura from fi inner join ft on fi.ftstamp=ft.ftstamp
where fi.ecusto<>fi.epcp and ref <>' ' and stns<> 1 and month(ft.fdata)=4 and ft.ndoc=8 AND FI.FNOFT <>0
the field ft.ndoc=8 that means credit note
I hope someone could help me
Best regards
Luis
February 1, 2004 at 2:15 pm
Your say the credit note shows the original invoice - only one condition ....
"the table keeps the invoice nr on which the credit note is made (field:fi.fnoft)"
Your select has two conditions ....
(select fii.ecusto from fi fii where fii.fnoft=fi.fnoft and fii.ref=fi.ref) as factura
Are you sure that "ref" is the same on both invoice and the credit note?
Also, to avoid any abiguity, I would code as follows (but I don't think it will make any difference)
select fi1.fno as 'nr.NotaDevol',
fi1.fnoft, … etc
(select fi2.ecusto from fi fi2
where fi2.fnoft=fi1.fnoft and fi2.ref=fi1.ref) as factura
from fi fi1inner join ft on fi1.ftstamp=ft.ftstamp
where fi1.ecusto<>fi1.epcp … etc
February 2, 2004 at 4:46 am
Thanks for your help
in your question :
Are you sure that "ref" is the same on both invoice and the credit note
Yes , i´m sure that is why i make this 2 conditions
(select fii.ecusto from fi fii where fii.fnoft=fi.fnoft and fii.ref=fi.ref) as factura
because if in this select i don´t use fii.ref=fi.ref my TSQL return me nothing.
Best regards
Luis Santos
February 2, 2004 at 1:20 pm
It seems to me that you would be better off to use a self join rather than the subselect. Since I don't know your schema this is a little hard but here is what I came up with.
select fi.fno as 'nr.NotaDevol'
, fi.fnoft
, fi.ndocft
, fi.ref
, fi.epv
, fi.qtt
, fi.ecusto
, fi.epcp
, EcustoFT_Unitario = (ft.ecusto/fi.qtt)
, dif = round(fi.ecusto*fi.qtt,2)
, factura = fi2.ecusto
from fi
inner join ft on fi.ftstamp = ft.ftstamp
inner join fi fi2 ON fi2.fnoft = fi.fnoft and fi2.ref = fi.ref
where fi.ecusto != fi.epcp
and fi.ref != ' '
and fi.stns != 1
and month(ft.fdata)=4
and ft.ndoc = 8
AND fi.FNOFT != 0
Please note that I used the WHERE clause on your subselect to do the join for the self join. I don't think this looks correct though as it doesn't seem to be the PK for the fi table since you allow empty strings in the ref field.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply