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.

how to handle <no rows returned> from a derived table in T-SQL

6 posts in General Discussion Last posting was on 2010-02-01 10:08:36.0Z
Mihir Hardas Posted on 2010-01-28 07:15:10.0Z
Sender: 568.4b613789.1804289383@sybase.com
From: Mihir Hardas
Newsgroups: sybase.public.ase.general
Subject: how to handle <no rows returned> from a derived table in T-SQL
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b61397e.5b8.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4b61397e"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 27 Jan 2010 23:15:10 -0800
X-Trace: forums-1-dub 1264662910 10.22.241.41 (27 Jan 2010 23:15:10 -0800)
X-Original-Trace: 27 Jan 2010 23:15:10 -0800, 10.22.241.41
Lines: 179
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28909
Article PK: 78150

Hi All:

I am trying to generate a resutset which computes certain
financial values based on aggregation and then these values
are presented horizontally one after the another in columns.

The basic skeleton of the SQL Query is thus:


SELECT

-

-

-

FROM

(

-- This derived table gets the avg of
(Assets_Liabilities_Confirmed)

SELECT avg(ReportField4) as Assets_Liabilities_Confirmed

WHERE ...

AND l.ReportField1="Confirmed by MSFS"

group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName

) derived_table_1

,



(

-- This derived table gets the avg (Provided by 3rd Party)

SELECT avg(ReportField4) as Provided by 3rd Party

WHERE ...

AND l.ReportField1="third party"

group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName

) derived_table_2



WHERE (1=1)
AND derived_table_1.month=derived_table_2.month
AND
derived_table_1.advisorLegalName=derived_table_2.advisorLegalName
AND
derived_table_1.fundLegalName=derived_table_2.fundLegalName





The issue is: <<suppose derived_table_2>> returns no rows
which means, that there are no records for <<Provided by 3rd
party>> the join conditions
derived_table_1.XYZ=derived_table_2.XYZ fails and the entire

SQL Query resultset becomes <no rows returmed>.



So essentially, i am looking at handling the no rows
returned from derived_table_2 such that the other resutset
is not hampered i.e. derived_table_1 in this case.



Any pathways to solve this problem would be appreciated.


Kind regards,

~Mihir

select
dt_box.year
,dt_box.month
,dt_box.advisorLegalName
,dt_box.fundLegalName

,dt_S2.Assets_Liabilities_Confirmed
, dt_S3.Provided_by_Fund_Manager

FROM


(
select
datename(year, l.periodStartDate) year
,datename(month, l.periodStartDate) month
,a.advisorLegalName as advisorLegalName
,f.fundLegalName as fundLegalName
FROM

ODSAggregatesSet1 l
,FundSections fs
,Sections s
,fsid..Fund f
,fsid..Advisor a

WHERE (1=1)
AND s.sectionKey = fs.sectionKey
AND fs.fundId= l.fundId
AND s.sectionKey = l.sectionKey
AND f.fundId=fs.fundId
AND f.advisorKey=a.advisorKey

AND l.periodStartDate between "01-Sep-2009" AND "31-Dec-2009"
AND l.baseCurrency = "USD"
AND effectiveStartDate <= "31-Dec-2009"
AND effectiveEndDate >= "31-Dec-2009"


group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName


) dt_box
,

(
select datename(month, l.periodStartDate) month
,a.advisorLegalName as advisorLegalName
,f.fundLegalName as fundLegalName
,round(avg(ReportField4),3) as Assets_Liabilities_Confirmed

FROM

ODSAggregatesSet1 l
, FundSections fs
,Sections s
,fsid..Fund f
,fsid..Advisor a

WHERE (1=1)
AND s.sectionKey = fs.sectionKey
AND fs.fundId= l.fundId
AND s.sectionKey = l.sectionKey
AND f.fundId=fs.fundId
AND f.advisorKey=a.advisorKey
AND l.ReportField1="Confirmed by MSFS"

AND l.periodStartDate between "01-Sep-2009" AND "31-Dec-2009"
AND l.baseCurrency = "USD"
AND effectiveStartDate <= "31-Dec-2009"
AND effectiveEndDate >= "31-Dec-2009"


group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName


) dt_S2

