SQL COUNT in Progress

[tale103108]

I have used many databases and can do

SELECT COUNT(DISTINCT col)) as colname
FROM mytable;

but having problems with Progress.

Question: How do you count distinct rows?

Also, what is the equivalent to the Oracle REPLACE command? (which search a string and replaces all occurances of a char with another char)

Example: (Oracle SQL)

SELECT REPLACE(name,'L','O') as new_name
FROM employee;

now all 'L' are replaced with 'O'

Cheers.

 

[tamhas]

SELECT COUNT(DISTINCT col)) FROM mytable;

is correct syntax. What error are you getting.

The manual shows

REPLACE ( string_exp1 , string_exp2 , string_exp3 )

as correct syntax.

Specific errors?

Try it without the AS?

Have you looked in the manual?

 

[tale103108]

select count(distinct("pub"."student"."student-id")) as students
from "pub"."student"


Error is:
[Error] Script lines: 1-2 --------------------------
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "("pub"."student"."student-id" (10713)

Removed AS and also removed alias -- no difference.

 

[Cecil Snodgrass]

you do not need to qualify the owner and table on the column.

select count(distinct("student-id")) as students
from "pub"."student"

 

[tale103108]

select distinct() and select count(), when used by themselves, work fine. When they are combined together as in my example is when I get the error. I did take the full qualification out of the column ... error is:

>[Error] Script lines: 1-3 --------------------------
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "("student-id")) as students from "pub"" (10713) 

 

[tale103108]

Is there a SQL Query tool that comes with the Progress Database that I can use to run this SQL thru...other than what I am using?

 

[tale103108]

select count( distinct col ) ... instead of select count(distinct(col)) ... too many () .. not needed with distinct.

Thx.

 

[tamhas]

There is sqlexp. It's not pretty, but it does eliminate issues of third party programs.