Selecting system date

[Gad Zooks]

Hi all,

Hope somebody can help, I'm pretty new to Progress although I have done a lot of SQL stuff with other databases. I am writing some data profiling software which already works well with Oracle, SqlServer and other databases but I am having some fun making it work with Progress.

I have worked out that I can select a system date in the format "YYYY-MM-DD HH24:MI:SS" from a table in the following manner;

select MIN(CONCAT(
to_char(sysdate(), 'YYYY-MM-DD '),
to_char( systime(), 'HH24:MI:SS')
)) as sdate
from PUB.my_table

but this seems pretty inefficient if "my_table" has a lot of rows.

In SqlServer I can just select the date without specifying a table by omitting the FROM clause e.g.

SELECT CONVERT(CHAR(19), GETDATE(), 120 )

In Oracle they have a pseudo-table, DUAL, which I can use e.g.

SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL

How can I achieve something similar in Progess SQL? It doesn't seem to work if I simply omit the FROM clause and I really want to avoid scanning tables just to get the system date.

Also, what GUI front-ends do people use with Progress? Are there any good freebies out there?

Many thanks for your help,

Gad

 

[Cringer]

Just a thought, can you query hidden tables? If so, use something like the table PUB._MstrBlk as this has only 1 record AFAIK.

 

[Gad Zooks]

That worked perfectly, thank-you muchly.

I'm having all kinds of fun and games getting what are sometimes even pretty simple queries to run but generally with a bit of perseverance I'm finding a way to get stuff to work!

For example:

SELECT SUM(CASE WHEN "col" IS NOT NULL THEN 1 ELSE 0 END) as "sum_col"
FROM PUB."my_table"

works, but

SELECT SUM(CASE WHEN "col" IS NOT NULL THEN 1 ELSE 0 END)
FROM PUB."my_table"

does not.

It is a little odd to me that I should be forced to provide an alias. But there you you, I guess that's half the fun of writing a cross-database application. One thing I have learned is that there is no such thing as "standard" SQL.

 

[Cringer]

Out of interest, what version of Progress are you running? That has quite a bearing on the version of SQL in play.

 

[Gad Zooks]

TBH I'm not sure, I am working on database on a client's site that I am connecting to using ODBC.

I'm, not sure how to tell what version they are using but I suspect it may be quite old as I am working on a data migration project in which this database represents the legacy system which my client wishes to move away from.

 

[Cringer]

It's worth finding out what the version is as older versions obviously have much more limited scope for what you are trying to do. Your client should be able to tell you. Hopefully. !!