,

(
select datename(month, l.periodStartDate) month
,a.advisorLegalName as advisorLegalName
,f.fundLegalName as fundLegalName
,round(avg(ReportField4),3) as Provided_by_Fund_Manager

FROM

ODSAggregatesSet1 l
, FundSections fs
,Sections s
,fsid..Fund f
,fsid..Advisor a

WHERE (1=1)
AND s.sectionKey = fs.sectionKey
AND fs.fundId= l.fundId
AND s.sectionKey = l.sectionKey
AND f.fundId=fs.fundId
AND f.advisorKey=a.advisorKey
AND l.ReportField1="Provided by Fund Manager"

AND l.periodStartDate between "01-Sep-2009" AND "31-Dec-2009"
AND l.baseCurrency = "USD"
AND effectiveStartDate <= "31-Dec-2009"
AND effectiveEndDate >= "31-Dec-2009"


group by l.periodStartDate
,a.advisorLegalName
,f.fundLegalName


) dt_S3

WHERE (1=1)
AND dt_box.month=dt_S2.month
AND dt_box.advisorLegalName=dt_S2.advisorLegalName
AND dt_box.fundLegalName=dt_S2.fundLegalName

AND dt_box.month=dt_S3.month
AND dt_box.advisorLegalName=dt_S3.advisorLegalName
AND dt_box.fundLegalName=dt_S3.fundLegalName



order by dt_box.month
,dt_box.advisorLegalName
,dt_box.fundLegalName
--=======================================================================================


Rob V [ Sybase ] Posted on 2010-01-28 08:40:57.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4b61397e.5b8.1681692777@sybase.com>
Subject: Re: how to handle <no rows returned> from a derived table in T-SQL
Lines: 163
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b614d99@forums-1-dub>
Date: 28 Jan 2010 00:40:57 -0800
X-Trace: forums-1-dub 1264668057 10.22.241.152 (28 Jan 2010 00:40:57 -0800)
X-Original-Trace: 28 Jan 2010 00:40:57 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28911
Article PK: 78151

Sounds like you're looking for an "outer join"?

create table t1 (a int)
insert t1 values (10)
insert t1 values (20)
insert t1 values (30)
go

create table t2 (b int, c varchar(10))
insert t2 values (20, 'twenty')
go

-- normal join:
1> select a, c from t1, t2 where a = b
2> go
a c
----------- ----------
20 twenty

-- outer join:
1> select a, c from t1 left outer join t2 on a = b
2> go
a c
----------- ----------
10 NULL
20 twenty
30 NULL

(3 rows affected)


-- changing the value for non-matching rows:
1> select a, fromt2=isnull(c,'No Row Found!') from t1 left outer join t2 on
a = b
2> go
a fromt2
----------- -------------
10 No Row Found!
20 twenty
30 No Row Found!

(3 rows affected)


