SQL syntax in Progress problem

[PenelopeC]

Hi,
So far, I find PROGRESS to be the most frustrating, obtuse database program ever!

But then again I haven't been around that much

I'm trying to group by

LEFT(a.company_name,3), sp.qty, p.name

Progress doesn't like my syntax, however its not cooperating in giving me any idea why.

Here's the whole thing...

Select LEFT(a.company_name,3)AS AU, sp.qty,p.name,
SUM(sp.unit_price)
From PUB.Subscriber u INNER JOIN PUB.Account a ON u.uid_account = a.uid_account INNER JOIN PUB.Subscriber_Services ss
ON u.uid_subscriber = ss.uid_subscriber INNER JOIN PUB.Subscriber_products sp ON ss.uid_subscriber_services = sp.uid_subscriber_services INNER JOIN PUB.Products p
ON sp.uid_products = p.uid_products
WHERE ss.svc_status LIKE 'A'
GROUP BY LEFT(a.company_name,3),sp.qty,p.name


I've tried all kinds of ways on this...is there some place one could point me to on the finer points of trying to write SQL select statements in Progress' SQL Explorer?

BTW: thanks for sticking it through the rant up there!



PenelopeC
~~~>-/O~~~~~swimming right along

 

[goodguy86]

Hi,

How about this?
===================

Select LEFT(a.company_name,3)AS AU, sp.qty,p.name, SUM(sp.unit_price)
From PUB.Subscriber u
INNER JOIN PUB.Account a ON u.uid_account = a.uid_account
INNER JOIN PUB.Subscriber_Services ss
ON u.uid_subscriber = ss.uid_subscriber AND ss.svc_status LIKE 'A'
INNER JOIN PUB.Subscriber_products sp ON ss.uid_subscriber_services = sp.uid_subscriber_services
INNER JOIN PUB.Products p ON sp.uid_products = p.uid_products
GROUP BY LEFT(a.company_name,3),sp.qty,p.name


Good Luck..

 

[PenelopeC]

Nope, that's not it. I can get results from the query as long as I don't try to group by LEFT(a.company_name,3)

Thanks anyways

PenelopeC
~~~>-/O~~~~~swimming right along

 

[harebrain]

The SQL-92 Guide specifies that the GROUP BY arguments are columns (as opposed to the SELECT clause, where the arguments may be expressions.) I wouldn't swear to it, but it could be complaining about the scalar function (LEFT) in your GROUP BY clause.

Have you tried the query without the LEFT?

HTH,
David

 

[PenelopeC]

Harebrain - Hi!

Yes,it works without the LEFT. I was afraid of something like that.

Is the SQL-92 Guide something of Progress'?

I am having so much trouble with this program its not even funny .

I'm also trying to link tables in Access. Seemed to work one day two weeks ago, then I opened the next day and the ODBC connection fails and I can't for the life of me recreate the time that it did work!

PenelopeC
~~~>-/O~~~~~swimming right along

 

[harebrain]

Hi Penelope,

Yes, the SQL-92 Guide is (I think) on your documentation disk, if you have one handy. Otherwise you can download it from the Progress site (filename is s92.pdf.)

As far as what you can do with SQL in Progress, I don't claim any special knowledge, I just try to interpret what I read, and I tend to be very literal.

I had my own adventure with Access-linked tables this week. I linked some Progress tables into a new (empty) Access database to get a quick ER diagram, which was fine. But I didn't have permission to look at any of the data in the tables (and I knew that.) It just seems hinky: if you don't have view-permission on the data, the schema shouldn't be wide-open.

--