• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2164
  • Last Modified:

Join subqueries in Sybase

Dear All,

I know SQL reasonably well, but am working with a Sybase server for the first time. I am wondering if there are aspects of it that are different than MySQL or Access.

I am trying to execute something along the lines of:
SELECT * FROM (SELECT 0, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=0 GROUP BY year(tmstmp)) as t1 INNER JOIN (SELECT 1, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=1 GROUP BY year(tmstmp)) as t2 ON t1.yr=t2.yr

It is starting to look like a SELECT subquery can't be the used in the FROM clause. Is that the case in Sybase and why? If so, how can I join manipulated tables?

Thanks in advance.
0
saulius88
Asked:
saulius88
  • 8
  • 7
  • 4
  • +1
1 Solution
 
HainKurtSr. System AnalystCommented:
try this
SELECT t1.*, t2.* 
FROM 
(SELECT 0, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=0 GROUP BY year(tmstmp)) as t1, 
(SELECT 1, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=1 GROUP BY year(tmstmp)) as t2 
where t1.yr=t2.yr

Open in new window

0
 
HainKurtSr. System AnalystCommented:
or this
SELECT t1.*, t2.* 
FROM 
(SELECT 0, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=0 GROUP BY year(tmstmp)) t1, 
(SELECT 1, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=1 GROUP BY year(tmstmp)) t2 
where t1.yr=t2.yr

or

SELECT *
FROM 
(SELECT 0, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=0 GROUP BY year(tmstmp)) t1, 
(SELECT 1, count(broker_id), year(tmstmp) as yr FROM orders WHERE broker_id=1 GROUP BY year(tmstmp)) t2 
where t1.yr=t2.yr

Open in new window

0
 
saulius88Author Commented:
Thanks HainKurt,

Intuitively it makes sense.. But I'm still getting the same error "Incorrect syntax near the keyword 'SELECT'.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
HainKurtSr. System AnalystCommented:
what abaout this?

select * from orders

;)

and also this

SELECT 0 as whatisthis, count(broker_id) count_broker, year(tmstmp) as yr FROM orders WHERE broker_id=0 GROUP BY year(tmstmp))
0
 
HainKurtSr. System AnalystCommented:
missedd one "as" and removed extra ")"

SELECT 0 as whatisthis, count(broker_id) as count_broker, year(tmstmp) as yr
FROM orders
WHERE broker_id=0
GROUP BY year(tmstmp)
0
 
saulius88Author Commented:
Thanks, but that's not quite it - one subquery returns IDs that are 0, the other one, all records that are not.

Why are could possibly the subqueries in the FROM clause be problematic?
0
 
Jan FranekCommented:
What version of Sybase dabatase are you using ?
0
 
saulius88Author Commented:
Hi Jan,

Sybase Adaptive Server Enterprise/12.5.0.3 is the version I'm on.
0
 
Jan FranekCommented:
Derived tables (aka subselects in place of tables) were introduced in ASE 12.5.1 - so your version doesn't support them.
0
 
Jan FranekCommented:
Possible workaround for your version:

By the way - ASE 12.5 is very old version - support ends in 9 days - on 31.12.2009.
SELECT 0 as col1, count(broker_id) as col2, year(tmstmp) as yr into #temp1 FROM orders WHERE broker_id=0 GROUP BY year(tmstmp)
SELECT 1 as col1, count(broker_id) as col2, year(tmstmp) as yr into #temp2 FROM orders WHERE broker_id=1 GROUP BY year(tmstmp)
SELECT * FROM #temp1 t1 INNER JOIN #temp2 t2 ON t1.yr=t2.yr

Open in new window

0
 
saulius88Author Commented:
Oh, ouch... That is very inconvenient for me..

Jan, any thoughts on how one might attack the challenge I have above - i.e. joining subsets of the same (or two distinct, for that matter) tables?

Derived tables just seem like such a basic necessity to me - would you by any chance be aware of any ingenious ways that Sybase programmers used to get around the need of joining refined tables?
0
 
saulius88Author Commented:
Thanks - didn't notice your second post before submitting mine.

One follow-on: I am using Rapid SQL to process my queries - I presume the #temp* type of tables are dropped as soon as the last query is executed (i.e. do not get stored in the database?). I will be dealing with fairly sizable datasets, and refining the queries will be a bit of a trial-and-error-like exercise, so I'm worried about creating a bunch of redundant data by SELECTing INTO.

0
 
Jan FranekCommented:
OK, now that I really looked at your query - you don't need joins at all. This should give you the same result.
SELECT 0, sum( case when broker_id = 0 then 1 else 0 end), year(tmstmp), 1, sum( case when broker_id = 1 then 1 else 0 end), year(tmstmp)
FROM orders 
WHERE broker_id between 0 and 1
GROUP BY year(tmstmp)

Open in new window

0
 
SharathData EngineerCommented:
what about this?
SELECT * 
  FROM (SELECT 0 as col, count(broker_id) as cnt, year(tmstmp) as yr FROM orders WHERE broker_id=0 GROUP BY year(tmstmp)) as t1 
 INNER JOIN (SELECT 1 as col, count(broker_id) as cnt, year(tmstmp) as yr FROM orders WHERE broker_id=1 GROUP BY year(tmstmp)) as t2 
    ON t1.yr=t2.yr

Open in new window

0
 
SharathData EngineerCommented:
Jan_Franek - Your query will give result in one record not two records.

I am not sure if the saulius88 is looking for that.
0
 
Jan FranekCommented:
#temp tables are created in tempdb and they persist only inside connection - they are droped as soon as you disconnect (but of course, you can drop them using drop table command as well)
0
 
Jan FranekCommented:
Hi Sharath_123

> I am not sure if the saulius88 is looking for that.

I'm not sure too of course :-) However, original piece of code does the same.

If you want to get more records, you have to use UNION and not JOIN.
0
 
SharathData EngineerCommented:
yeah... you are correct.
0
 
saulius88Author Commented:
Sarath_123 - thanks for the suggestion, but I would have to rely on the derived tables to be able to use it - as Jan_Franek points out my antiquated version will not support a "SELECT" in the FROM clause.

Jan_Franek - thanks again for the suggestion. Your solution would work, but I was using my example as more of a "concept" testing one - in some instances I will be joining separate tables that I would like to pre-filter (i.e. access them as derived tables).

The solution you had first - the one with #temp - could you outline in a few words how those temp tables are processed by the server? They seem to me like quite a suitable substitute for subqueries, for my purposes anyway.
0
 
saulius88Author Commented:
Jan_Franek,

Thanks again - it is all starting to come together. One last question if you would not mind addressing it (if you can't, its fine, I understand it falls beyond the scope of the question - the points, and my thanks, are already yours).

I am using Rapid SQL to execute my queries. The error of "The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database".

I presume this is due to the way I try to execute the three queries. I tried them one by one, but I guess the connection gets reestablished every time that the query is run (and hence the temp tables are dropped).

Can you think of a way one might work with that? Perhaps there is a way of making the connection persist for a little while?
0
 
Jan FranekCommented:
Sorry, I don't know how Rapid SQL exactly works.

But the error message says, you can't create temp table inside transaction. So it seems like Rapid SQL opens transaction and then executes your code inside transaction. It that is true, it will be hard to use temp tables via Rapid SQL. It that case I would recommend AseIsql - http://dm.char.com.ua/ase/aseisql.htm

Here is some info about temporary tables on Sybase ASE - http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug309.htm
0
 
saulius88Author Commented:
Excellent, thanks for all the help.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 8
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now