Check the T-SQL User's guide in the ASE documnetation for more information
on outer joins (there's a lot to say about it).


HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

<Mihir Hardas> wrote in message news:4b61397e.5b8.1681692777@sybase.com...
> Hi All:
>
> I am trying to generate a resutset which computes certain
> financial values based on aggregation and then these values
> are presented horizontally one after the another in columns.
>
> The basic skeleton of the SQL Query is thus:
>
>
> SELECT
>
> -
>
> -
>
> -
>
> FROM
>
> (
>
> -- This derived table gets the avg of
> (Assets_Liabilities_Confirmed)
>
> SELECT avg(ReportField4) as Assets_Liabilities_Confirmed
>
> WHERE ...
>
> AND l.ReportField1="Confirmed by MSFS"
>
> group by l.periodStartDate
> ,a.advisorLegalName
> ,f.fundLegalName
>
> ) derived_table_1
>
> ,
>
>
>
> (
>
> -- This derived table gets the avg (Provided by 3rd Party)
>
> SELECT avg(ReportField4) as Provided by 3rd Party
>
> WHERE ...
>
> AND l.ReportField1="third party"
>
> group by l.periodStartDate
> ,a.advisorLegalName
> ,f.fundLegalName
>
> ) derived_table_2
>
>
>
> WHERE (1=1)
> AND derived_table_1.month=derived_table_2.month
> AND
> derived_table_1.advisorLegalName=derived_table_2.advisorLegalName
> AND
> derived_table_1.fundLegalName=derived_table_2.fundLegalName
>
>
>
>
>
> The issue is: <<suppose derived_table_2>> returns no rows
> which means, that there are no records for <<Provided by 3rd
> party>> the join conditions
> derived_table_1.XYZ=derived_table_2.XYZ fails and the entire
>
> SQL Query resultset becomes <no rows returmed>.
>
>
>
> So essentially, i am looking at handling the no rows
> returned from derived_table_2 such that the other resutset
> is not hampered i.e. derived_table_1 in this case.
>
>
>
> Any pathways to solve this problem would be appreciated.
>
>
> Kind regards,
>
> ~Mihir
>


Mihir Hardas Posted on 2010-01-28 12:02:41.0Z
Sender: 71e.4b614799.1804289383@sybase.com
From: Mihir Hardas
Newsgroups: sybase.public.ase.general
Subject: Re: how to handle <no rows returned> from a derived table in T-SQL
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b617ce1.d23.1681692777@sybase.com>
References: <4b614d99@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jan 2010 04:02:41 -0800
X-Trace: forums-1-dub 1264680161 10.22.241.41 (28 Jan 2010 04:02:41 -0800)
X-Original-Trace: 28 Jan 2010 04:02:41 -0800, 10.22.241.41
Lines: 248
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28912
Article PK: 78152

Not really. I tried left join :) but it will naturally give
all the records from the left(most) table which is not the
objective.

Becasue there are several derived tables each calculating
entirely different resultset.



So my approcach is:



SELECT

dt_sandbox.year

,dt_1.assets_liabilities

, dt_2/provided_by_third_party



(

get records for the group by columns = year, month,
advisorLegalName, fundLegalName



) dt_sandbox {the base resultset}

,



(

select ...

) dt_1 {all records for assets & liabilities}

,

(

select ...



) dt_2 {all records for Provided by 3rd party }



WHERE (1=1)
AND

( dt_box.month=dt_1.month
AND dt_box.advisorLegalName=dt_1.advisorLegalName
AND dt_box.fundLegalName=dt_1.fundLegalName )



AND

(dt_box.month=dt_2.month --(1)
AND dt_box.advisorLegalName=dt_2.advisorLegalName --(2)
AND dt_box.fundLegalName=dt_2.fundLegalName) --(3)


The joins with numbers, result in no matching records and
thus entire result-set is <<no rows returned>> If I use,
left join, the enitre resultset of dt_sandbox is returned
which is not the objective.


Regards,
~Mihir

> Sounds like you're looking for an "outer join"?
>
> create table t1 (a int)
> insert t1 values (10)
> insert t1 values (20)
> insert t1 values (30)
> go
>
> create table t2 (b int, c varchar(10))
> insert t2 values (20, 'twenty')
> go
>
> -- normal join:
> 1> select a, c from t1, t2 where a = b
> 2> go
> a c
> ----------- ----------
> 20 twenty
>
> -- outer join:
> 1> select a, c from t1 left outer join t2 on a = b
> 2> go
> a c
> ----------- ----------
> 10 NULL
> 20 twenty
> 30 NULL
>
> (3 rows affected)
>
>
> -- changing the value for non-matching rows:
> 1> select a, fromt2=isnull(c,'No Row Found!') from t1 left
> outer join t2 on a = b
> 2> go
> a fromt2
> ----------- -------------
> 10 No Row Found!
> 20 twenty
> 30 No Row Found!
>
> (3 rows affected)
>
>
> Check the T-SQL User's guide in the ASE documnetation for
> more information on outer joins (there's a lot to say
> about it).
>
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., Amersfoort, The Netherlands
> Chamber of Commerce 27138666
> ----------------------------------------------------------
> -------
>
> <Mihir Hardas> wrote in message
> > news:4b61397e.5b8.1681692777@sybase.com... Hi All:
> >
> > I am trying to generate a resutset which computes
> > certain financial values based on aggregation and then
> > these values are presented horizontally one after the
> another in columns. >
> > The basic skeleton of the SQL Query is thus:
> >
> >
> > SELECT
> >
> > -
> >
> > -
> >
> > -
> >
> > FROM
> >
> > (
> >
> > -- This derived table gets the avg of
> > (Assets_Liabilities_Confirmed)
> >
> > SELECT avg(ReportField4) as
> Assets_Liabilities_Confirmed >
> > WHERE ...
> >
> > AND l.ReportField1="Confirmed by MSFS"
> >
> > group by l.periodStartDate
> > ,a.advisorLegalName
> > ,f.fundLegalName
> >
> > ) derived_table_1
> >
> > ,
> >
> >
> >
> > (
> >
> > -- This derived table gets the avg (Provided by 3rd
> Party) >
> > SELECT avg(ReportField4) as Provided by 3rd Party
> >
> > WHERE ...
> >
> > AND l.ReportField1="third party"
> >
> > group by l.periodStartDate
> > ,a.advisorLegalName
> > ,f.fundLegalName
> >
> > ) derived_table_2
> >
> >
> >
> > WHERE (1=1)
> > AND derived_table_1.month=derived_table_2.month
> > AND
> >
> derived_table_1.advisorLegalName=derived_table_2.advisorLe
> > galName AND
> >
> derived_table_1.fundLegalName=derived_table_2.fundLegalNam
> e >
> >
> >
> >
> >
> > The issue is: <<suppose derived_table_2>> returns no
> > rows which means, that there are no records for
> > <<Provided by 3rd party>> the join conditions
> > derived_table_1.XYZ=derived_table_2.XYZ fails and the
> entire >
> > SQL Query resultset becomes <no rows returmed>.
> >
> >
> >
> > So essentially, i am looking at handling the no rows
> > returned from derived_table_2 such that the other
> > resutset is not hampered i.e. derived_table_1 in this
> case. >
> >
> >
> > Any pathways to solve this problem would be appreciated.
> >
> >
> > Kind regards,
> >
> > ~Mihir
> >
>
>


Luc Van der Veurst Posted on 2010-01-28 15:26:16.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4b614d99@forums-1-dub> <4b617ce1.d23.1681692777@sybase.com>
Subject: Re: how to handle <no rows returned> from a derived table in T-SQL
Lines: 269
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b61ac98$1@forums-1-dub>
Date: 28 Jan 2010 07:26:16 -0800
X-Trace: forums-1-dub 1264692376 10.22.241.152 (28 Jan 2010 07:26:16 -0800)
X-Original-Trace: 28 Jan 2010 07:26:16 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28914
Article PK: 78154


<Mihir Hardas> wrote in message news:4b617ce1.d23.1681692777@sybase.com...
> Not really. I tried left join :) but it will naturally give
> all the records from the left(most) table which is not the
> objective.
>
> Becasue there are several derived tables each calculating
> entirely different resultset.

What is the leftmost table ? ODSAggregatesSet1 ? You get all
rows from ODSAggregatesSet1 in the result set ?

You didn't give the code of the left outer join that you tried.

But your example is way to complex for me to understand.
Rob gave an example about how he understood your
problem and I interpreted your problem the same way.

