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.

Query Question in iAnywhere

8 posts in ,  General Discussion General Discussion Last posting was on 2006-12-21 19:48:27.0Z
abcd Posted on 2006-12-06 17:08:37.0Z
From: "abcd" <abcd@abcd.com>
Newsgroups: ianywhere.public.general,sybase.public.sqlanywhere.general
Subject: Query Question in iAnywhere
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: 67.42.198.185
X-Original-NNTP-Posting-Host: 67.42.198.185
Message-ID: <45770725$1@forums-1-dub>
Date: 6 Dec 2006 10:08:37 -0700
X-Trace: forums-1-dub 1165428517 67.42.198.185 (6 Dec 2006 10:08:37 -0700)
X-Original-Trace: 6 Dec 2006 10:08:37 -0700, 67.42.198.185
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5742 sybase.public.sqlanywhere.general:112209
Article PK: 1741

I am trying to write a query

My table has 2 columns

Writer Reader
-------------
John Mike
Mike Albert
John Smith


I want to fetch single column which will look like (i.e. basically all
unique values)

Users
-----
John
Mike
Albert
Smith

I have written a poor query as below...I know its not correct...How to
correct it

Select if(Writer <> Reader,Reader,null) From Table1

thanks in advance


Reg Domaratzki (iAnywhere Solutions) Posted on 2006-12-06 17:16:42.0Z
From: "Reg Domaratzki \(iAnywhere Solutions\)" <FirstName.LastName@ianywhere.com>
Newsgroups: ianywhere.public.general,sybase.public.sqlanywhere.general
References: <45770725$1@forums-1-dub>
Subject: Re: Query Question in iAnywhere
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: rdomarat-xp.sybase.com
X-Original-NNTP-Posting-Host: rdomarat-xp.sybase.com
Message-ID: <4577090a$1@forums-1-dub>
Date: 6 Dec 2006 10:16:42 -0700
X-Trace: forums-1-dub 1165429002 10.25.99.188 (6 Dec 2006 10:16:42 -0700)
X-Original-Trace: 6 Dec 2006 10:16:42 -0700, rdomarat-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5743 sybase.public.sqlanywhere.general:112212
Article PK: 1739

select writer from table1
union
select reader from table1

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"abcd" <abcd@abcd.com> wrote in message news:45770725$1@forums-1-dub...
>I am trying to write a query
>
> My table has 2 columns
>
> Writer Reader
> -------------
> John Mike
> Mike Albert
> John Smith
>
>
> I want to fetch single column which will look like (i.e. basically all
> unique values)
>
> Users
> -----
> John
> Mike
> Albert
> Smith
>
> I have written a poor query as below...I know its not correct...How to
> correct it
>
> Select if(Writer <> Reader,Reader,null) From Table1
>
> thanks in advance
>


abcd Posted on 2006-12-06 17:19:43.0Z
From: "abcd" <abcd@abcd.com>
Newsgroups: ianywhere.public.general,sybase.public.sqlanywhere.general
References: <45770725$1@forums-1-dub> <4577090a$1@forums-1-dub>
Subject: Re: Query Question in iAnywhere
Lines: 42
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: 67.42.198.185
X-Original-NNTP-Posting-Host: 67.42.198.185
Message-ID: <457709bf$1@forums-1-dub>
Date: 6 Dec 2006 10:19:43 -0700
X-Trace: forums-1-dub 1165429183 67.42.198.185 (6 Dec 2006 10:19:43 -0700)
X-Original-Trace: 6 Dec 2006 10:19:43 -0700, 67.42.198.185
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5744 sybase.public.sqlanywhere.general:112213
Article PK: 1742

Thanks Reg. Union Worls well. I was just curious to explore the if statement
in select clause

Thanks again

Reg Domaratzki (iAnywhere Solutions) wrote:
> select writer from table1
> union
> select reader from table1
>
>
> "abcd" <abcd@abcd.com> wrote in message
> news:45770725$1@forums-1-dub...
>> I am trying to write a query
>>
>> My table has 2 columns
>>
>> Writer Reader
>> -------------
>> John Mike
>> Mike Albert
>> John Smith
>>
>>
>> I want to fetch single column which will look like (i.e. basically
>> all unique values)
>>
>> Users
>> -----
>> John
>> Mike
>> Albert
>> Smith
>>
>> I have written a poor query as below...I know its not correct...How
>> to correct it
>>
>> Select if(Writer <> Reader,Reader,null) From Table1
>>
>> thanks in advance


