Hi guys.  I come from a SQL Server background and am banging my head against the wall trying to write a relatively simple query.  My technique involves using derived columns such as:

select t1.exportid, t1.dailyscheduleid, (

select top 1 dailyscheduleid from ids_exportdailyschedule where exportid = t1.exportid and currentstatus = 2 order by statusbydate desc

) as LastCompletedID

from ids_exportdailyschedule t1

where exportid = '4ED32872-A3A8-43E8-8E98-15005E3F69C9'

This works fine with all other ANSI SQL, but Pervasive doesn't seem to like the SELECT in the derived column (LastCompletedID).  Is this not supported?

Thanks for your help.

Doug

What error or behavior are you getting? 

What version of PSQL are you using? 

Does this extremely simple query work (using DEMODATA)?
select name,(select top 1 first_name from person) as PersonName from class 

I'm getting an ODBC syntax error like "select t1.exportid, t1.dailyscheduleid, (select<<<???>>> top 1" .

That's off hte top of my head, but it is very similar.  I have already tried a very simple query like

SELECT name, (SELECT TOP 1 name FROM myTable) as anotherName FROM myTable

and get the same error.  I will try the same simple query later today when I can access the machine with Pervasive.

I don't know the exact version, but it is 8.x.  I can also find that out later, but I can't imagine such a simple query isn't supported in any version.  I have to have my syntax messed up.

Thanks so much for the quick response.  This is driving me crazy!

Doug

I just realized you're using V8.  The TOP command wasn't added until V9.  

It worked for me because I'm using v10.  Try without the TOP command.   

Actually, TOP is documented for v8, but the (SELECT...) subquery in the select list is not supported until PSQL 9.5.

I have a problem that I can't wrap my head around, in that case. Maybe someone will have an idea.

My table has the following structure:

MYJOBS 

JobNum int,

Sequence int,

Completed bit (or bool - whatever pSQL uses) 

The primary key is JobNum and Sequence 

It is populated with data like:

The query I want to use is:

SELECT t1.JobNum, t1.Sequence,

(SELECT TOP 1 Completed

FROM myJobs

WHERE JobNum = t1.JobNum

AND Sequence = t1.Sequence 

AND Completed = 0

ORDER BY Sequence) AS CurrentSequence

FROM myJobs t1

ORDER BY t1.JobNum, t1.Sequence 

It's a simple task.  In one query return the jobnum, sequence, and the first incompleted sequence related to the record.  Is it possible?

Thanks for your quick responses.  I'm surprised this forum is so active for a depricated product! 

Doug 

Does this accomplish what you want?

select jobnum, sequence, completed
from myjobs t1 where sequence =
   (select top 1 sequence from myjobs t2
    where t2.jobnum = t1.jobnum and t2.completed = 0)

For the data you show above, this query returns:

JobNum   Sequence   Completed
   1               3                0
   2               80              0

Unfortunately not.  I need all of the records returned with the first uncompleted sequence.  Your example is fine for just getting records that are the first uncompleted, but I'd have to join on that subquery, which I know isn't possible in v8.

Thanks for the example, though.

Doug

So, given the sample data from your earlier message, what are you expecting returned?

 Good question.  From this data:

I want this result:

 Does that clear things up?

Doug 

Ugly, but this seems to work:

select t1.jobnum, t1.sequence, t2.sequence
from myjobs t1, myjobs t2
where t1.jobnum = t2.jobnum and t2.sequence =
   (select top 1 t3.sequence from myjobs t3
    where t3.jobnum = t2.jobnum and t3.completed = 0)

Also, this works in v9.5 or later:

SELECT t1.JobNum, t1.Sequence,
   (SELECT TOP 1 Sequence FROM myJobs WHERE JobNum = t1.JobNum
    AND Completed = 0) AS CurrentSequence
FROM myJobs t1
ORDER BY t1.JobNum, t1.Sequence

 Linda,

I've never seen two tables in a FROM statement like that.  Also, I'm surprised the subquery will work in the WHERE clause but not the SELECT.  It's an interesting idea, and it seems like it should work.

In fact, I just tesed the method in T-SQL and it went well.  I'm going to give it a try later today against the pSQL DB and let you know how it goes.

FYI, this is for a report, so the efficiency isn't a big deal. 

Thanks so much for your help.

Doug 

My previous comment was in regard to your post at 12:55.  The method you use in your 1:10 post is what I wanted to do from the start, but as you said earlier, v8 doesn't support sub queries that way.

Now we are getting somewhere.  This got me close, but I needed an ORDER BY on t3.  When I attempted that, I got a syntax error.

Your example of t3: 

Linda84:

(select top 1 t3.sequence from myjobs t3
    where t3.jobnum = t2.jobnum and t3.completed = 0)

My testing version:

(select top 1 t3.sequence from myjobs t3
    where t3.jobnum = t2.jobnum and t3.completed = 0 order by t3.sequence)

 The server doesn't like the order by I added to the end, which is necessary to get the lowest sequence number.  Instead of using TOP and ORDER BY I could do a MIN(t3.sequence) in the SELECT.  In fact I just tested that method and it works.

 I'm still curious why my order by failed.  If you don't know, don't worry about spending too much time on it.  The end result is the same. 

Thank you so much for your insight.

Doug