It would help if you could do something similar: create some
simple tables and insert some rows, create a query that contains
your problem and give the result set that you get + the result set you'd
like
to get.

Luc.


>
> So my approcach is:
>
>
>
> SELECT
>
> dt_sandbox.year
>
> ,dt_1.assets_liabilities
>
> , dt_2/provided_by_third_party
>
>
>
> (
>
> get records for the group by columns = year, month,
> advisorLegalName, fundLegalName
>
>
>
> ) dt_sandbox {the base resultset}
>
> ,
>
>
>
> (
>
> select ...
>
> ) dt_1 {all records for assets & liabilities}
>
> ,
>
> (
>
> select ...
>
>
>
> ) dt_2 {all records for Provided by 3rd party }
>
>
>
> WHERE (1=1)
> AND
>
> ( dt_box.month=dt_1.month
> AND dt_box.advisorLegalName=dt_1.advisorLegalName
> AND dt_box.fundLegalName=dt_1.fundLegalName )
>
>
>
> AND
>
> (dt_box.month=dt_2.month --(1)
> AND dt_box.advisorLegalName=dt_2.advisorLegalName --(2)
> AND dt_box.fundLegalName=dt_2.fundLegalName) --(3)
>
>
> The joins with numbers, result in no matching records and
> thus entire result-set is <<no rows returned>> If I use,
> left join, the enitre resultset of dt_sandbox is returned
> which is not the objective.
>
>
> Regards,
> ~Mihir
>
>
>> Sounds like you're looking for an "outer join"?
>>
>> create table t1 (a int)
>> insert t1 values (10)
>> insert t1 values (20)
>> insert t1 values (30)
>> go
>>
>> create table t2 (b int, c varchar(10))
>> insert t2 values (20, 'twenty')
>> go
>>
>> -- normal join:
>> 1> select a, c from t1, t2 where a = b
>> 2> go
>> a c
>> ----------- ----------
>> 20 twenty
>>
>> -- outer join:
>> 1> select a, c from t1 left outer join t2 on a = b
>> 2> go
>> a c
>> ----------- ----------
>> 10 NULL
>> 20 twenty
>> 30 NULL
>>
>> (3 rows affected)
>>
>>
>> -- changing the value for non-matching rows:
>> 1> select a, fromt2=isnull(c,'No Row Found!') from t1 left
>> outer join t2 on a = b
>> 2> go
>> a fromt2
>> ----------- -------------
>> 10 No Row Found!
>> 20 twenty
>> 30 No Row Found!
>>
>> (3 rows affected)
>>
>>
>> Check the T-SQL User's guide in the ASE documnetation for
>> more information on outer joins (there's a lot to say
>> about it).
>>
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>> http://www.sypron.nl
>> Sypron B.V., Amersfoort, The Netherlands
>> Chamber of Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>> <Mihir Hardas> wrote in message
>> > news:4b61397e.5b8.1681692777@sybase.com... Hi All:
>> >
>> > I am trying to generate a resutset which computes
>> > certain financial values based on aggregation and then
>> > these values are presented horizontally one after the
>> another in columns. >
>> > The basic skeleton of the SQL Query is thus:
>> >
>> >
>> > SELECT
>> >
>> > -
>> >
>> > -
>> >
>> > -
>> >
>> > FROM
>> >
>> > (
>> >
>> > -- This derived table gets the avg of
>> > (Assets_Liabilities_Confirmed)
>> >
>> > SELECT avg(ReportField4) as
>> Assets_Liabilities_Confirmed >
>> > WHERE ...
>> >
>> > AND l.ReportField1="Confirmed by MSFS"
>> >
>> > group by l.periodStartDate
>> > ,a.advisorLegalName
>> > ,f.fundLegalName
>> >
>> > ) derived_table_1
>> >
>> > ,
>> >
>> >
>> >
>> > (
>> >
>> > -- This derived table gets the avg (Provided by 3rd
>> Party) >
>> > SELECT avg(ReportField4) as Provided by 3rd Party
>> >
>> > WHERE ...
>> >
>> > AND l.ReportField1="third party"
>> >
>> > group by l.periodStartDate
>> > ,a.advisorLegalName
>> > ,f.fundLegalName
>> >
>> > ) derived_table_2
>> >
>> >
>> >
>> > WHERE (1=1)
>> > AND derived_table_1.month=derived_table_2.month
>> > AND
>> >
>> derived_table_1.advisorLegalName=derived_table_2.advisorLe
>> > galName AND
>> >
>> derived_table_1.fundLegalName=derived_table_2.fundLegalNam
>> e >
>> >
>> >
>> >
>> >
>> > The issue is: <<suppose derived_table_2>> returns no
>> > rows which means, that there are no records for
>> > <<Provided by 3rd party>> the join conditions
>> > derived_table_1.XYZ=derived_table_2.XYZ fails and the
>> entire >
>> > SQL Query resultset becomes <no rows returmed>.
>> >
>> >
>> >
>> > So essentially, i am looking at handling the no rows
>> > returned from derived_table_2 such that the other
>> > resutset is not hampered i.e. derived_table_1 in this
>> case. >
>> >
>> >
>> > Any pathways to solve this problem would be appreciated.
>> >
>> >
>> > Kind regards,
>> >
>> > ~Mihir
>> >
>>
>>


"Mark A. Parsons" <iron_horse Posted on 2010-01-28 17:04:17.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: how to handle <no rows returned> from a derived table in T-SQL
References: <4b614d99@forums-1-dub> <4b617ce1.d23.1681692777@sybase.com>
In-Reply-To: <4b617ce1.d23.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100125-0, 01/25/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b61c391$1@forums-1-dub>
Date: 28 Jan 2010 09:04:17 -0800
X-Trace: forums-1-dub 1264698257 10.22.241.152 (28 Jan 2010 09:04:17 -0800)
X-Original-Trace: 28 Jan 2010 09:04:17 -0800, vip152.sybase.com
Lines: 78
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28915
Article PK: 78157

Based on your partial query and your comments ...

- dt_1 is never empty

- dt_2 is occassionally empty

- dt_1 and dt_2 are joined to dt_sandbox with the same set of columns/values

- if dt_2 returns no rows you still want to see all of the records generated by the join between dt_sandbox and dt_1

Have you tried performing the following joins:

- equi-join between dt_sandbox and dt_1

- outer join between dt_1 (outer table) and dt_2 (inner table)

Another option would be to perform an equi-join between dt_sandbox and dt_1 as a new derived table (eg, dt_main), then
perform an outer join between dt_main (outer table) and dt_2 (inner table).

If this suggestion doesn't address your issue then you'll probably be better off providing more details. I complete
reproducible example of table DDL, raw data, and result data would be very helpful.

Mihir Hardas wrote:
> Not really. I tried left join :) but it will naturally give
> all the records from the left(most) table which is not the
> objective.
>
> Becasue there are several derived tables each calculating
> entirely different resultset.
>
> So my approcach is:
>
> SELECT
>
> dt_sandbox.year
>
> ,dt_1.assets_liabilities
>
> , dt_2/provided_by_third_party
>
> (
>
> get records for the group by columns = year, month,
> advisorLegalName, fundLegalName
>
> ) dt_sandbox {the base resultset},
>
> (
>
> select ...
>
> ) dt_1 {all records for assets & liabilities} ,
>
> (
>
> select ...
>
> ) dt_2 {all records for Provided by 3rd party }
>
> WHERE (1=1)
> AND
>
> ( dt_box.month=dt_1.month
> AND dt_box.advisorLegalName=dt_1.advisorLegalName
> AND dt_box.fundLegalName=dt_1.fundLegalName )
>
> AND
>
> (dt_box.month=dt_2.month --(1)
> AND dt_box.advisorLegalName=dt_2.advisorLegalName --(2)
> AND dt_box.fundLegalName=dt_2.fundLegalName) --(3)
>
>
> The joins with numbers, result in no matching records and
> thus entire result-set is <<no rows returned>> If I use,
> left join, the enitre resultset of dt_sandbox is returned
> which is not the objective.


Mihir Hardas Posted on 2010-02-01 10:08:36.0Z
Sender: 4590.4b66a714.1804289383@sybase.com
From: Mihir Hardas
Newsgroups: sybase.public.ase.general
Subject: Re: how to handle <no rows returned> from a derived table in T-SQL
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b66a824.45a4.1681692777@sybase.com>
References: <4b61c391$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Feb 2010 02:08:36 -0800
X-Trace: forums-1-dub 1265018916 10.22.241.41 (1 Feb 2010 02:08:36 -0800)
X-Original-Trace: 1 Feb 2010 02:08:36 -0800, 10.22.241.41
Lines: 102
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28920
Article PK: 78164

I think Mark has addressed it exactly and correctly.
I have adopted the following approcach to address this
business case.

1) I take all the base records in a temp table
2) by derived tables or comination of derived tables (as
suggested by Mark) create the 2D matrix of financial
positions and other percentage data
3) Other than the grouping columns if all the fields are
NULL, I delete them off.
Point 3) is necessary because ANY of the derived table can
comeup with NULL values.

Thanks a lot to all.
~Mihir Hardas

> Based on your partial query and your comments ...
>
> - dt_1 is never empty
>
> - dt_2 is occassionally empty
>
> - dt_1 and dt_2 are joined to dt_sandbox with the same set
> of columns/values
>
> - if dt_2 returns no rows you still want to see all of the
> records generated by the join between dt_sandbox and dt_1
>
> Have you tried performing the following joins:
>
> - equi-join between dt_sandbox and dt_1
>
> - outer join between dt_1 (outer table) and dt_2 (inner
> table)
>
> Another option would be to perform an equi-join between
> dt_sandbox and dt_1 as a new derived table (eg, dt_main),
> then perform an outer join between dt_main (outer table)
> and dt_2 (inner table).
>
> If this suggestion doesn't address your issue then you'll
> probably be better off providing more details. I complete
> reproducible example of table DDL, raw data, and result
> data would be very helpful.
>
>
> Mihir Hardas wrote:
> > Not really. I tried left join :) but it will naturally
> > give all the records from the left(most) table which is
> > not the objective.
> >
> > Becasue there are several derived tables each
> > calculating entirely different resultset.
> >
> > So my approcach is:
> >
> > SELECT
> >
> > dt_sandbox.year
> >
> > ,dt_1.assets_liabilities
> >
> > , dt_2/provided_by_third_party
> >
> > (
> >
> > get records for the group by columns = year, month,
> > advisorLegalName, fundLegalName
> >
> > ) dt_sandbox {the base resultset},
> >
> > (
> >
> > select ...
> >
> > ) dt_1 {all records for assets & liabilities} ,
> >
> > (
> >
> > select ...
> >
> > ) dt_2 {all records for Provided by 3rd party }
> >
> > WHERE (1=1)
> > AND
> >
> > ( dt_box.month=dt_1.month
> > AND dt_box.advisorLegalName=dt_1.advisorLegalName
> > AND dt_box.fundLegalName=dt_1.fundLegalName )
> >
> > AND
> >
> > (dt_box.month=dt_2.month --(1)
> > AND dt_box.advisorLegalName=dt_2.advisorLegalName
> > --(2) AND dt_box.fundLegalName=dt_2.fundLegalName)
> > --(3)
> >
> > The joins with numbers, result in no matching records
> > and thus entire result-set is <<no rows returned>> If I
> > use, left join, the enitre resultset of dt_sandbox is
> > returned which is not the objective.