Breck Carter [Team iAnywhere] Posted on 2006-12-06 20:29:38.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Query Question in iAnywhere
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <jc9en2lbgkg4ntsfn316b37r7mdvsrnis3@4ax.com>
References: <45770725$1@forums-1-dub> <4577090a$1@forums-1-dub> <457709bf$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 199.82.235.215
X-Original-NNTP-Posting-Host: 199.82.235.215
Date: 6 Dec 2006 13:29:38 -0700
X-Trace: forums-1-dub 1165440578 199.82.235.215 (6 Dec 2006 13:29:38 -0700)
X-Original-Trace: 6 Dec 2006 13:29:38 -0700, 199.82.235.215
Lines: 20
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5747
Article PK: 1746


On 6 Dec 2006 10:19:43 -0700, "abcd" <abcd@abcd.com> wrote:

>Thanks Reg. Union Worls well. I was just curious to explore the if statement
>in select clause

It's hard to get a result set of 4 rows by just selecting FROM a
single table that only has 3 rows.

Everyone's been remarkably consistent suggesting UNION, and I can't
think of an alternative...

...but the IF expression is very cool, don't give up on it :)

Breck
--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Ivan T. Bowman Posted on 2006-12-21 18:58:35.0Z
From: "Ivan T. Bowman" <ibowman@ianywhere.NOSPAM.com>
Newsgroups: ianywhere.public.general
References: <45770725$1@forums-1-dub> <4577090a$1@forums-1-dub> <457709bf$1@forums-1-dub> <jc9en2lbgkg4ntsfn316b37r7mdvsrnis3@4ax.com>
Subject: Re: Query Question in iAnywhere
Lines: 70
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: vpn-concord-062.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-062.sybase.com
Message-ID: <458ae76b$1@forums-1-dub>
Date: 21 Dec 2006 11:58:35 -0700
X-Trace: forums-1-dub 1166731115 158.159.8.62 (21 Dec 2006 11:58:35 -0700)
X-Original-Trace: 21 Dec 2006 11:58:35 -0700, vpn-concord-062.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5773
Article PK: 1771

The UNION solutions seem like the best because you asked to remove
duplicates. If, instead, you wanted to preserve duplicates for some reason,
you could use the following:

select argn( row_num, Writer, Reader )
from TWriterReader, sa_rowgenerator(1,2)

The above uses a join (well, cartesian product) to duplicate each of the
TWriterReader rows 2 times.

You might be able to make that a little faster with some trickery to force
the TWriterReader table to be read scanned only one time:

select argn( row_num, Writer, Reader )
from TWriterReader left outer join sa_rowgenerator(1,2) on 1=1

and you could also prevent generating a duplicate if a single row had the
same Writer and Reader:

select argn( row_num, Writer, Reader )
from TWriterReader left outer join sa_rowgenerator(1,2) on 1 = row_num or
Reader <> Writer

although this could still generate duplicates because of a Reader on one row
matching a Writer on another, or two Writers matching, etc. So, a final
version that matches what you asked for is:

select distinct argn( row_num, Writer, Reader )
from TWriterReader left outer join sa_rowgenerator(1,2) on 1 = row_num or
Reader <> Writer

I used ARGN() above instead of IF because it's a little tidier, and also it
can generalize easily to more columns if that strikes your fancy.

There might be cases where this JOIN based approach is better than a UNION
because there is some other expensive stuff in your query that you would
need to duplicate in the branches of the union, but I would probably
consider the JOIN overly complicated if there weren't a good reason for it.
In this case, that was trying to show Breck a non-UNION alternative ;-).

