Can't Alias A Column

[texasprd]

We're running our data mart on Progress 9.1E. Even with an extremely simple query like

"select g1.date DATE1 from glc g1"

the column-alias fails and I get the following: "** Unable to understand after -- "select". (247)".

"select g1.date alias DATE1 from glc g1" yields the same result.

"select g1.date as DATE1 from glc g1" results in "** Invalid datatype -- sample types are: char, integer, date, logical(222)".

Why am I unable to alias a column? I see other posts on the forum with snippets that appear to successfully alias columns. Is there a database parameter that allows/suppresses aliasing?

We don't have a Progress DBA here, but if there's a parameter that enables column-aliasing, please tell me what to change and I'll get our AIX administrator to change it if I don't have sufficient access.

Thanks for your help!

 

[medu]

the issue you have is the 'date' keyword... try

select g1."date" [as] date1 from glc [as] g1

m.edu
keep it simple
www.ganimede.ro
medu@ganimede.ro

 

[texasprd]

medu, thanks for your reply! Unfortunately, "date" as keyword and column name isn't the issue. (I didn't design this db - it was done by someone before me. I would not have used a keyword as a column name)

"select g1.date from glc g1" works, so the table-name alias works, and clearly the column name isn't an issue either. However, to eliminate red herrings, I'll use a different query:

"select c.profit-center from cda c where c.date = (select max(g.date) from glc g)"
The above works.

But
"select c.profit-center ownbranch from cda c where c.date = (select max(g.date) from glc g)"

and

"select max(c.profit-center) newestbranch from cda c where c.date = (select max(g.date) from glc g)"
do not work - they return "** Unable to understand after -- "select". (247)** Line 1. Invalid SQL statement. (945)". Inserting "AS" between the "max" function and the alias returns "** AS or LIKE phrase not allowed with expressions. (591)** Could not understand line 1. (196)".

So, I can alias a table, but the alias of a column or scalar isn't working.

 

[medu]

That's weird, i usually get errors for fields that are keywords and always use quote just in case... since you are talking about AIX I thought it was safe to assume that you were using JDBC, and next I was about to falsely presume you were using some ODBC data access when in fact I think you're just writing SQL statements inside the Progress client

That completely weird error message and the short explanation found on KB lead me to assumption and indeed I do get a similar error when running the statement on progress client but as you were talking about data marts why are you trying to run SQL commands from progress 4gl client... this only supports a limited subset of SQL98?

You won't have any problems with column/table aliasing either with JDBC/ODBC and in that case you will need to quote the tables/fields that are keywords or contains non standard SQL characters like "-" which are very common in a progress database.

Marian

m.edu
keep it simple
www.ganimede.ro
medu@ganimede.ro

 

[texasprd]

...I think you're just writing SQL statements inside the Progress client

That's correct - I am doing this in the Progress client/editor on AIX. My apologies for not specifying that earlier...

As for why I'm trying to do SQL from the Progress client, I do SQL whenever I can in order to build my skills in that area. The reasons for that belong in another thread - probably under a "general" forum on the board :biggrin:...

This is really frustrating - as I read the documentation, I should be able to alias a column/scalar.

 

[tamhas]

Marion means SQL-89 from the Progress editor ... ancient, incomplete, etc., etc. so please clarify how you are submitting these statements. If in the Progress editor, all bets are off.

Your narration is also complete. You talk about using AS with max, but not with a simple field. Please check AS with a simple field and try it with the expression in ()s, i.e., (max(g.date)) as something. Unless you are using the Progress editor, then I wouldn't even bother.

 

[medu]

This is really frustrating - as I read the documentation, I should be able to alias a column/scalar.

well, progress documentation and frustration actually goes hand in hand very often

never tried SQL statements in progress client (except counts or when I'm too lazy to type for each/display) but you might be right and it looks like you can alias columns, the error pop up only when you try to use it with expressions... check out the error message on KB.

 

[texasprd]

In response to tamhas;

If you equate "simple field" to "column" (select column from table), tried that already (see post #3). Also tried putting parens around the entire MAX function - no joy there, either. And "AS" in all attempts returns the same result - ""** AS or LIKE phrase not allowed with expressions. (591)**" (maybe a bug where Progress doesn't realize SQL is involved and thinks I'm trying to define a variable?).

Yes - I'm in the Progress editor. This would run in our AIX/Progress environment to push data to another system.

 

[jopeJeano]

Cant Alias A Column

i cant vote for my site.Out is ok but not In.The code is ok and cookies enabled.i dont understand.