Problem with a TSQL statment with Subquery

Question

Problem with a TSQL statment with Subquery

luissantos

SSCertifiable

Points: 7391
More actions
May 2, 2005 at 6:45 pm

#65809

Hello
I have the following TSQL query :
select 'DocGestao'=ft.fno,
'ValorGestao'=round(sum(fi.ecusto*fi.qtt),2) ,adoc as DocCTB,
'Valor322'= (select (case when sum(edeb)=0 then sum(ecre) else sum(-edeb) end)
from ml ml1 (nolock)inner join
ft ft1 (nolock) on
ltrim(rtrim(ml1.adoc))=LTRIM(RTRIM(STR(ft1.fno)))and ft1.ndoc=1 and ml1.doctipo=1 and
(LTRIM(RTRIM(STR(ml1.conta))) = '322' or LTRIM(RTRIM(STR(ml1.conta))) = '329') and
ft1.fdata between '20040101' and '20041231' and ml1.data between '20040101' and '20041231'
where ft.ftstamp=ft1.ftstamp)
from ft (nolock)
inner join ml (nolock)on LTRIM(RTRIM(STR(ft.fno)))=ml.adoc
inner join fi (nolock)on ft.ftstamp=fi.ftstamp
where
(LTRIM(RTRIM(STR(ml.conta))) = '322' or LTRIM(RTRIM(STR(ml.conta))) = '329') and
ltrim(rtrim(ml.adoc))=LTRIM(RTRIM(STR(ft.fno))) and ft.ndoc=1 and ml.doctipo=1 and
ft.fdata between '20040101' and '20041231' and ml.data between '20040101' and '20041231'
and fi.stns=0 and ft.fno=6096
group by ml.adoc,ft.fno,ft.ndoc,ft.ftstamp
order by ft.fno asc
my first question is if i do in my subquery 'Valor322' put all the WHERE clauses write in the outer query.
second : this query return to me the cost in my invoice nr. 6096 in my invoice program and compare it with the cost in my accounting program for the same type of document and number, but curiousely the cost of my invoice in my invooice program is the double compare to the cost in my accounting software. Why ?
here is the result :
docGestao ValorGestao docCTB Valor322
6096 5109,02000 6096 2554,51000
where
docgestao is the number invoice of my invoice program
Valorgestao is the cost in my invoice program
docctb is the umber of my invoice in the accouting program
valor322 is the cost in the accounting program
I don´t understand why, maybe the problem is in my subselect
because i write TSQL in my invoice program to see the cost of my invoice 6096 :
select sum(fi.qtt*fi.ecusto) from fi inner join ft on fi.ftstamp=ft.ftstamp
where ft.fdata between '20040101' and '20041231' and fi.fno=6096
the return value is : 2554,51000
Best regards
Luis Santos

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

As you suspect, I too am guessing that your query has some flaw and seems to calculate twice the correct value.

If you could supply example of the table(s) involved and some rows with data that your query can run against, and an explanation of the desired output, we might be able to help.

/Kenneth

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

how about :

select ft.fno as DocGestao,

,round(sum(fi.ecusto*fi.qtt),2) as ValorGestao

,ml.adoc as DocCTB

-- ,(select (case when sum(edeb)=0 then sum(ecre) else sum(-edeb) end)

-- from ml ml1 (nolock)

-- inner join ft ft1 (nolock)

-- on ltrim(rtrim(ml1.adoc))=LTRIM(RTRIM(STR(ft1.fno)))

-- and ft1.ndoc=1

-- and ml1.doctipo=1

-- and (LTRIM(RTRIM(STR(ml1.conta))) = '322'

-- or LTRIM(RTRIM(STR(ml1.conta))) = '329')

-- and ft1.fdata between '20040101' and '20041231'

-- and ml1.data between '20040101' and '20041231'

-- where ft.ftstamp=ft1.ftstamp)

-- as Valor322

, sum (case when NestSel.sum_edeb = 0 then NestSel.sum_ecre else NestSel.sum_edeb end) as Valor322

from ft (nolock)

inner join ml (nolock)

on LTRIM(RTRIM(STR(ft.fno)))=ml.adoc

inner join fi (nolock)

on ft.ftstamp=fi.ftstamp

left join (select ltrim(rtrim(ml1.adoc)) as adoc_fno

, ft1.ftstamp as ftstamp

, sum(-edeb) as sum_edeb, sum(ecre) as sum_ecre

from ml ml1 (nolock)

inner join ft ft1 (nolock)

on ltrim(rtrim(ml1.adoc))=LTRIM(RTRIM(STR(ft1.fno)))

and ft1.ndoc=1

and ml1.doctipo=1

and (LTRIM(RTRIM(STR(ml1.conta))) = '322'

or LTRIM(RTRIM(STR(ml1.conta))) = '329')

and ft1.fdata between '20040101' and '20041231'

and ml1.data between '20040101' and '20041231'

group by ltrim(rtrim(ml1.adoc))

, ft1.ftstamp

&nbsp as NestSel

on ltrim(rtrim(ml.adoc)) = NestSel.adoc_fno

and ft.ftstamp = NestSel.ftstamp

where (LTRIM(RTRIM(STR(ml.conta))) = '322'

or LTRIM(RTRIM(STR(ml.conta))) = '329')

and ltrim(rtrim(ml.adoc))=LTRIM(RTRIM(STR(ft.fno)))

and ft.ndoc=1

and ml.doctipo=1

and ft.fdata between '20040101' and '20041231'

and ml.data between '20040101' and '20041231'

and fi.stns=0

and ft.fno=6096

group by ml.adoc,ft.fno,ft.ndoc,ft.ftstamp

order by ft.fno asc

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Thanks yours suggestions i will try them.

For Kenneth Wilhelmsson who has asked me for more information, i wil send them:

I'm explain the structure of all the tables:

FT - is the header of my invoice

FI - is the lines of my invoice

the fields who are equal in FT and FI is ndoc, ftstamp

ndoc is invoice number

ftstamp is the index header field .The same one is present for the same invoice on each lines

for ex:

invoice nº (ft.ndoc) 100 and ft.ftstamp= ADM10000

line invoice and those articles :

article 1 quant(fi.qtt) = 1 unit sail(fi.epv) is 100 cost(fi.ecusto) of this article is 50

then 1 x 100 = 100 cost (fi.ecusto) is (fi.ecusto * fi.ecusto)

article 2 quant(fi.qtt) =1 unit sail(fi.epv) is 150 cost(fi.ecusto) of this article is 75

then 1 x 150 = 150 cost (fi.ecusto) is (fi.ecusto * fi.ecusto)

the contents of fields in FI who are the same is fi.ftstamp = ADM10000 for these 2 lines

fi.ndoc are the same in this 2 lines = 100 , but i need an inner join to FT to obtain the

date of my invoice

the accountancy movement in the table ML is :

712------------------Credit ----250

2433112--------------Credit----- 47,5 (tax. 19%)

2110001--Debt------297,5

321------debt------50

329------debt------75

612------------------credit-----125

the unique fields that i have in my accountancy lines movement equal to my invoice is :

date of invoice (ft.fdata)=(ml.data)

nº invoice str(ft.fno)= (ml.adoc)

they exist other fields, for ex. ml.oristamp is equal to ft.ftstamp, but for example if i write in my accontancy program one line with the

account 321 or 329, these fields loose there relation with my invoice document because when i´am

integrate my invoice , i have a predefine accounts for it.

then the cost of my invoice is sum(fi.qtt*fi.ecusto) = 125 but in my accountancy program i have

the same value in separate account , 321 and 329.

that´s why i need in the subselect all the where clauses in the outer select.

I don´t know if this code could be build more efficiently.

I hope you understand my explanation and many thanks for your interest.

Best regards

Luis Santos

luissantos SSCertifiable Points: 7391 More actions · Answer 4

Hello Alzdba

Thanks for your help, but it didn´t work well because the values return on fields:

ValorGestao:5109,02

Valor322 :40872.160000

the correct values for both is 2554.5100

Also with this modification on your code i can put the correct value in ValorGestao but not in Valor322

I ´ll send you the modifications in your code :

select ft.fno as DocGestao,

(select round(sum(fi.ecusto*fi.qtt),2)from fi where fi.ftstamp=ft.ftstamp) as ValorGestao ,ml.adoc as DocCTB

/*-- ,(select (case when sum(edeb)=0 then sum(ecre) else sum(-edeb) end)

-- from ml ml1 (nolock)

-- inner join ft ft1 (nolock)

-- on ltrim(rtrim(ml1.adoc))=LTRIM(RTRIM(STR(ft1.fno)))

-- and ft1.ndoc=1

-- and ml1.doctipo=1

-- and (LTRIM(RTRIM(STR(ml1.conta))) = '322'

-- or LTRIM(RTRIM(STR(ml1.conta))) = '329')

-- and ft1.fdata between '20040101' and '20041231'

-- and ml1.data between '20040101' and '20041231'

-- where ft.ftstamp=ft1.ftstamp)

-- as Valor322*/

, sum (case when NestSel.sum_edeb = 0 then NestSel.sum_ecre else NestSel.sum_edeb end) as Valor322

from ft (nolock)

inner join ml (nolock)on LTRIM(RTRIM(STR(ft.fno)))=ml.adoc

inner join fi (nolock)on ft.ftstamp=fi.ftstamp

left join

(select ltrim(rtrim(ml1.adoc)) as adoc_fno, ft1.ftstamp as ftstamp, sum(-edeb) as sum_edeb, sum(ecre) as sum_ecre

from ml ml1 (nolock)

inner join ft ft1 (nolock)

on ltrim(rtrim(ml1.adoc))=LTRIM(RTRIM(STR(ft1.fno)))

and ft1.ndoc=1

and ml1.doctipo=1

and (LTRIM(RTRIM(STR(ml1.conta))) = '322'

or LTRIM(RTRIM(STR(ml1.conta))) = '329')

and ft1.fdata between '20040101' and '20041231'

and ml1.data between '20040101' and '20041231'

group by ltrim(rtrim(ml1.adoc))

, ft1.ftstamp)as NestSel

on ltrim(rtrim(ml.adoc)) = NestSel.adoc_fno

and ft.ftstamp = NestSel.ftstamp

where (LTRIM(RTRIM(STR(ml.conta))) = '322'

or LTRIM(RTRIM(STR(ml.conta))) = '329')

and ltrim(rtrim(ml.adoc))=LTRIM(RTRIM(STR(ft.fno)))

and ft.ndoc=1

and ml.doctipo=1

and ft.fdata between '20040101' and '20041231'

and ml.data between '20040101' and '20041231'

and fi.stns=0

and ft.fno=6096

group by ml.adoc,ft.fno,ft.ndoc,ft.ftstamp

order by ft.fno asc

the RED color is my modification on your coding

Thanks again and i wait your reply

Luis Santos

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Bookkeeping is a strange thing and I never really understood it... but one thing I remember: most bookings are in pairs. That could be the reason of doubling 2554.51 to 5109.02.

Try this for the beginning, whether it helps:

select 'DocGestao'=ft.fno,

'ValorGestao'=round(sum(fi.ecusto*fi.qtt),2) ,

'Valor322'= (select (case when sum(edeb)=0 then sum(ecre) else sum(-edeb) end)

from ml ml1 (nolock)

inner join ft ft1 (nolock) on ltrim(rtrim(ml1.adoc))=LTRIM(RTRIM(STR(ft1.fno)))and ft1.ndoc=1 and ml1.doctipo=1 and

(LTRIM(RTRIM(STR(ml1.conta))) = '322' or LTRIM(RTRIM(STR(ml1.conta))) = '329') and

ft1.fdata between '20040101' and '20041231' and ml1.data between '20040101' and '20041231'

where ft.ftstamp=ft1.ftstamp)

from ft (nolock)

inner join fi (nolock)on ft.ftstamp=fi.ftstamp

where

fi.stns=0 and ft.fno=6096

group by ft.fno

I have erased all the direct links to bookkeeping program, so that data from table "ml" are being read only in the subquery. Let's see whether this will give correct result, and then we can consider how to optimize the query.

luissantos SSCertifiable Points: 7391 More actions · Answer 6

Hello Vladan

i test the code , and i think is run more quickly, but i need to put on the group by clause "FT.FTSTAMP".

Also, the query is not totally perfect because its return two lines like follow :

DocGestao ValorGestao Valor322

1º Line 6096 0.0000000 .NULL.

2º Line 6096 2554.51000 2554.51000

Why , it´s curious ?. Maybe the problem is in the joins conditions.

I don´t understand what you mean in "Bookkeeping", my english is not so rich. Could you give me an example or explain in other way.

I wait your comments and i thank you for your help

Luis Santos

Vladan SSC-Insane Points: 22072 More actions · Answer 7

Hi Luis, Bookkeeping means the same as Accounting... well, maybe there are some differences, but I don't speak English good enough to know them.

It would really help a lot if you post DDL of the 3 tables (CREATE TABLE statements for ml, ft and fi) and a few lines of sample data (in form "INSERT INTO tbl1 (col1, col2, col3) VALUES (1, 'ABC', 77.45)").. at least data for the invoice 6096. Then we can test the query and find the problem.

Vladan SSC-Insane Points: 22072 More actions · Answer 8

... oh, and one question.. What is result of this :

SELECT count(*) FROM ft WHERE fno=6096

SELECT count(*) FROM ml WHERE adoc=6096

I supposed that adoc may occur twice, but fno is unique - if there are several rows with the same value in ft.fno, then this could be where the query fails and multiplies the values.

BTW, you could also try to add - to the query I posted before - these conditions into WHERE clause:

ft.ndoc=1 AND ft.fdata between '20040101' and '20041231'

luissantos SSCertifiable Points: 7391 More actions · Answer 9

Hello Vladan

I try to your frst query and i join in the WHERE conditions (ft.ndoc=1 AND ft.fdata between '20040101' and '20041231') and also i need to put in the GROUP BY ft.ftstamp .

This TSQL command work ok :

select 'DocGestao'=ft.fno,

'ValorGestao'=round(sum(fi.ecusto*fi.qtt),2) ,

'Valor322'= (select (case when sum(edeb)=0 then sum(ecre) else sum(-edeb) end)

from ml ml1 (nolock)

inner join ft ft1 (nolock) on ltrim(rtrim(ml1.adoc))=LTRIM(RTRIM(STR(ft1.fno)))and ft1.ndoc=1 and ml1.doctipo=1 and

(LTRIM(RTRIM(STR(ml1.conta))) = '322' or LTRIM(RTRIM(STR(ml1.conta))) = '329') and

ft1.fdata between '20040101' and '20041231' and ml1.data between '20040101' and '20041231'

where ft.ftstamp=ft1.ftstamp)

from ft (nolock)

inner join fi (nolock)on ft.ftstamp=fi.ftstamp

where

fi.stns=0 and ft.fno=6096 and ft.ndoc=1 AND ft.fdata between '20040101' and '20041231'

group by ft.fno,ft.ftstamp

Comments:

SELECT count(*) FROM ft WHERE fno=6096

SELECT count(*) FROM ml WHERE adoc=6096

in table FT i can have the same ft.fno 6096 but for different ft.ndoc.

ex. invoice 6096 ft.ndoc=1 and ml.doctipo=1

credit note 6096 ft.ndoc=3 and ml.doctipo=3

following this example:

For my invoice :

in ML table for each line of invoice nº 6096 , ML.ADOC= 6096 but also ml.doctipo=1

For my Credit note :

in ML table for each line of Credit note nº 6096 , ML.ADOC= 6096 but ml.doctipo=3

Ok, this a small explanation of how fields on invoice program are related on accountancy program.

Many thanks for your kindly helps

Luis Santos