Using subquery in select statement

[Hasan Haliciogl]

Simple SQLQUERY below works MSSQL and ORACLE.
-------
Select CustNum,
(Select Sum(TotalPaid) From Invoice)
From Customer
-------
But when I call this simple query from ODBC connection on Progress DB, ODBC driver generate "SQL syntax error".
Why doesn't work. Is this an SQL92 capability problem?

 

[Casper]

Progress SQL92 does not support subqueries in the select list.

You can only use subqueries like this:

SELECT table1.field, dummy.count FROM table1, (SELECT COUNT(*) FROM table2) AS dummy (count);

This is probably not what you are looking for because in mysql you can do stuff like:

SELECT *,(SELECT COUNT(*) FROM table2 WHERE table2.field1 = table1.id) AS count FROM table1 WHERE table1.field1 = 'value'

which seems much more usefull to me....

Regards,

Casper.

 

[Hasan Haliciogl]

I realy thank you Casper.
If you don't mind I have another question about same case.
I have to pass a parameter to subquery
----------------------------------
SELECT CustNum, sq.Tutar FROM Pub.Customer CUST,
(SELECT Sum(TotalPaid) FROM pub.Invoice INV WHERE INV.CustNum = CUST.CustNum ) AS sq (tutar)
----------------------------------
But when I use CUST in subquery driver generate an error "CUST.CustNum can not be found or is not specified for query".
Thanks for your interests.

 

[Casper]

yeah that is kind of the problem I ran into myself too, if you use a subquery after the from, then no join can be made to the first table. So not very usefull, I guess

Casper.

 

[4GLNewbie]

Sorry to ask, but cant u solve using a join ad a distinct?

Like

SELECT DISTINCT t1.CustNum, sum(t2.TotalPaid)
FROM cust t1 left join pub.Invoice t2 on t1.CustNum = t2.CustNum
GROUP BY t1.CustNum

It seems to me it does the same thing u wanted to do..

 

[Hasan Haliciogl]

Thanks,
but I must use the subquery to solved my problem.

 

[4GLNewbie]

Oh, i understand.

Tried something like

SELECT CustNum, (( SELECT Sum(TotalPaid) FROM pub.Invoice INV WHERE INV.CustNum = CustNum )) as Tutar
FROM
(
SELECT CustNum
FROM Pub.Customer CUST
)

?

I noticed that sometimes also brackets are a possible problem ( i recently found it in oracle ).
This is my last stupid suggestion, i promise :blush:

Bye

 

[Hasan Haliciogl]

Yes, the problem is "the sample code" that you send to me run in Oracle, MS SQL, Interbase,... but doesn't work on Progress.
I have tried to find a way to solve that problem for 3 weeks.

 

[4GLNewbie]

Sorry. I tried. I failed. I m not able to test the scripts on a progress db.
I hope someone helps you out, if can be made.

Bye

 

[Hasan Haliciogl]

I hope so.
Thank you.

 

[jhenslee]

Yes, the problem is "the sample code" that you send to me run in Oracle, MS SQL, Interbase,... but doesn't work on Progress.
I have tried to find a way to solve that problem for 3 weeks.

I'm having the same trouble using ODBC and Progress. I'm used to writing subqueries until the cows come hom in Oracle and SQL - just unable to get through issues as progress. Please advise if you have made any breakthroughs... Thank you!

 

[Beeb]

I'm also running into this same problem.
Does anybody has a suggestion how to solve this?

greetz,
Elise

 

[RealHeavyDude]

The basic issue is that the Progress OpenEdge database is not an SQL database in the first place. The native way to talk to it is the ABL, not SQL. Over time and releases they have added features to get closer, but even if Progress sales reps won't confirm this: You can't compare it to the other databases mentioned in this thread because their natural interface is SQL.

The strengths of the Progress OpenEdge database is not SQL - it just has some SQL capabilities. From my point of view that's completely okay because I would never trade the strengths of the ABL for any SQL capability that is missing.

