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 Cartesian Join ?

3 posts in General Discussion Last posting was on 2009-11-09 10:35:41.0Z
janis Posted on 2009-10-24 16:34:33.0Z
From: janis <janimie@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Multiple Cartesian Join ?
Date: Sat, 24 Oct 2009 09:34:33 -0700 (PDT)
Organization: http://groups.google.com
Lines: 32
Message-ID: <f30d7b4d-c430-4a85-abba-0db24b678c41@a6g2000vbp.googlegroups.com>
NNTP-Posting-Host: 195.241.168.22
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1256402073 3771 127.0.0.1 (24 Oct 2009 16:34:33 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 24 Oct 2009 16:34:33 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a6g2000vbp.googlegroups.com; posting-host=195.241.168.22; posting-account=T_ezLQoAAAC_FIZX9ZP3WTOqAwZ7JloT
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.5.21022; InfoPath.2; .NET CLR 3.5.30729; .NET CLR 3.0.30618),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!news.linkpendium.com!news.linkpendium.com!news.glorb.com!postnews.google.com!a6g2000vbp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28544
Article PK: 77786

Hi, I have a stored procedure , the results look like this

ID | fruit1 | fruit2 | veggie1 | veggie2 | candy1 | candy2
--------------------------------------------------------------------------------------
1 | apple | banana | soybean | potatoe | lollypop | gum

I used a cartesian join to transform the fruits colums into rows and
this is the result

ID | fruit
-----------
1 | apple
1 | banana

this is the query i used

select id,
CASE b
WHEN 1 THEN fruit1
ELSE fruit2
END fruit
FROM "DBA"."food"() i , (SELECT 1 b UNION ALL SELECT 2) C

Using this jjoin in stead of unions made my query much faster.
BUT I also want the vegetables and the candies next to the first colum
but that doesnt work because it doubles my rows ... :(
Could someone please help me getting this result:

ID | fruit | veggies | candy
-----------------------------------------------------------------
1 | apple | soybean | lollypop
1 | banana | potatoe | gum


"Mark A. Parsons" <iron_horse Posted on 2009-10-24 17:54:37.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: Multiple Cartesian Join ?
References: <f30d7b4d-c430-4a85-abba-0db24b678c41@a6g2000vbp.googlegroups.com>
In-Reply-To: <f30d7b4d-c430-4a85-abba-0db24b678c41@a6g2000vbp.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091014-0, 10/14/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae33f5d$1@forums-1-dub>
Date: 24 Oct 2009 10:54:37 -0700
X-Trace: forums-1-dub 1256406877 10.22.241.152 (24 Oct 2009 10:54:37 -0700)
X-Original-Trace: 24 Oct 2009 10:54:37 -0700, vip152.sybase.com
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28545
Article PK: 77785

1 - your current query generates syntax errors in Sybase ASE

2 - you didn't post your problematic query so it's hard to say what the problem is

In the meantime, I'd suggest adding additional CASE statements to your current query, eg, rewriting to meet ASE syntax:

========================
select * from food
go

id fruit1 fruit2 veggie1 veggie2 candy1 candy2
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 apple banana soybean potatoe lollypop gum
2 cherry grape yam corn chocolate licorice

(2 rows affected)

select id,
CASE b
WHEN 1 THEN fruit1
ELSE fruit2
END fruit,
CASE b
WHEN 1 THEN veggie1
ELSE veggie2
END veggie,
CASE b
WHEN 1 THEN candy1
ELSE candy2
END candy
FROM food i ,
(SELECT 1 b
UNION ALL
SELECT 2) C
order by id, 2
go

id fruit veggie candy
----------- ---------- ---------- ----------
1 apple soybean lollypop
1 banana potatoe gum
2 cherry yam chocolate
2 grape corn licorice

(4 rows affected)
========================

janis wrote:
> Hi, I have a stored procedure , the results look like this
>
> ID | fruit1 | fruit2 | veggie1 | veggie2 | candy1 | candy2
> --------------------------------------------------------------------------------------
> 1 | apple | banana | soybean | potatoe | lollypop | gum
>
> I used a cartesian join to transform the fruits colums into rows and
> this is the result
>
> ID | fruit
> -----------
> 1 | apple
> 1 | banana
>
> this is the query i used
>
> select id,
> CASE b
> WHEN 1 THEN fruit1
> ELSE fruit2
> END fruit
> FROM "DBA"."food"() i , (SELECT 1 b UNION ALL SELECT 2) C
>
> Using this jjoin in stead of unions made my query much faster.
> BUT I also want the vegetables and the candies next to the first colum
> but that doesnt work because it doubles my rows ... :(
> Could someone please help me getting this result:
>
> ID | fruit | veggies | candy
> -----------------------------------------------------------------
> 1 | apple | soybean | lollypop
> 1 | banana | potatoe | gum


janis Posted on 2009-11-09 10:35:41.0Z
From: janis <janimie@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Multiple Cartesian Join ?
Date: Mon, 9 Nov 2009 02:35:41 -0800 (PST)
Organization: http://groups.google.com
Lines: 1
Message-ID: <afc60665-3a53-4c92-8970-2b8b4d68e699@d21g2000yqn.googlegroups.com>
References: <f30d7b4d-c430-4a85-abba-0db24b678c41@a6g2000vbp.googlegroups.com> <4ae33f5d$1@forums-1-dub>
NNTP-Posting-Host: 80.126.11.242
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1257762942 1480 127.0.0.1 (9 Nov 2009 10:35:42 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 9 Nov 2009 10:35:42 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: d21g2000yqn.googlegroups.com; posting-host=80.126.11.242; posting-account=T_ezLQoAAAC_FIZX9ZP3WTOqAwZ7JloT
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; WOW64; Trident/4.0; SLCC1; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729),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!npeer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!d21g2000yqn.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28624
Article PK: 77866

thk u very much!