Construct Variable Name?

Can you contruct a variable name from another variable? For example, I want the following to PRINT 10

DECLARE @.var1 INT
DECLARE @.var2 INT

SET @.var1 = 10
SET @.var2 = 20

PRINT '@.var' + '1'

This prints the variable name, not the contents of the var. I tried to parse it with square brackets, but no luck.

Thanks,
CarlYou can do this using dynamic SQL, though I'm not sure it would work for the specific PRINT statement in your example.|||Ya, I need a varName in a var. A datatype of VARNAME. in some langs you just parse it correctly and it works, some it won't.

if you substitute EXEC for PRINT, it says you must declare variable 'var1', as you can see it obviously is being declared and interpretted the way it was typed.

Carl|||Actually, dynamic SQL will not work for this example, because the scope of the variables is limited to the procedure in which they are created.

Please explain what you are trying to do, and maybe we can come up with a satisfactory solution.|||I am just trying to simplify the code.

In stead of:

IF @.var1 < @.var2
SET @.var3 = DATEADD(n,GETDATE(), @.var1)
ELSE
SET @.var3 = DATEADD(n,GETDATE(), @.var2)

I was hoping to simply:

SET @.var3 = DATEADD(n,GETDATE(), @.var + @.CurrentVarIndex)

I already know which one I want, @.var1 or @.var2, it's just that I have to IF and have 2 lines to do the work of 1.|||... it's just that I have to IF and have 2 lines to do the work of 1.I've seen worse. Using dynamic SQL or some other hack work around to do this is going to end up making your code more complicated, not simpler.|||I know what you mean regarding the dynamic sql and "the sea of red". can make reviewing your code quite miserable.
Carl

construct hierarchy

I have an emp table with the two columns
emp_no and mgr_no

say for e.g
emp_no mgr_no
-------
10 100
15 90
20 100
25 150
30 100
90 200
100 200
150 200
200 300
300 400

I want to construct a query for empno = 100, which will construct hieachy both above and one level below... e.g
400
300
200
100
30
20
10

Here 10,20, and 30 are directly below Mgr 100

For emp_no 200 the output should be
400
300
200
150
100
90
Here emp - 100,90 and 150 report to Mgr - 200

Any suggestions/comments ?

Many Thanks.

Ashselect emp_no
from emp
where mgr_no = &&par_emp_no
union
select mgr_no
from emp
where mgr_no >= &&par_emp_no
order by 1 desc;"&&", in Oracle, requires you to insert value for a parameter "par_emp_no". If you use another DB, see if it needs to be changed.
Also, I'd say that your first example lacks in mgr_no = 150 (which is higher than the parametrized 100).|||Thanks for the suggestion. This works as per the example I had given.

However it is not necesssary that the manager's empno is greater than his employee.|||ash, if you go all the way up the tree from any given level, what is the maximum dpth of the tree?

also, if you just dump them out in one column, like this --

400
300
200
150
100
90

what possible use could this be? how do you know which one's the boss, which one's the boss's boss, which one's the subordinate?

Construct a query.

Hi,

I have a table tab_temp of the format

FUNCTION_ID VARCHAR2(20),
DAILY_TARGET NUMBER,
DAILY_RESULT NUMBER,
DAILY_VARIANCE NUMBER,
WEEK1_TARGET NUMBER,
WEEK1_RESULT NUMBER,
WEEK1_VARIANCE NUMBER,
WEEK2_TARGET NUMBER,
WEEK2_RESULT NUMBER,
WEEK2_VARIANCE NUMBER,
WEEK3_TARGET NUMBER,
WEEK3_RESULT NUMBER,
WEEK3_VARIANCE NUMBER

No I want to fetch records in such a way that I display target first, and then result and then vairance.
e.g
1st record
function_id,daily_target,week1_target,week_2_targe t,week3_target
2nd record
function_id,daily_result,week1_result,week_2_resul t,week3_result
3rd record
function_id, daily_variance, week1_variance, week2_variance, week3_variance.

So one function_id should have 3 sets of records.
Now there could be several such function_ids.

How should I construct a query, (This is a requirement )which would fetch the records in above defined format.

Many thanks in advance.
Ashselect function_id
, 1 as line_number
, daily_target
, week1_target
, week2_target
, week3_target
from tab_temp
union all
select function_id
, 2
, daily_result
, week1_result
, week2_result
, week3_result
from tab_temp
union all
select function_id
, 3
, daily_variance
, week1_variance
, week2_variance
, week3_variance
from tab_temp
order
by function_id
, line_number|||I think this might work.

Many Thanks.