I am afraid that you have to accept that the SQL capabilities of the Progress OpenEdge database are limited compared to other SQL databases.

Heavy Regards, RealHeavyDude.

 

[Beeb]

yes, i know ...
but since i have to make a query in crystal reports accessing a progress database, i don't have a choice...

grtz,
Elise

 

[RealHeavyDude]

Maybe you should not use the database as the data source in Crystal report. You could also access the AppServer via the .NET open client and build your reports on ADO.Net data sets ...

Heavy Regards, RealHeavyDude.

 

[Beeb]

thans for this one.
i fixed my problem using multiple queries but i'll try this for the next time

grtz,
Elise

 

[Lobo360]

I'm also running into this same problem.
Does anybody has a suggestion how to solve this?

greetz,
Elise

Hi, I think I was having the same problem and came accross this thread looking for a solution but in the end fixed the issue by canabilising some other scripts and so thought I'd try to be helpful by posting my solution.
I'm very much an SQL novice so firstly my apologies if I have interpreted your issue incorrectly and if my solution doesn't help.

To be clear, this is regarding writing Correlated Subqueries without getting an error that states the Subquery returns too many results.
My problem stems from being accustomed to querying Oracle databases in PL SQL and making the transition to a Business Intelligence solution using SQL Server 2008 and T-SQL.
In PL/SQL I would put the SubQuery in the Select statement of the main query. The Subquery contained details for the join to the main query and PL/SQL was able to handle this one to one join and display results, whereas T-SQL seems to run the Subquery without making the connection to the main query hence too many results returned:

SELECT
Field 1
,(Select Field2 from Table2 ...additional joins... where Table2.Field1 = Table1.Field1 and ...other criteria...)

FROM
Table1

WHERE
...
End

The solution for T-SQL was to move the correlated subquery to the From Statement:

SELECT
Field1
,Field2

FROM
Table1 Left Join
(SELECT Field1, Field2 FROM Table2 ...additional joins... WHERE ...other criteria...) as AltnameTable2 ON AltnameTable2.Field1 = table1.Field1

WHERE
...
End


The renaming of the Select statement (and optionally it's fields also) may not be required, I think it was just a requirement in my script as I was actually linking to the same table many times.

I hope this is of some assistance.

 

[JJames100]

I was having a similiar problem trying to run a pass-through query via Access to a Progress DB. I was able to run the following query after much trial/effort:

SELECT pub.client.client_id, pub.client.first_name, MyStaff.date_of_hire FROM Pub.Client inner join (select * from pub.staff) MyStaff
on pub.client.client_id = MyStaff.client_id

 

[JJames100]

Also got the following to work which contains a where clause (if I remove the part of the stmt starting with 'AND' at the end. Evidently, the 'is null' is causing it to have an error)

SELECT qryClientFromSC.client_id, Pub.pt_program.program_id, Pub.pt_program.program_seq, Pub.pt_program.admit_date, Pub.pt_program.discharge_date, Pub.address.address_reference_id, qryClientFromSC.name_title, qryClientFromSC.last_name, qryClientFromSC.first_name, qryClientFromSC.middle, qryClientFromSC.DOB, qryClientFromSC.gender, Pub.address.address_type, Pub.address.Line1, Pub.address.Line2, Pub.address.City, Pub.address.Zip, Pub.address.County_id, Pub.address.end_date, Pub.address.primary_address
FROM (SELECT client_id, last_name, first_name, middle, name_suffix, name_title, preferred_name, DOB, gender FROM Pub.client) qryClientFromSC INNER JOIN (Pub.pt_program INNER JOIN Pub.address ON Pub.pt_program.client_id = Pub.address.address_reference_id) ON qryClientFromSC.client_id = Pub.pt_program.client_id
WHERE ((qryClientFromSC.client_id)=*****) AND ((Pub.address.address_type)='Client') AND ((Pub.address.end_date) Is Null)

 

[JJames100]

Got the whole thing to work by removing the ) and its matching (, before the 'Is Null'