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.

Column names of derived table

16 posts in General Discussion Last posting was on 2009-08-17 15:19:22.0Z
pd Posted on 2009-08-11 10:36:26.0Z
Sender: 7ea1.4a8146ab.1804289383@sybase.com
From: PD
Newsgroups: sybase.public.ase.general
Subject: Column names of derived table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a8149aa.20f.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 11 Aug 2009 03:36:26 -0700
X-Trace: forums-3-dub.sybase.com 1249986986 10.22.241.188 (11 Aug 2009 03:36:26 -0700)
X-Original-Trace: 11 Aug 2009 03:36:26 -0700, forums-3-dub.sybase.com
Lines: 39
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28124
Article PK: 77368

To get the previous business day:
Why does this not work? bug?

select max(prevdate) from (
select dateadd(dd
,datediff(dd, '0:0', getdate()) - number
,'0:0')
from master.dbo.spt_values
where type='P' and number between 1 and 10
)t (prevdate)
where datepart(dw,prevdate) not in (1,7)
and not exists
(select 1 from holidays where holiday=t.prevdate)

on ASE 12.5.4 the process terminates

However when I specify the column name prevdate inside the
derived table then no problem e.g.

select max(prevdate) from (
select prevdate=
dateadd(dd
,datediff(dd, '0:0', getdate()) - number
,'0:0')
from master.dbo.spt_values
where type='P' and number between 1 and 10
)t
where datepart(dw,prevdate) not in (1,7)
and not exists
(select 1 from holidays where holiday=t.prevdate)


Sample holidays table:
create table holidays (holiday datetime)
insert into holidays select
'20090101' union all select
'20090810' union all select
'20091225' union all select
'20091226'


mpeppler@peppler.org [Team Sybase] Posted on 2009-08-11 12:05:28.0Z
From: "mpeppler@peppler.org [Team Sybase]" <michael.peppler@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Column names of derived table
Date: Tue, 11 Aug 2009 05:05:28 -0700 (PDT)
Organization: http://groups.google.com
Lines: 22
Message-ID: <281eeed9-da8a-475f-af90-a1ef57228017@c2g2000yqi.googlegroups.com>
References: <4a8149aa.20f.1681692777@sybase.com>
NNTP-Posting-Host: 170.148.215.156
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1249992328 20815 127.0.0.1 (11 Aug 2009 12:05:28 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 11 Aug 2009 12:05:28 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: c2g2000yqi.googlegroups.com; posting-host=170.148.215.156; posting-account=9rHMzAoAAADtzToS8d2WKVGlkISAvPdk
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.2) Gecko/20090729 Firefox/3.5.2,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!c2g2000yqi.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28125
Article PK: 77370


On Aug 11, 12:36 pm, PD wrote:
> To get the previous business day:
> Why does this not work? bug?
>
> select max(prevdate) from (
> select dateadd(dd
>   ,datediff(dd, '0:0', getdate()) - number
>   ,'0:0')
> from master.dbo.spt_values
> where type='P' and number between 1 and 10
> )t (prevdate)
> where datepart(dw,prevdate) not in (1,7)
> and not exists
> (select 1 from holidays where holiday=t.prevdate)
>
> on ASE 12.5.4 the process terminates

I think you MUST specify an alias for the column names in the derived
table, otherwise ASE doesn't know what you are referring to in the
outer query.

Michael


pd Posted on 2009-08-11 12:22:03.0Z
Sender: 7ea1.4a8146ab.1804289383@sybase.com
From: PD
Newsgroups: sybase.public.ase.general
Subject: Re: Column names of derived table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a81626a.14d9.1681692777@sybase.com>
References: <281eeed9-da8a-475f-af90-a1ef57228017@c2g2000yqi.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 11 Aug 2009 05:22:03 -0700
X-Trace: forums-3-dub.sybase.com 1249993323 10.22.241.188 (11 Aug 2009 05:22:03 -0700)
X-Original-Trace: 11 Aug 2009 05:22:03 -0700, forums-3-dub.sybase.com
Lines: 29
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28126
Article PK: 77371


> On Aug 11, 12:36�pm, PD wrote:
> > To get the previous business day:
> > Why does this not work? bug?
> >
> > select max(prevdate) from (
> > select dateadd(dd
> > � ,datediff(dd, '0:0', getdate()) - number
> > � ,'0:0')
> > from master.dbo.spt_values
> > where type='P' and number between 1 and 10
> > )t (prevdate)
> > where datepart(dw,prevdate) not in (1,7)
> > and not exists
> > (select 1 from holidays where holiday=t.prevdate)
> >
> > on ASE 12.5.4 the process terminates
>
> I think you MUST specify an alias for the column names in
> the derived table, otherwise ASE doesn't know what you are
> referring to in the outer query.
>
> Michael

I do give column names
In the 1st query it is after the derived table name i.e.
t (prevdate)

similar to
select * from (select 'abc',123)f(x,y)


"Mark A. Parsons" <iron_horse Posted on 2009-08-11 16:42:42.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: Column names of derived table
References: <4a8149aa.20f.1681692777@sybase.com>
In-Reply-To: <4a8149aa.20f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090808-0, 08/08/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a819f82$3@forums-3-dub.sybase.com>
Date: 11 Aug 2009 09:42:42 -0700
X-Trace: forums-3-dub.sybase.com 1250008962 10.22.241.152 (11 Aug 2009 09:42:42 -0700)
X-Original-Trace: 11 Aug 2009 09:42:42 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28129
Article PK: 77372

When I try to execute the first query in ASE 15.0.3/ESD #1 I get the following compilation error messages:

===============
Msg 206, Level 16, State 2:
Server 'CC1', Line 1:
Operand type clash: MAINTENANCE TOKEN is incompatible with TIME
Msg 206, Level 16, State 2:
Server 'CC1', Line 1:
Operand type clash: MAINTENANCE TOKEN is incompatible with TIME
===============

Dataserver is still up and running ... no errors/messages/stack-traces in the ASE errorlog.

If I replace '0:0' with the equivalent '' or '1/1/1900', I still get the same compilation errors ... so it doesn't
appear to be an issue with the use of '0:0' as a datetime value.

--------

If I comment out the 'not exists()' clause the query will compile and run to completion. [Sure, it doesn't generate the
desired result ... I'm just trying to narrow down where the issue resides.]

If I comment out the 'datepart(dw...)' clause I get a stack trace in the ASE errorlog and my connection (into the
dataserver) is terminated.

--------

The syntax of your query looks ok, and I'm able to run the derived table definition as a standalone query, so it looks
like the optimizer is having problems associating 'prevdate' with the date function results.

When you provide a column name (in the 2nd query) this seems to make things easier for the parsing/compilation step ...
*shrug*.

I'd recommend opening a case with Sybase TechSupport to see if this is a known issue.

PD wrote:
> select max(prevdate) from (
> select dateadd(dd
> ,datediff(dd, '0:0', getdate()) - number
> ,'0:0')
> from master.dbo.spt_values
> where type='P' and number between 1 and 10
> )t (prevdate)
> where datepart(dw,prevdate) not in (1,7)
> and not exists
> (select 1 from holidays where holiday=t.prevdate)
>
> on ASE 12.5.4 the process terminates


pd Posted on 2009-08-12 09:25:44.0Z
Sender: 7ea1.4a8146ab.1804289383@sybase.com
From: PD
Newsgroups: sybase.public.ase.general
Subject: Re: Column names of derived table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a828a98.7797.1681692777@sybase.com>
References: <4a819f82$3@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 12 Aug 2009 02:25:44 -0700
X-Trace: forums-3-dub.sybase.com 1250069144 10.22.241.188 (12 Aug 2009 02:25:44 -0700)
X-Original-Trace: 12 Aug 2009 02:25:44 -0700, forums-3-dub.sybase.com
Lines: 89
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28142
Article PK: 77386

Thanks Mark

The local Sybase TechSupport says it is the same issue as
CR: 5500847

Another work around to my original query appears to be
rewriting the original query as a left join i.e.

select max(prevdate) from (
select dateadd(dd ,datediff(dd, '', getdate()) - number
,'') -- no column name here
from master.dbo.spt_values
where type='P' and number between 1 and 10
)t(prevdate) -- column name here
left join
(select holiday from holidays)h
on h.holiday=t.prevdate
where datepart(dw,prevdate) not in (1,7)
and h.holiday is null


For those not familiar with the SQL syntax I used,
here are the links to the documentation RE: Renaming columns
with SQL derived tables
ASE 12.5.1:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc32300_1251/html/sqlug/sqlug182.htm
ASE 15.0.3:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1502/html/sqlug/sqlug427.htm

> When I try to execute the first query in ASE 15.0.3/ESD #1
> I get the following compilation error messages:
>
> ===============
> Msg 206, Level 16, State 2:
> Server 'CC1', Line 1:
> Operand type clash: MAINTENANCE TOKEN is incompatible with
> TIME Msg 206, Level 16, State 2:
> Server 'CC1', Line 1:
> Operand type clash: MAINTENANCE TOKEN is incompatible with
> TIME ===============
>
> Dataserver is still up and running ... no
> errors/messages/stack-traces in the ASE errorlog.
>
> If I replace '0:0' with the equivalent '' or '1/1/1900', I
> still get the same compilation errors ... so it doesn't
> appear to be an issue with the use of '0:0' as a datetime
> value.
>
> --------
>
> If I comment out the 'not exists()' clause the query will
> compile and run to completion. [Sure, it doesn't generate
> the desired result ... I'm just trying to narrow down
> where the issue resides.]
>
> If I comment out the 'datepart(dw...)' clause I get a
> stack trace in the ASE errorlog and my connection (into
> the dataserver) is terminated.
>
> --------
>
> The syntax of your query looks ok, and I'm able to run the
> derived table definition as a standalone query, so it
> looks like the optimizer is having problems associating
> 'prevdate' with the date function results.
>
> When you provide a column name (in the 2nd query) this
> seems to make things easier for the parsing/compilation
> step ... *shrug*.
>
> I'd recommend opening a case with Sybase TechSupport to
> see if this is a known issue.
>
>
> PD wrote:
> > select max(prevdate) from (
> > select dateadd(dd
> > ,datediff(dd, '0:0', getdate()) - number
> > ,'0:0')
> > from master.dbo.spt_values
> > where type='P' and number between 1 and 10
> > )t (prevdate)
> > where datepart(dw,prevdate) not in (1,7)
> > and not exists
> > (select 1 from holidays where holiday=t.prevdate)
> >
> > on ASE 12.5.4 the process terminates


"Mark A. Parsons" <iron_horse Posted on 2009-08-12 13:02:46.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: Column names of derived table
References: <4a819f82$3@forums-3-dub.sybase.com> <4a828a98.7797.1681692777@sybase.com>
In-Reply-To: <4a828a98.7797.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090811-0, 08/11/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a82bd76$5@forums-3-dub.sybase.com>
Date: 12 Aug 2009 06:02:46 -0700
X-Trace: forums-3-dub.sybase.com 1250082166 10.22.241.152 (12 Aug 2009 06:02:46 -0700)
X-Original-Trace: 12 Aug 2009 06:02:46 -0700, vip152.sybase.com
Lines: 13
X-Authenticated-User: TeamSybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28146
Article PK: 77389


PD wrote:
> The local Sybase TechSupport says it is the same issue as
> CR: 5500847

According to the notes for CR 5500847, there should be a fix in ASE 15.0.3/ESD #1.

I'm running ASE 15.0.3/ESD#1 and while my session is not terminated (like your session), I do get the compilation errors
(see my original post in this thread).

It looks like either a) the CR wasn't fixed in 15.0.3/ESD#1 or b) this CR does not address your issue or c) your problem
(session termination) has morphed into a different problem (compilation errors).


pd Posted on 2009-08-14 06:02:07.0Z
Sender: 4a6c.4a84fd0a.1804289383@sybase.com
From: PD
Newsgroups: sybase.public.ase.general
Subject: Re: Column names of derived table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a84fddf.4a82.1681692777@sybase.com>
References: <4a82bd76$5@forums-3-dub.sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Aug 2009 23:02:07 -0700
X-Trace: forums-1-dub 1250229727 10.22.241.41 (13 Aug 2009 23:02:07 -0700)
X-Original-Trace: 13 Aug 2009 23:02:07 -0700, 10.22.241.41
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28160
Article PK: 77407

FYI: A new CR has been opened for this issue.

> PD wrote:
> > The local Sybase TechSupport says it is the same issue
> > as CR: 5500847
>
> According to the notes for CR 5500847, there should be a
> fix in ASE 15.0.3/ESD #1.
>
> I'm running ASE 15.0.3/ESD#1 and while my session is not
> terminated (like your session), I do get the compilation
> errors (see my original post in this thread).
>
> It looks like either a) the CR wasn't fixed in
> 15.0.3/ESD#1 or b) this CR does not address your issue or
> c) your problem (session termination) has morphed into a
> different problem (compilation errors).


pd Posted on 2009-08-11 12:51:54.0Z
Sender: 7ea1.4a8146ab.1804289383@sybase.com
From: PD
Newsgroups: sybase.public.ase.general
Subject: Re: Column names of derived table
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a81696a.1a1b.1681692777@sybase.com>
References: <4a8149aa.20f.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 11 Aug 2009 05:51:54 -0700
X-Trace: forums-3-dub.sybase.com 1249995114 10.22.241.188 (11 Aug 2009 05:51:54 -0700)
X-Original-Trace: 11 Aug 2009 05:51:54 -0700, forums-3-dub.sybase.com
Lines: 55
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28127
Article PK: 77373


> To get the previous business day:
> Why does this not work? bug?
>
> select max(prevdate) from (
> select dateadd(dd
> ,datediff(dd, '0:0', getdate()) - number
> ,'0:0')
> from master.dbo.spt_values
> where type='P' and number between 1 and 10
> )t (prevdate)
> where datepart(dw,prevdate) not in (1,7)
> and not exists
> (select 1 from holidays where holiday=t.prevdate)
>
> on ASE 12.5.4 the process terminates
>
> However when I specify the column name prevdate inside the
> derived table then no problem e.g.
>
> select max(prevdate) from (
> select prevdate=
> dateadd(dd
> ,datediff(dd, '0:0', getdate()) - number
> ,'0:0')
> from master.dbo.spt_values
> where type='P' and number between 1 and 10
> )t
> where datepart(dw,prevdate) not in (1,7)
> and not exists
> (select 1 from holidays where holiday=t.prevdate)
>
>
> Sample holidays table:
> create table holidays (holiday datetime)
> insert into holidays select
> '20090101' union all select
> '20090810' union all select
> '20091225' union all select
> '20091226'

There is also a problem with ASE 15 although a different
error
It seems related to renaming the column as the below code
also fails unless you remove the renaming of the column
select max(prevdate) from (
select prevdt=
dateadd(dd
,datediff(dd, '0:0', getdate()) - number
,'0:0')
from master.dbo.spt_values
where type='P' and number between 1 and 10
)t (prevdate)
where datepart(dw,prevdate) not in (1,7)
and not exists
(select 1 from holidays where holiday=t.prevdate)


Jeff Tallman [Sybase] Posted on 2009-08-11 18:50:44.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Column names of derived table
References: <4a8149aa.20f.1681692777@sybase.com> <4a81696a.1a1b.1681692777@sybase.com>
In-Reply-To: <4a81696a.1a1b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81bd84$5@forums-3-dub.sybase.com>
Date: 11 Aug 2009 11:50:44 -0700
X-Trace: forums-3-dub.sybase.com 1250016644 10.22.241.152 (11 Aug 2009 11:50:44 -0700)
X-Original-Trace: 11 Aug 2009 11:50:44 -0700, vip152.sybase.com
Lines: 108
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28131
Article PK: 77376

Part of your problem is that you do have the syntax wrong. Column alias
*MUST* be either immediately preceding the projection expression or
immediately after the column projection.

For example:

select <colname>=<expression>

..or...

select <expression> [as] <colname>

what you have is:

(subquery) <colname>

Which is incorrect - whether a derived table or not. Worse yet, the syntax:

from table (<name>)

Typically is reserved for index forcing, etc. so effectively the syntax
you are trying is confusing the optimizer to think that (prevdate) is
some sort of index forcing.

To fix it, you must either do

select max(prevdate)
from (select dateadd(dd,
datediff(dd,'0:0',getdate())-number, '0:0') as prevdate
from master..spt_values
where type='P' and number between 1 and 10) t
where datepart(dw,prevdate) not in (1,7)
and not exists (select 1 from holidays where holiday=t.prevdate)

or

select max(prevdate)
from (select prevdate=dateadd(dd,
datediff(dd,'0:0',getdate())-number, '0:0')
from master..spt_values
where type='P' and number between 1 and 10) t
where datepart(dw,prevdate) not in (1,7)
and not exists (select 1 from holidays where holiday=t.prevdate)

Both compile and execute fine (in mere ms) in ASE 15.0.3.

Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

PD wrote:
>> To get the previous business day:
>> Why does this not work? bug?
>>
>> select max(prevdate) from (
>> select dateadd(dd
>> ,datediff(dd, '0:0', getdate()) - number
>> ,'0:0')
>> from master.dbo.spt_values
>> where type='P' and number between 1 and 10
>> )t (prevdate)
>> where datepart(dw,prevdate) not in (1,7)
>> and not exists
>> (select 1 from holidays where holiday=t.prevdate)
>>
>> on ASE 12.5.4 the process terminates
>>
>> However when I specify the column name prevdate inside the
>> derived table then no problem e.g.
>>
>> select max(prevdate) from (
>> select prevdate=
>> dateadd(dd
>> ,datediff(dd, '0:0', getdate()) - number
>> ,'0:0')
>> from master.dbo.spt_values
>> where type='P' and number between 1 and 10
>> )t
>> where datepart(dw,prevdate) not in (1,7)
>> and not exists
>> (select 1 from holidays where holiday=t.prevdate)
>>
>>
>> Sample holidays table:
>> create table holidays (holiday datetime)
>> insert into holidays select
>> '20090101' union all select
>> '20090810' union all select
>> '20091225' union all select
>> '20091226'
>
> There is also a problem with ASE 15 although a different
> error
> It seems related to renaming the column as the below code
> also fails unless you remove the renaming of the column
> select max(prevdate) from (
> select prevdt=
> dateadd(dd
> ,datediff(dd, '0:0', getdate()) - number
> ,'0:0')
> from master.dbo.spt_values
> where type='P' and number between 1 and 10
> )t (prevdate)
> where datepart(dw,prevdate) not in (1,7)
> and not exists
> (select 1 from holidays where holiday=t.prevdate)


"Mark A. Parsons" <iron_horse Posted on 2009-08-11 19:36:31.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: Column names of derived table
References: <4a8149aa.20f.1681692777@sybase.com> <4a81696a.1a1b.1681692777@sybase.com> <4a81bd84$5@forums-3-dub.sybase.com>
In-Reply-To: <4a81bd84$5@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090808-0, 08/08/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81c83f$2@forums-3-dub.sybase.com>
Date: 11 Aug 2009 12:36:31 -0700
X-Trace: forums-3-dub.sybase.com 1250019391 10.22.241.152 (11 Aug 2009 12:36:31 -0700)
X-Original-Trace: 11 Aug 2009 12:36:31 -0700, vip152.sybase.com
Lines: 150
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28135
Article PK: 77377

The column alias may also be defined in the derived table's column list (ie, parenthesis after the derived table's name)
as from the OP:

========================== OP's first query
select max(prevdate)
from (
select dateadd(...) -- no column alias
from master.dbo.spt_values
where type='P' and number between 1 and 10

)t (prevdate) -- column alias

where ....
==========================

or how about ...

========================== additional query
select max(prevdate)
from (
select dateadd(dd,1,holiday) -- no column alias
from holidays

)t (prevdate) -- column alias

==========================

In both cases the column alias is only listed in the derived table's column list.

This additional example compiles and runs just fine, but for some reason the OP's first query generates a compilation
error.

