Creating Alias for Columns (is mine right or wrong)

[03Explorer]

I am trying to use my MSSQL knowledge to build reports for Informix and the interface I am programming in (Aqua Data Studio) does not seem to aid me in properly using syntax or where errors are in my code. So this could be a two part question.

One is to verifiy that I am using proper aliasing in my column names. Two is there a better application to program Informix with other than Aqua Data Studio that will help just like MSSQL has 'SQL Query Analyzer' or 'SQL Server Management Studio'?

Thanks

[b]SELECT[/b] 
  1                           AS 'OrdBy',
  c.callstartdate             AS 'Date',
  c.dnis                      AS 'dialed_number_id',
  'Total Number of calls'     AS 'Option',
  COUNT (DISTINCT s.callguid) AS 'Calls'

 

[PHV]

Don't use single quotes in alias names.
You may use dbaccess to test queries.
You may also take the time to read the documentation.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

Here is a sample of what I am useing now... which works with other systems. What am I doing wrong? Aqua Data Studio (which is the application my office uses to pull data gives a very vague error response

[b]SELECT distinct[/b]
  e.elementname as "element",
  e.exitstate

Full code being used

[b]SELECT distinct[/b]
  e.elementname as "element",
  e.exitstate
[b]FROM[/b]
  vxmlelement e
[b]WHERE[/b]
  e.elementname like 'get_taxID'
[b]GROUP BY[/b]
  e.elementname,
  e.exitstate
[b]ORDER BY
  e.elementname ASC;

Here is the error Message

>[Error] Script lines: 1-20 -------------------------
 A syntax error has occurred. 

 [Executed: 7/28/08 10:56:09 AM CDT ] [Execution: 0/ms]

 

[PHV]

First, why using the DISTINCT predicate in an aggregate query ?
I'd try the following:

SELECT DISTINCT
  e.elementname AS element,
  e.exitstate
FROM
  vxmlelement e
WHERE
  e.elementname = 'get_taxID'
ORDER BY
  e.elementname ASC;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[03Explorer]

Yeah, I notice that I was sending questionable with Distinct and then Group by... I guess in my efforts to debug I didn't fix that.

That is what I needed... no quotes or single quotes at all. one last question... what do you recommend for using a label with a space? Using underscore instead of space or is there an option to use brackets ie [ ] around the alias name with spaces?