Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

multiple detail joins

7 posts in General Discussion Last posting was on 2007-08-28 15:09:54.0Z
Geoffrey Chambers Posted on 2007-08-23 23:40:15.0Z
Sender: 1de.46c5eab4.1804289383@sybase.com
From: Geoffrey Chambers
Newsgroups: ianywhere.public.general
Subject: multiple detail joins
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <46ce1adf.5ab0.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Aug 2007 16:40:15 -0700
X-Trace: forums-1-dub 1187912415 10.22.241.41 (23 Aug 2007 16:40:15 -0700)
X-Original-Trace: 23 Aug 2007 16:40:15 -0700, 10.22.241.41
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6231
Article PK: 2637

I have one table that is a list of ost codes for a budget
program, there exists two other detail tables one is a cost
adjustment detail table and the other is a change order
detail table. I tried to create two left outer joins, but,
just as I thought, the second one is summing a value based
on the first join. I have an adjustment of $13,408 but the
Change Order table has 59 records so the $13,408 is
totaling $791,072. How can I acomplish this, I'm fairly new
to SQL. Here what I tried.

ALTER VIEW
"DBA"."JCSpecwithCE"(cSpec,cDesc,nOwner,nSub,cJob,cSubjob,CostCode
,cType,cVendorid,nContractNo,nOco,nJca,nRowner,nRsub)
AS Select
t1.spec_no,
t1.description,
t1.owner,
(if t1.subs is null then 0 else t1.subs endif),
t1.job,
t1.subjob,
t1.costcode,
t1.costtype,
t1.vendorid,
t1.contract_no,
sum(if t2.toowner is null then 0 else t2.toowner endif) as
oco,
sum(if t3.amount is null then 0 else t3.amount endif) as
jca,
t1.owner+oco+jca,
t1.subs+sum(if t2.tosub is null then 0 else t2.tosub endif )
from Specification AS T1
left outer join CostEventDetail AS T2 on T1.job = T2.job and
T1.subjob = T2.subjob and T1.costcode = T2.costcode and
T1.costtype = T2.costtype and T1.spec_no = T2.spec_no
left outer join jcadjust AS T3 on T1.job = T3.job and
T1.subjob = T3.subjob and T1.costcode = T3.costcode and
T1.costtype = T3.costtype and T1.spec_no = T3.spec_no
group by
t1.spec_no,t1.description,t1.owner,t1.subs,t1.job,t1.subjob,t1.costcode
,t1.costtype,t1.vendorid,t1.contract_no
ORDER BY t1.costcode, t1.costtype,t1.spec_no


"Frank Ploessel" <fpl... Posted on 2007-08-27 19:13:54.0Z
Subject: Re: multiple detail joins
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <46ce1adf.5ab0.1681692777@sybase.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.txqfpfk6j0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: mail.taskarena-software-engineering.net
X-Original-NNTP-Posting-Host: mail.taskarena-software-engineering.net
Date: 27 Aug 2007 12:13:54 -0700
X-Trace: forums-1-dub 1188242034 217.76.101.130 (27 Aug 2007 12:13:54 -0700)
X-Original-Trace: 27 Aug 2007 12:13:54 -0700, mail.taskarena-software-engineering.net
Lines: 67
X-Authenticated-User: sa_beta
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6237
Article PK: 2642

Geoffrey,

What exactly is it that you want to have? What should be summend and what
not?

By the way: You can probably leave out the if expressions from within the
sum, as aggregation functions such as sum ignore null values in SQL, in
contrast to most other operations where nulls spread out.
Anyway, it does not hurt to do so, and in some situations might be
necessary, as when you only are summing nulls. In this case, the sum will
be null, and the sum then to something else will spread the null again.

Frank

On Fri, 24 Aug 2007 01:40:15 +0200, Geoffrey <Chambers> wrote:

