November 6, 2009 at 2:42 am
Hello comunity
I need help on this script, because i need to join 2 derived table. My script is :
select ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5) as 'DocTesouraria',
SUM(valowbincid.Bincid) as 'BincidDocTesouraria', SUM(valorIVA.IVAdoctes)
FROM
(SELECT SUM(evalor) AS 'Bincid' , owstamp FROM owli
where (oldesc like '68%' or oldesc like '129%') GROUP BY owstamp) valowbincid
INNER JOIN ow ON valowbincid.owstamp = ow.owstamp
INNER JOIN (SELECT SUM(evalor) AS 'IVAdoctes' , owstamp FROM owli
where (oldesc like '2430%') GROUP BY owstamp) valorIVA
inner join valowbincid on valowbincid.owstamp = valorIVA.owstamp AND valorIVA.owstamp =ow.owstamp
WHERE OW.DATA>='20090116' AND ow.data<='20090131'
GROUP BY ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5)
if i use only one derived table , like script above everything works fine :
select ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5) as 'DocTesouraria',
SUM(valowbincid.Bincid) as 'BincidDocTesouraria'
FROM
(SELECT SUM(evalor) AS 'Bincid' , owstamp FROM owli
where (oldesc like '68%' or oldesc like '129%') GROUP BY owstamp) valowbincid
INNER JOIN ow ON valowbincid.owstamp = ow.owstamp
WHERE OW.DATA>='20090116' AND ow.data<='20090131'
GROUP BY ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5)
Many thanks and best regards
Luis Santos
November 6, 2009 at 3:01 am
I presume you are getting a syntax error with your 2 derived table script.
It looks as though you have missed out the join criteria where I've highlighted.
SUM(valowbincid.Bincid) as 'BincidDocTesouraria', SUM(valorIVA.IVAdoctes)
FROM
(SELECT SUM(evalor) AS 'Bincid' , owstamp FROM owli
where (oldesc like '68%' or oldesc like '129%') GROUP BY owstamp) valowbincid
INNER JOIN ow ON valowbincid.owstamp = ow.owstamp
INNER JOIN (SELECT SUM(evalor) AS 'IVAdoctes' , owstamp FROM owli
where (oldesc like '2430%') GROUP BY owstamp) valorIVA /* insert join criteria here */
inner join valowbincid on valowbincid.owstamp = valorIVA.owstamp AND valorIVA.owstamp =ow.owstamp
WHERE OW.DATA>='20090116' AND ow.data<='20090131'
GROUP BY ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5)
You've posted this in the SQL2005 forum, so I assume you are using SQL2005.
If so, have a look at CTE's... if nothing else, they'll make the code much easier to read and understand.
November 6, 2009 at 3:08 am
Hello Ian
Could you please build this join to help me, because i don´t know the syntax of the join, join on 2 derived table or not.
Thanks
Luis Santos
November 6, 2009 at 3:23 am
The join syntax using 2 derived tables is no different to any of your other joins, I just think you've missed it out.
Without table definitions, sample data and expected results, this is just a guess
select ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5) as 'DocTesouraria',
SUM(valowbincid.Bincid) as 'BincidDocTesouraria', SUM(valorIVA.IVAdoctes)
FROM
(SELECT SUM(evalor) AS 'Bincid' , owstamp FROM owli
where (oldesc like '68%' or oldesc like '129%') GROUP BY owstamp) valowbincid
INNER JOIN ow ON valowbincid.owstamp = ow.owstamp
INNER JOIN (SELECT SUM(evalor) AS 'IVAdoctes' , owstamp FROM owli
where (oldesc like '2430%') GROUP BY owstamp) valorIVA on valorIVA.owstamp = ow.owstamp
inner join valowbincid on valowbincid.owstamp = valorIVA.owstamp
WHERE OW.DATA>='20090116' AND ow.data<='20090131'
GROUP BY ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5)
Edit:
On second look, I think you'd put the join in the wrong place. Corrected above.
November 6, 2009 at 3:28 am
Luis,
Just a guess really, but:
SELECT OW.data,
LEFT(OW.docnome,3) + ' nº' + RIGHT('00000' + OW.intid, 5) as 'DocTesouraria',
SUM(valowbincid.Bincid) as 'BincidDocTesouraria',
SUM(valorIVA.IVAdoctes)
FROM (
SELECT SUM(evalor) AS 'Bincid',
owstamp
FROM owli
WHERE (
oldesc like '68%'
OR oldesc like '129%'
)
GROUP BY
owstamp
) valowbincid
JOIN OW
ON valowbincid.owstamp = OW.owstamp
JOIN (
SELECT SUM(evalor) AS 'IVAdoctes',
owstamp
FROM owli
WHERE oldesc LIKE '2430%'
GROUP BY
owstamp
) valorIVA
ON valorIVA.owstamp = OW.owstamp -- WAS MISSING
JOIN valowbincid
ON valowbincid.owstamp = valorIVA.owstamp
WHERE OW.DATA>='20090116'
AND OW.data<='20090131'
GROUP BY
OW.data,
LEFT(OW.docnome,3) + ' nº' + RIGHT('00000' + OW.intid, 5);
edit: I made exactly the same 'error' as Ian :blink:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 6, 2009 at 3:31 am
Hello again Ian
I try your script but they return an error :
Msg 208, Level 16, State 1, Line 1
Invalid object name 'valowbincid'.
I change to the following and now they work :
select ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5) as 'DocTesouraria',
SUM(valowbincid.Bincid) as 'BincidDocTesouraria', SUM(valorIVA.IVAdoctes) AS 'ValIVA'
FROM
(SELECT SUM(evalor) AS 'Bincid' , owstamp FROM owli
where (oldesc like '68%' or oldesc like '129%') GROUP BY owstamp) valowbincid
INNER JOIN ow ON valowbincid.owstamp = ow.owstamp
INNER JOIN (SELECT SUM(evalor) AS 'IVAdoctes' , owstamp FROM owli
where (oldesc like '2430%') GROUP BY owstamp) valorIVA on valorIVA.owstamp = ow.owstamp
--inner join valowbincid on valowbincid.owstamp = valorIVA.owstamp AND valorIVA.owstamp =ow.owstamp
WHERE OW.DATA>='20090116' AND ow.data<='20090131'
GROUP BY ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5)
But, know i understand better how i can do this.
Many thanks and good weekend
Luis Santos
November 6, 2009 at 4:42 am
Hello
Thanks Paul and Ian , know i could understand better how can make several joins to derived tables.
Good weekend
Luis Santos
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply