Lexical paramater problem

[kakeez]

Hi,

In the query of the report, want to do something like:

Select
&lex_var my_alias,
... .....
FROM
....;

Now when lex_var, a bind variable is defined as:

:lex_var = 'statement I want'

I have an error saying 'my_alias' is undeclared identifier.

However with

:lex_var = 'statement I want my_alias,'

it works. Please help as I need to make it work the first way!

 

[nagornyi]

Way of doing what?

 

[kakeez]

I would like to have the statement as

SELECT
&lex_var my alias
.. ...
FROM
...;

with lex_var='statement_I_want'

As opposed to:

SELECT
&lex_var
.. ...
FROM
...;

with lex_var='statement_I_want my_alias'

The reason is that I need 'statement_I_want' in the Group By clause and so I need the alias not to be in lex_var.

 

[nagornyi]

Try this

[b]SELECT my_alias FROM ([/b]
SELECT
&lex_var my_alias
.. ...
FROM
...
[b])GROUP BY my_alias[/b]

 

[nagornyi]

Try this

[b]SELECT my_alias FROM ([/b]
SELECT
&lex_var my_alias
.. ...
FROM
...
[b])GROUP BY my_alias[/b]

 

[kakeez]

Hi,

Thanks for your reply. The thing is my select statement is huge (3,4 pages), and this is a report which I am asked to modify as little as possible,

I really need to stick to the structure
---------------------------------------------
SELECT
.. ..
&lev_var myalias
.. ..
FROM
...
...
GROUP BY
...
my_alias
...
------------------------------------------------

I know this is possible. I just tried the statement in TOAD and it works with no error. However with report builder, in the SQL query window, I get an error : "my_alias" undeclared indentifier.

 

[nagornyi]

You need to provide Initial value to the lex_var right in the Propery Palette when defining it as report parameter. When you type in the query in the SQL window, that initial value will be used to try the query and see if it has errors. You may change the lex_var at run time in the after paramform trigger, but withou initial value it will not parse the SQL.

 

[kakeez]

THANKS!!! I tried everything and have been trying for over 6 hours. It is exactly what you have said.

 

[kakeez]

Ok...I have a new problem now. The SQL query is accepted thanks to your help. Just a reminder. I am doing

SELECT
... . ...
&lex_var my_alias
... ...
FROM
...
GROUBY
my_alias;

Also, my_alias is likend to a column for display in the report. Now, essentially the new problem is that Nothing is displayed in the column corresponding to my_alias. My guess is that for some reason or another my_alias remains at null.

Also, if it helps, in the Data Model in the reports builder. When I look at the my_alias variable, it is the only one without a yellow arrow next to it.

I would apreciate any imput.