June 12, 2002 at 2:38 pm
We all know we can write a Select top 10 field1,field2 from tablename where field=value. But I have a real problem with top. It is not supported by unisql(unidata sql) and I need something to function similar to it. I need unisql to connect to a unidata database system. I was wondering if someone had any wild ideas on how to tackle this?
Matt
June 12, 2002 at 3:10 pm
Hello, this problem I would within stored procedure using intermediate table.
For example:
declare @t table(MyID int identity(1,1),
CategoryID int,
CategoryName varchar(50)
)
declare @top int
set @top = 2
insert @t
select CategoryID, CategoryName
from Categories
order by CategoryName DESC
select CategoryID, CategoryName from @t
where MyID <= @top
June 13, 2002 at 1:16 pm
hmm that won't work well because first of all there is no tsql functionality and secondly I would just return all the rows before creating a temp table thats just more work the server has to do. Basically I need to do it with a sql statement.
Matt
June 13, 2002 at 1:36 pm
SQL Server supports set rowcount, the older way of doing Top N. Other than that, maybe a cursor that will loop x times and insert the keys in a table, then use that table for the join?
Andy
June 13, 2002 at 2:02 pm
I think you may want to use a cursor here where you get the data and then grab a couple items only. Downside is there could be a large result set. Can you reduce the result set from the query some? If so, then a cursor might be best. Or insert into a temp table, select TOP, drop temp table.
Steve Jones
June 13, 2002 at 7:45 pm
Ok folks, he is talking about another server. Which company version is this? (IBM??) I have never used but I have documentation from IBM on there stuff and you could if you have a unique data field may be able to pull this off sorta like so (but I am looking for the right syntax).
SELECT * FROM tblX AS ox WHERE (SELECT COUNT(uniquecol) FROM tblX AS ix WHERE ix.uniquecol < ox.uniquecol) < 10
But due to structure differences I am not sure this will work and you may not be using IBM UniData SQL.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 14, 2002 at 8:06 am
Thanks for all your guys help but none of it works. I looked into all your ideas but I can't use cursors there is no set command for rowcount (or something similar to it). The best example was Antares686 gave but it kept throwing a syntax error. The funny thing about that way is if you put the 10< before your second select it would run for 7 min returning no rows so we killed it. But if you put the 10 part at the end it would throw a syntax error pointing at the t in the second select. I am using Unidata SQL and I am using IBM/Informix version of the SQL syntax for Unidata. I checked the documenation and their tech support is so lame I was laughing at them (not over the phone of course). They basically told me it could not be done and to use > < Between, etc to limit the number of rows it returns. I was thinking maybe some how to cashe the data per user through ASP.NET and grab the data from there since they *could* in fact get more than 10 rows since it was going to be dynamic. Unidata sql sucks if you want my honest opinon. :]
Well if there are any other thoughts, I would glady hear of them.
Matt
June 14, 2002 at 11:44 am
quote:
The funny thing about that way is if you put the 10< before your second select it would run for 7 min returning no rows so we killed it
Sorry I have to go by the docs and they are all but useless. However from the statement depending on how many records are in your table it may have been working. Try 10 > (SUBQUERY) since you want records 0-9 and 10< would return 11+. No guarantees but from the slowness it may be just have been backwards.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 14, 2002 at 1:40 pm
I gave that a shot and well it runs like crap. still returns nothing after 7 minutes. Something tells me that the unioledb provider does not like subqueries much. there are only 1200 records in the file so it should run just fine. I tried the same query off of sql server with 400,000 records and it was lightning fast. I was looking through stuff on sql server and saw the linked server thing. Would that help me any or would that produce the same problem. I am really just a Vb programmer but I have the task of working with data all over the place and I have to try and administer sql server too at the same time. I was either thinking of something with linked servers(not exactly sure what they are used for), schedule the data to be transferred to sql server and run off of that, or use some caching scheme in asp.net to handle the data directly.
I am open to other thoughts or suggestions since I am still trying to figure out the best course of action on the giving cirimstances.
Matt
June 14, 2002 at 8:36 pm
Found it.
The keyword is SAMPLE
quote:
SAMPLE n displays only n records (default is 10).
So example
SELECT * FROM tblX SAMPLE 5
May be slightly off but you should now be able to get support to give you help (this time without needing to laugh).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 17, 2002 at 10:31 am
I really hate to bust your bubble but your looking at the wrong documentation :] The documentation we need is is Unidata SQL 5.2.
Link to documentation: http://www.informix.com/answers/english/docs/52unidata/Sqlu.pdf
For some reason unidata has 2 query languages uniquery and unidata sql I can't tell you much about them except uniquery has built in support for multivalue data while unidata sql has to nest/unnest the data. For sh!ts and giggles I tried using the sample and all I got was a syntax error and tried different variations of it and it does not like that word Sample. I might be screwed. I do have a backup plan in any case to store the data in cache if I can get it working.
Matt
June 17, 2002 at 11:13 am
Looks that way. Sorry about my confusion hard to understand any of that documentation (not friendly at all). Without being able to play with myself with this type server I am jsut researching. If I come across anything UniData SQL related to this I will pass along.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 20, 2002 at 3:43 am
An example of the "relational way":
use northwind
select productid, productname, unitprice
from products o
where 10 > (select count(*)
from products i
where o.unitprice < i.unitprice)
order by unitprice desc
is almost equivalent with
SELECT TOP 10 productid, productname, pnitprice
FROM products
ORDER BY unitprice DESC
June 20, 2002 at 9:04 am
I'm not sure if you have to do this to lots of different tables but one work around would be to look into an equivalent to MS SQL's Identity property thus creating a fake "row id." Then use that in your WHERE clause. If no equivalent identity property exists, you can still sequence a field by using "update myTable set myIdentityField = (select max(myIdentityField) + 1 from myTable)" but this typically sucks because it's slow and you'd have to decide when you'd run this, e.g. in an update trigger.
Assuming unidata sql has ODBC drivers, you may be able to set up a link to the unisql database, create views to the tables on that database (for ease of use) and use MS functionality via the link rather than unisql.
Another idea I can think of is to use DTS to pull the table over to MS SQL (since it sounds to be pretty small) and perform your queries there. Don't know if that's a viable option, depends on if you need real-time access to the data or not.
June 20, 2002 at 11:48 am
Micheal:
Your way is the same as Antares686's way. IT runs way to slow. 7 minutes and returns no records. SubQueries run crap ass slow in unidata sql along with joins but I'll get to that in a minute.
Dean: Yeah I was gonna do something similar to your link server. I tried setting one up and I get no tables listed. So I am assuming I either did something wrong or the OLE DB unisql component does not work with it (wouldn't surprise me at this point. For security I chose the last option and put in the username/password to log in to the unidata server. Under general I was not too sure what I was to put for product name so I put in the datasource as the product name. The DTS option I may do *if* my application runs into a bottleneck.
If anyone can give me any more feedback on linked servers, or let me know that I am doing everything correctly in order for it to work since I never really used it before and I am a ASP.NET/db programmer/db admin so I kinda have to spread myself out over all 3.
Matt
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply