Question: **Background:**

I'm writing a report in Avaya CMS Supervisor. Now, if you don't know what that is, it doesn't really matter, since the question's about SQL. But if you *do* know about it, then it would most certainly help.

In this program, you can create reports which pull data out of a database (which is then shown to a user). Thing is; to create these reports, you have to use a (ridiculous) form of visual point-and-click-select-items sort of query builder. What you can do, though, is define every item to select (in the `SELECT`-clause) and every item in the `WHERE`-clause.

Now, my problem is that the only way to access user-input is within the `WHERE`.

**Question:**

Is it possible, through any hacks or whatever, to modify, from the `WHERE`, what values are shown in the `SELECT`?

If simple SQL-queries had (variable) variables, this would be more-or-less valid:

```
SELECT drv-tbl.$A
FROM drv-tbl
WHERE
    $b = (SELECT value AS $A FROM table) AS drv-tbl

```

Answer: 
It's a bit late but nevertheless: there is no standard way to do that. A hacky way would be to save the report you need to a file, edit it and load back into CMS Supervisor. This way you can use almost any kind of query supported by Informix server in CMS.

A slightly easier and still-within-warranty way would be to dump the data you need directly from Informix, load it in Excel or whatever and process there. See my article on how to access Informix data: <http://avaya.dwalin.ru/call-management-system/tutorial-using-informix-tools-to-access-cms-historical-database/>
The easier way to do this would be to create a custom database view (the name must begin with c\_)that encapsulates the logic you are trying to apply. After the view is working, you must then go into the CMS dictionary and add the view as a custom table and add each column. This is needed because that is the only way the view will be available to Designer.