> I have one table that is a list of ost codes for a budget
> program, there exists two other detail tables one is a cost
> adjustment detail table and the other is a change order
> detail table. I tried to create two left outer joins, but,
> just as I thought, the second one is summing a value based
> on the first join. I have an adjustment of $13,408 but the
> Change Order table has 59 records so the $13,408 is
> totaling $791,072. How can I acomplish this, I'm fairly new
> to SQL. Here what I tried.
>
> ALTER VIEW
> "DBA"."JCSpecwithCE"(cSpec,cDesc,nOwner,nSub,cJob,cSubjob,CostCode
> ,cType,cVendorid,nContractNo,nOco,nJca,nRowner,nRsub)
> AS Select
> t1.spec_no,
> t1.description,
> t1.owner,
> (if t1.subs is null then 0 else t1.subs endif),
> t1.job,
> t1.subjob,
> t1.costcode,
> t1.costtype,
> t1.vendorid,
> t1.contract_no,
> sum(if t2.toowner is null then 0 else t2.toowner endif) as
> oco,
> sum(if t3.amount is null then 0 else t3.amount endif) as
> jca,
> t1.owner+oco+jca,
> t1.subs+sum(if t2.tosub is null then 0 else t2.tosub endif )
> from Specification AS T1
> left outer join CostEventDetail AS T2 on T1.job = T2.job and
> T1.subjob = T2.subjob and T1.costcode = T2.costcode and
> T1.costtype = T2.costtype and T1.spec_no = T2.spec_no
> left outer join jcadjust AS T3 on T1.job = T3.job and
> T1.subjob = T3.subjob and T1.costcode = T3.costcode and
> T1.costtype = T3.costtype and T1.spec_no = T3.spec_no
> group by
> t1.spec_no,t1.description,t1.owner,t1.subs,t1.job,t1.subjob,t1.costcode
> ,t1.costtype,t1.vendorid,t1.contract_no
> ORDER BY t1.costcode, t1.costtype,t1.spec_no


Glenn Paulley Posted on 2007-08-27 19:29:16.0Z
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: multiple detail joins
References: <46ce1adf.5ab0.1681692777@sybase.com>
In-Reply-To: <46ce1adf.5ab0.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: paulley-d620.sybase.com
X-Original-NNTP-Posting-Host: paulley-d620.sybase.com
Message-ID: <46d3260c$1@forums-1-dub>
Date: 27 Aug 2007 12:29:16 -0700
X-Trace: forums-1-dub 1188242956 10.25.99.239 (27 Aug 2007 12:29:16 -0700)
X-Original-Trace: 27 Aug 2007 12:29:16 -0700, paulley-d620.sybase.com
Lines: 80
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6239
Article PK: 4667

I *think* what you want to do, based on the description you've provided,
it so left outer join Specification and CostEventDetail, and left outer
join that result to a derived table that does the SUM() over
jcadjust.amount independently. The idea is

SELECT ....
from Specification AS T1
left outer join CostEventDetail AS T2 on T1.job = T2.job and
T1.subjob = T2.subjob and T1.costcode = T2.costcode and
T1.costtype = T2.costtype and T1.spec_no = T2.spec_noFROM

left outer join

(Select job, subjob, costcode, costtype, ....,
sum(if amount is null then 0 else amount endif) as jca,
From jcadjust
group by job, subjob, costcode, costtype, .... ) as T3

on T1.job = T3.job .....

Glenn

Geoffrey Chambers wrote:
> I have one table that is a list of ost codes for a budget
> program, there exists two other detail tables one is a cost
> adjustment detail table and the other is a change order
> detail table. I tried to create two left outer joins, but,
> just as I thought, the second one is summing a value based
> on the first join. I have an adjustment of $13,408 but the
> Change Order table has 59 records so the $13,408 is
> totaling $791,072. How can I acomplish this, I'm fairly new
> to SQL. Here what I tried.
>
> ALTER VIEW
> "DBA"."JCSpecwithCE"(cSpec,cDesc,nOwner,nSub,cJob,cSubjob,CostCode
> ,cType,cVendorid,nContractNo,nOco,nJca,nRowner,nRsub)
> AS Select
> t1.spec_no,
> t1.description,
> t1.owner,
> (if t1.subs is null then 0 else t1.subs endif),
> t1.job,
> t1.subjob,
> t1.costcode,
> t1.costtype,
> t1.vendorid,
> t1.contract_no,
> sum(if t2.toowner is null then 0 else t2.toowner endif) as
> oco,
> sum(if t3.amount is null then 0 else t3.amount endif) as
> jca,
> t1.owner+oco+jca,
> t1.subs+sum(if t2.tosub is null then 0 else t2.tosub endif )
> from Specification AS T1
> left outer join CostEventDetail AS T2 on T1.job = T2.job and
> T1.subjob = T2.subjob and T1.costcode = T2.costcode and
> T1.costtype = T2.costtype and T1.spec_no = T2.spec_no
> left outer join jcadjust AS T3 on T1.job = T3.job and
> T1.subjob = T3.subjob and T1.costcode = T3.costcode and
> T1.costtype = T3.costtype and T1.spec_no = T3.spec_no
> group by
> t1.spec_no,t1.description,t1.owner,t1.subs,t1.job,t1.subjob,t1.costcode
> ,t1.costtype,t1.vendorid,t1.contract_no
> ORDER BY t1.costcode, t1.costtype,t1.spec_no

--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer