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.

Re: Query question

3 posts in General Discussion Last posting was on 2005-05-03 17:47:57.0Z
Michael Posted on 2005-05-03 15:32:10.0Z
Sender: 118d.42761451.1804289383@sybase.com
From: Michael
Newsgroups: ianywhere.public.general
Subject: Re: Query question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <42779975.278b.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 3 May 2005 08:32:05 -0700, 10.22.241.42
Lines: 61
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 May 2005 08:32:08 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 May 2005 08:32:10 -0700
X-Trace: forums-1-dub 1115134330 10.22.108.75 (3 May 2005 08:32:10 -0700)
X-Original-Trace: 3 May 2005 08:32:10 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4436
Article PK: 8246

Thanks for help Brecks.
But I only have fields : Trx_Date DateTime, and Status
Varchar(3).
So your syntax won't run since it need pkey.

Do you have solution for my table's structure ?


CREATE TABLE status (
pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT,
status_time TIME NOT NULL,
status VARCHAR ( 4 ) NOT NULL,
PRIMARY KEY ( pkey ) );

INSERT status ( status_time, status ) VALUES ( '9:30', 'Up'
);
INSERT status ( status_time, status ) VALUES ( '9:31',
'Down' );
INSERT status ( status_time, status ) VALUES ( '9:35',
'Down' );
INSERT status ( status_time, status ) VALUES ( '9:38', 'Up'
);
INSERT status ( status_time, status ) VALUES ( '9:41',
'Down' );
INSERT status ( status_time, status ) VALUES ( '9:55', 'Up'
);
INSERT status ( status_time, status ) VALUES ( '9:59', 'Up'
);
COMMIT;

SELECT this.*
FROM status AS this
LEFT OUTER JOIN status AS prior
ON prior.pkey = this.pkey - 1
LEFT OUTER JOIN status AS next
ON next.pkey = this.pkey + 1
WHERE this.status = prior.status
OR this.status = next.status
ORDER BY this.status_time;

> I need a query help to get invalid down/up status from
> records :
>
> 9:30 Up
> 9:31 Down
> 9:35 Down
> 9:38 Up
> 9:41 Down
> 9:55 Up
> 9:59 Up
>
> And here is the expecting result :
> 9:31 Down
> 9:35 Down
> 9:55 Up
> 9:59 Up
>
> Thanks for help.
>
>
> Michael


Breck Carter Posted on 2005-05-03 16:45:46.0Z
Sender: 288a.4277a6c6.1804289383@sybase.com
From: Breck Carter
Newsgroups: ianywhere.public.general
Subject: Re: Query question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4277aab4.28ee.1681692777@sybase.com>
References: <42779975.278b.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 3 May 2005 09:45:40 -0700, 10.22.241.42
Lines: 63
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 May 2005 09:45:43 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 May 2005 09:45:46 -0700
X-Trace: forums-1-dub 1115138746 10.22.108.75 (3 May 2005 09:45:46 -0700)
X-Original-Trace: 3 May 2005 09:45:46 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4437
Article PK: 8245

What is the primary key?

> Thanks for help Brecks.
> But I only have fields : Trx_Date DateTime, and Status
> Varchar(3).
> So your syntax won't run since it need pkey.
>
> Do you have solution for my table's structure ?
>
>
> CREATE TABLE status (
> pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT,
> status_time TIME NOT NULL,
> status VARCHAR ( 4 ) NOT NULL,
> PRIMARY KEY ( pkey ) );
>
> INSERT status ( status_time, status ) VALUES ( '9:30',
> 'Up' );
> INSERT status ( status_time, status ) VALUES ( '9:31',
> 'Down' );
> INSERT status ( status_time, status ) VALUES ( '9:35',
> 'Down' );
> INSERT status ( status_time, status ) VALUES ( '9:38',
> 'Up' );
> INSERT status ( status_time, status ) VALUES ( '9:41',
> 'Down' );
> INSERT status ( status_time, status ) VALUES ( '9:55',
> 'Up' );
> INSERT status ( status_time, status ) VALUES ( '9:59',
> 'Up' );
> COMMIT;
>
> SELECT this.*
> FROM status AS this
> LEFT OUTER JOIN status AS prior
> ON prior.pkey = this.pkey - 1
> LEFT OUTER JOIN status AS next
> ON next.pkey = this.pkey + 1
> WHERE this.status = prior.status
> OR this.status = next.status
> ORDER BY this.status_time;
>
> > I need a query help to get invalid down/up status from
> > records :
> >
> > 9:30 Up
> > 9:31 Down
> > 9:35 Down
> > 9:38 Up
> > 9:41 Down
> > 9:55 Up
> > 9:59 Up
> >
> > And here is the expecting result :
> > 9:31 Down
> > 9:35 Down
> > 9:55 Up
> > 9:59 Up
> >
> > Thanks for help.
> >
> >
> > Michael


Michael Posted on 2005-05-03 17:47:57.0Z
Sender: 118d.42761451.1804289383@sybase.com
From: Michael
Newsgroups: ianywhere.public.general
Subject: Re: Query question
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4277b948.29e5.1681692777@sybase.com>
References: <4277aab4.28ee.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 3 May 2005 10:47:52 -0700, 10.22.241.42
Lines: 67
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 May 2005 10:47:54 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 May 2005 10:47:57 -0700
X-Trace: forums-1-dub 1115142477 10.22.108.75 (3 May 2005 10:47:57 -0700)
X-Original-Trace: 3 May 2005 10:47:57 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4438
Article PK: 8251

The primary key is trx_date and status.

Michael

> What is the primary key?
>
> > Thanks for help Brecks.
> > But I only have fields : Trx_Date DateTime, and Status
> > Varchar(3).
> > So your syntax won't run since it need pkey.
> >
> > Do you have solution for my table's structure ?
> >
> >
> > CREATE TABLE status (
> > pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT,
> > status_time TIME NOT NULL,
> > status VARCHAR ( 4 ) NOT NULL,
> > PRIMARY KEY ( pkey ) );
> >
> > INSERT status ( status_time, status ) VALUES ( '9:30',
> > 'Up' );
> > INSERT status ( status_time, status ) VALUES ( '9:31',
> > 'Down' );
> > INSERT status ( status_time, status ) VALUES ( '9:35',
> > 'Down' );
> > INSERT status ( status_time, status ) VALUES ( '9:38',
> > 'Up' );
> > INSERT status ( status_time, status ) VALUES ( '9:41',
> > 'Down' );
> > INSERT status ( status_time, status ) VALUES ( '9:55',
> > 'Up' );
> > INSERT status ( status_time, status ) VALUES ( '9:59',
> > 'Up' );
> > COMMIT;
> >
> > SELECT this.*
> > FROM status AS this
> > LEFT OUTER JOIN status AS prior
> > ON prior.pkey = this.pkey - 1
> > LEFT OUTER JOIN status AS next
> > ON next.pkey = this.pkey + 1
> > WHERE this.status = prior.status
> > OR this.status = next.status
> > ORDER BY this.status_time;
> >
> > > I need a query help to get invalid down/up status from
> > > records :
> > >
> > > 9:30 Up
> > > 9:31 Down
> > > 9:35 Down
> > > 9:38 Up
> > > 9:41 Down
> > > 9:55 Up
> > > 9:59 Up
> > >
> > > And here is the expecting result :
> > > 9:31 Down
> > > 9:35 Down
> > > 9:55 Up
> > > 9:59 Up
> > >
> > > Thanks for help.
> > >
> > >
> > > Michael