Lexical paramater problem 2

[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.

 

[nagornyi]

Try debugging the SQL in TOAD. Lexicals can be substituted in TOAD parameter screen the same way the report is supposed to do.

 

[kakeez]

Hi again nagornyi,

Thanks for bearing with me. I have been playing around with the code. Remember how I wanted

lex_var := 'Statement I want'

and to have

Select &lex_var my_alias....

well lex_var has two possibilities:
1. A function, say lex_var= my_package.my_function()
2. A field, say lex_var = my_table.my_field

I have a feeling that my_alias is in fact equal to the statements in 1 and 2 and not the returned values. Is this possible? The reason I say that is that I tried in pl/sql with

Select &lex_var into my_alias and I saw that my_alias was equal to the string of statement 1 or 2..

 

[nagornyi]

Of course, my_alias wil be equal to statement 1 or 2, but it gets that value before the SQL gets parsed, so the value becomes a part of the SQL statement. All you need to do is to try the SQL with my_alias replaced with either value, and debug it.

 

[kakeez]

Ok. I did what you asked and thanks! I have it seems pinpointed the problem. Because the query ran fine in toad, I started looking at the layout of the report and there I see that the width field is blank and I believe this to be the root of the entire problem (i.e. of not having anything displayed for my_alias).

Unfortunately, I cannot directly set the width field to some value so I will try to work around! Any suggestions are welcome and thanks so much for bearing with me here.

 

[nagornyi]

What is the width field?

 

[kakeez]

In Layout, choose a displayed field, double-click or press F4 takes you to the Property Palette.

There, under Column, there is a field or paramater called width.

I actually fixed the original problem with a sort off patheric workaround. I created a formula column in DATA MODEL. The formula is:

my_alias_form := my_alias.

and I display the formula instead of the original alias which allowed me to set the width field to 250 and now things are properly displayed.