--
Ivan T. Bowman
SQLAnywhere Research and Development

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:jc9en2lbgkg4ntsfn316b37r7mdvsrnis3@4ax.com...
> On 6 Dec 2006 10:19:43 -0700, "abcd" <abcd@abcd.com> wrote:
>
>>Thanks Reg. Union Worls well. I was just curious to explore the if
>>statement
>>in select clause
>
> It's hard to get a result set of 4 rows by just selecting FROM a
> single table that only has 3 rows.
>
> Everyone's been remarkably consistent suggesting UNION, and I can't
> think of an alternative...
>
> ...but the IF expression is very cool, don't give up on it :)
>
> Breck
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Breck Carter [Team iAnywhere] Posted on 2006-12-21 19:48:27.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Query Question in iAnywhere
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <usrlo29tki29qefcf68s0qb78gbvajf9bi@4ax.com>
References: <45770725$1@forums-1-dub> <4577090a$1@forums-1-dub> <457709bf$1@forums-1-dub> <jc9en2lbgkg4ntsfn316b37r7mdvsrnis3@4ax.com> <458ae76b$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 21 Dec 2006 12:48:27 -0700
X-Trace: forums-1-dub 1166734107 64.7.134.118 (21 Dec 2006 12:48:27 -0700)
X-Original-Trace: 21 Dec 2006 12:48:27 -0700, bcarter.sentex.ca
Lines: 18
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5774
Article PK: 1769

On 21 Dec 2006 11:58:35 -0700, "Ivan T. Bowman"

<ibowman@ianywhere.NOSPAM.com> wrote:

>In this case, that was trying to show Breck a non-UNION alternative ;-)

I like to think it took you over two weeks to come up with that
alternative... I like to think that, yes indeedy... I also like to
think I'm getting a turbo Porsche for Christmas :)

Breck


--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


"Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g Posted on 2006-12-06 17:41:56.0Z
From: "Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com>
Organization: iAnywhere Solutions
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general,sybase.public.sqlanywhere.general
Subject: Re: Query Question in iAnywhere
References: <45770725$1@forums-1-dub>
In-Reply-To: <45770725$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vpneast-client-028.sybase.com
X-Original-NNTP-Posting-Host: vpneast-client-028.sybase.com
Message-ID: <45770ef4$1@forums-1-dub>
Date: 6 Dec 2006 10:41:56 -0700
X-Trace: forums-1-dub 1165430516 10.18.242.35 (6 Dec 2006 10:41:56 -0700)
X-Original-Trace: 6 Dec 2006 10:41:56 -0700, vpneast-client-028.sybase.com
Lines: 64
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5746 sybase.public.sqlanywhere.general:112220
Article PK: 1743

A UNION should solve this. Lets assume your table name is t

SELECT Writer FROM t UNION SELECT Reader FROM t



BTW Please refrain from posting across several forums.

abcd wrote:
> I am trying to write a query
>
> My table has 2 columns
>
> Writer Reader
> -------------
> John Mike
> Mike Albert
> John Smith
>
>
> I want to fetch single column which will look like (i.e. basically all
> unique values)
>
> Users
> -----
> John
> Mike
> Albert
> Smith
>
> I have written a poor query as below...I know its not correct...How to
> correct it
>
> Select if(Writer <> Reader,Reader,null) From Table1
>
> thanks in advance
>
>

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

*****************************************************************************

Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition =and try out the market-leading database for mobile, embedded
and small to medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

*****************************************************************************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

*****************************************************************************


Shuchit Posted on 2006-12-06 17:19:56.0Z
Newsgroups: ianywhere.public.general,sybase.public.sqlanywhere.general
Subject: Re: Query Question in iAnywhere
From: Shuchit <me@privacy.net>
References: <45770725$1@forums-1-dub>
Message-ID: <Xns98918604C2FD6svelkarprivacynet@127.0.0.1>
User-Agent: Xnews/2006.08.24 Hamster/2.1.0.0
NNTP-Posting-Host: 216.16.231.204
X-Original-NNTP-Posting-Host: 216.16.231.204
Date: 6 Dec 2006 10:19:56 -0700
X-Trace: forums-1-dub 1165429196 216.16.231.204 (6 Dec 2006 10:19:56 -0700)
X-Original-Trace: 6 Dec 2006 10:19:56 -0700, 216.16.231.204
Lines: 31
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5745 sybase.public.sqlanywhere.general:112214
Article PK: 1744


"abcd" <abcd@abcd.com> wrote in news:45770725$1@forums-1-dub:

> I am trying to write a query
>
> My table has 2 columns
>
> Writer Reader
> -------------
> John Mike
> Mike Albert
> John Smith
>
>
> I want to fetch single column which will look like (i.e. basically all
> unique values)
>
> Users
> -----
> John
> Mike
> Albert
> Smith
>

The following works in 9.0.2 of ASA, what version are you using

SELECT Writers FROM table1
UNION DISTINCT
SELECT Readers FROM table1;

Shuchit