This method (ie, column aliases showing up only in the derived table's column list) in discussed, with exmaples, in the
T-SQL manual (12.5.1, 15.x).

While adding the column alias to the sub-query 'solves' the OP's problem, this doesn't explain why the optimizer has
problems compiling the OP's first query which *is* in a valid format.

Jeff Tallman [Sybase] wrote:
>
> Part of your problem is that you do have the syntax wrong. Column alias
> *MUST* be either immediately preceding the projection expression or
> immediately after the column projection.
>
> For example:
>
> select <colname>=<expression>
>
> ..or...
>
> select <expression> [as] <colname>
>
> what you have is:
>
> (subquery) <colname>
>
> Which is incorrect - whether a derived table or not. Worse yet, the
> syntax:
>
> from table (<name>)
>
> Typically is reserved for index forcing, etc. so effectively the syntax
> you are trying is confusing the optimizer to think that (prevdate) is
> some sort of index forcing.
>
> To fix it, you must either do
>
> select max(prevdate)
> from (select dateadd(dd,
> datediff(dd,'0:0',getdate())-number, '0:0') as prevdate
> from master..spt_values
> where type='P' and number between 1 and 10) t
> where datepart(dw,prevdate) not in (1,7)
> and not exists (select 1 from holidays where holiday=t.prevdate)
>
> or
>
> select max(prevdate)
> from (select prevdate=dateadd(dd,
> datediff(dd,'0:0',getdate())-number, '0:0')
> from master..spt_values
> where type='P' and number between 1 and 10) t
> where datepart(dw,prevdate) not in (1,7)
> and not exists (select 1 from holidays where holiday=t.prevdate)
>
> Both compile and execute fine (in mere ms) in ASE 15.0.3.
>
> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tallman@sybase.com
> http://blogs.sybase.com/database
>
> PD wrote:
>>> To get the previous business day:
>>> Why does this not work? bug?
>>>
>>> select max(prevdate) from (
>>> select dateadd(dd
>>> ,datediff(dd, '0:0', getdate()) - number
>>> ,'0:0')
>>> from master.dbo.spt_values
>>> where type='P' and number between 1 and 10
>>> )t (prevdate)
>>> where datepart(dw,prevdate) not in (1,7)
>>> and not exists
>>> (select 1 from holidays where holiday=t.prevdate)
>>>
>>> on ASE 12.5.4 the process terminates
>>>
>>> However when I specify the column name prevdate inside the
>>> derived table then no problem e.g.
>>>
>>> select max(prevdate) from (
>>> select prevdate=
>>> dateadd(dd
>>> ,datediff(dd, '0:0', getdate()) - number
>>> ,'0:0')
>>> from master.dbo.spt_values
>>> where type='P' and number between 1 and 10
>>> )t
>>> where datepart(dw,prevdate) not in (1,7)
>>> and not exists
>>> (select 1 from holidays where holiday=t.prevdate)
>>>
>>>
>>> Sample holidays table:
>>> create table holidays (holiday datetime)
>>> insert into holidays select
>>> '20090101' union all select
>>> '20090810' union all select
>>> '20091225' union all select
>>> '20091226'
>>
>> There is also a problem with ASE 15 although a different
>> error
>> It seems related to renaming the column as the below code
>> also fails unless you remove the renaming of the column
>> select max(prevdate) from (
>> select prevdt=
>> dateadd(dd
>> ,datediff(dd, '0:0', getdate()) - number
>> ,'0:0')
>> from master.dbo.spt_values
>> where type='P' and number between 1 and 10
>> )t (prevdate)
>> where datepart(dw,prevdate) not in (1,7)
>> and not exists
>> (select 1 from holidays where holiday=t.prevdate)


"Mark A. Parsons" <iron_horse Posted on 2009-08-11 19:56:22.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: Column names of derived table
References: <4a8149aa.20f.1681692777@sybase.com> <4a81696a.1a1b.1681692777@sybase.com> <4a81bd84$5@forums-3-dub.sybase.com>
In-Reply-To: <4a81bd84$5@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090808-0, 08/08/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81cce6$2@forums-3-dub.sybase.com>
Date: 11 Aug 2009 12:56:22 -0700
X-Trace: forums-3-dub.sybase.com 1250020582 10.22.241.152 (11 Aug 2009 12:56:22 -0700)
X-Original-Trace: 11 Aug 2009 12:56:22 -0700, vip152.sybase.com
Lines: 14
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28137
Article PK: 77378


Jeff Tallman [Sybase] wrote:
> ... snip ... Worse yet, the syntax:
>
> from table (<name>)
>
> Typically is reserved for index forcing, etc. so effectively the syntax
> you are trying is confusing the optimizer to think that (prevdate) is
> some sort of index forcing.

I'd hope the optimizer is smart enough to realize in this case that 'table' is a sub-query defining a derived table and
therefore cannot have any indexes, thus allowing the optimizer to parse the '(<name>)' as the derived table's column list.


Jeff Tallman [Sybase] Posted on 2009-08-17 15:00:24.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Column names of derived table
References: <4a8149aa.20f.1681692777@sybase.com> <4a81696a.1a1b.1681692777@sybase.com> <4a81bd84$5@forums-3-dub.sybase.com> <4a81cce6$2@forums-3-dub.sybase.com>
In-Reply-To: <4a81cce6$2@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a897088$2@forums-1-dub>
Date: 17 Aug 2009 08:00:24 -0700
X-Trace: forums-1-dub 1250521224 10.22.241.152 (17 Aug 2009 08:00:24 -0700)
X-Original-Trace: 17 Aug 2009 08:00:24 -0700, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28172
Article PK: 77413

Don't know what it does - frankly, I am surprised a syntax error isn't
thrown. It can't treat it as a column list as that isn't even correct
SQL....

Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

Mark A. Parsons wrote:
>
>
> Jeff Tallman [Sybase] wrote:
>> ... snip ... Worse yet, the syntax:
>>
>> from table (<name>)
>>
>> Typically is reserved for index forcing, etc. so effectively the
>> syntax you are trying is confusing the optimizer to think that
>> (prevdate) is some sort of index forcing.
>
> I'd hope the optimizer is smart enough to realize in this case that
> 'table' is a sub-query defining a derived table and therefore cannot
> have any indexes, thus allowing the optimizer to parse the '(<name>)' as
> the derived table's column list.
>


"Mark A. Parsons" <iron_horse Posted on 2009-08-17 15:19:22.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: Column names of derived table
References: <4a8149aa.20f.1681692777@sybase.com> <4a81696a.1a1b.1681692777@sybase.com> <4a81bd84$5@forums-3-dub.sybase.com> <4a81cce6$2@forums-3-dub.sybase.com> <4a897088$2@forums-1-dub>
In-Reply-To: <4a897088$2@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090811-0, 08/11/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a8974fa$2@forums-1-dub>
Date: 17 Aug 2009 08:19:22 -0700
X-Trace: forums-1-dub 1250522362 10.22.241.152 (17 Aug 2009 08:19:22 -0700)
X-Original-Trace: 17 Aug 2009 08:19:22 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28173
Article PK: 77418

Well, yes it is correct SQL and it is supported by ASE.

- see the 12.5.1 T-SQL manual (Derived Tables)
- see the 15.0.3 T-SQL manual (Derived Tables)
- see the SQL-92 standards for derived tables

Jeff Tallman [Sybase] wrote:
>
> Don't know what it does - frankly, I am surprised a syntax error isn't
> thrown. It can't treat it as a column list as that isn't even correct
> SQL....
>
> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tallman@sybase.com
> http://blogs.sybase.com/database
>
> Mark A. Parsons wrote:
>>
>>
>> Jeff Tallman [Sybase] wrote:
>>> ... snip ... Worse yet, the syntax:
>>>
>>> from table (<name>)
>>>
>>> Typically is reserved for index forcing, etc. so effectively the
>>> syntax you are trying is confusing the optimizer to think that
>>> (prevdate) is some sort of index forcing.
>>
>> I'd hope the optimizer is smart enough to realize in this case that
>> 'table' is a sub-query defining a derived table and therefore cannot
>> have any indexes, thus allowing the optimizer to parse the '(<name>)'
>> as the derived table's column list.
>>


Sherlock, Kevin [TeamSybase] Posted on 2009-08-11 18:51:53.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a8149aa.20f.1681692777@sybase.com>
Subject: Re: Column names of derived table
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81bdc9$4@forums-3-dub.sybase.com>
Date: 11 Aug 2009 11:51:53 -0700
X-Trace: forums-3-dub.sybase.com 1250016713 10.22.241.152 (11 Aug 2009 11:51:53 -0700)
X-Original-Trace: 11 Aug 2009 11:51:53 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28132
Article PK: 77374

Your syntax is wrong. Period. If you are used to it working in another
product, then you have to adjust your syntax for ASE.

<PD> wrote in message news:4a8149aa.20f.1681692777@sybase.com...
> To get the previous business day:
> Why does this not work? bug?
>
> select max(prevdate) from (
> select dateadd(dd
> ,datediff(dd, '0:0', getdate()) - number
> ,'0:0')
> from master.dbo.spt_values
> where type='P' and number between 1 and 10
> )t (prevdate)
> where datepart(dw,prevdate) not in (1,7)
> and not exists
> (select 1 from holidays where holiday=t.prevdate)
>
> on ASE 12.5.4 the process terminates
>
> However when I specify the column name prevdate inside the
> derived table then no problem e.g.
>
> select max(prevdate) from (
> select prevdate=
> dateadd(dd
> ,datediff(dd, '0:0', getdate()) - number
> ,'0:0')
> from master.dbo.spt_values
> where type='P' and number between 1 and 10
> )t
> where datepart(dw,prevdate) not in (1,7)
> and not exists
> (select 1 from holidays where holiday=t.prevdate)
>
>
> Sample holidays table:
> create table holidays (holiday datetime)
> insert into holidays select
> '20090101' union all select
> '20090810' union all select
> '20091225' union all select
> '20091226'


Sherlock, Kevin [TeamSybase] Posted on 2009-08-11 20:04:14.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a8149aa.20f.1681692777@sybase.com> <4a81bdc9$4@forums-3-dub.sybase.com>
Subject: Re: Column names of derived table
Lines: 52
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81cebe$3@forums-3-dub.sybase.com>
Date: 11 Aug 2009 13:04:14 -0700
X-Trace: forums-3-dub.sybase.com 1250021054 10.22.241.152 (11 Aug 2009 13:04:14 -0700)
X-Original-Trace: 11 Aug 2009 13:04:14 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28139
Article PK: 77383

Looks like i stand corrected by Mark's response that this is valid syntax.
I'd never seen it. I'll look that up... sorry.

"Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in
message news:4a81bdc9$4@forums-3-dub.sybase.com...
> Your syntax is wrong. Period. If you are used to it working in another
> product, then you have to adjust your syntax for ASE.
>
> <PD> wrote in message news:4a8149aa.20f.1681692777@sybase.com...
>> To get the previous business day:
>> Why does this not work? bug?
>>
>> select max(prevdate) from (
>> select dateadd(dd
>> ,datediff(dd, '0:0', getdate()) - number
>> ,'0:0')
>> from master.dbo.spt_values
>> where type='P' and number between 1 and 10
>> )t (prevdate)
>> where datepart(dw,prevdate) not in (1,7)
>> and not exists
>> (select 1 from holidays where holiday=t.prevdate)
>>
>> on ASE 12.5.4 the process terminates
>>
>> However when I specify the column name prevdate inside the
>> derived table then no problem e.g.
>>
>> select max(prevdate) from (
>> select prevdate=
>> dateadd(dd
>> ,datediff(dd, '0:0', getdate()) - number
>> ,'0:0')
>> from master.dbo.spt_values
>> where type='P' and number between 1 and 10
>> )t
>> where datepart(dw,prevdate) not in (1,7)
>> and not exists
>> (select 1 from holidays where holiday=t.prevdate)
>>
>>
>> Sample holidays table:
>> create table holidays (holiday datetime)
>> insert into holidays select
>> '20090101' union all select
>> '20090810' union all select
>> '20091225' union all select
>> '20091226'
>
>