Help Neeed: conditional execution in macro

Database
Enthusiast

Help Neeed: conditional execution in macro

Hi,
Is it possible to do conditional execution in macro based on the input parameter passed:

For example:
create macro Mymacro(
id integer,
param_2 varchar(10),
param_3 date format 'YYYY-mm-dd')
as
(
if (:id=2) Then
SEL col1,col2 from tab1;
else
SEL col1,col2 from tab2;
);

If my id ,which is an input parameter,is certain value, then select from table tab1
else select from table tab2.
I tried the above syntax in bteq, but there were compilation errors.

Can anyone let me know the solution for this?
Any alternate way?

5 REPLIES
Enthusiast

Re: Help Neeed: conditional execution in macro

Select col1, col2 from tab1
where :id = 2;
Select col1, col2 from tab2
where :id <> 2;
Junior Supporter

Re: Help Neeed: conditional execution in macro

It seems a typical use of a stored procedure...

HTH.

Cheers.

Carlos.
Enthusiast

Re: Help Neeed: conditional execution in macro

@Jimm
The macro should execute one of the statements in IF..ELSE Block,not both.This is the requirement.
@Carlos..Cannot we handle this in a macro?
Enthusiast

Re: Help Neeed: conditional execution in macro

If tab1 and tab2 are related and can be joined you may be able to use a CASE statement to handle this inside your macro:

SELECT CASE WHEN :ID = 2
THEN tab1.col1
ELSE tab2.col1
END AS col1_ ,
CASE WHEN :ID = 2
THEN tab1.co2
ELSE tab2.col2
END AS col2_
FROM Table1 tab1
(INNER||LEFT OUTER ||FULL OUTER) JOIN
Table2 tab2
ON tab1.colx = tab2.colx
;

(Really wish these forums supported proper code formatting)

A FULL OUTER JOIN might be appropriate here if you wish to make sure that a row is returned. It may not be the most efficient solution, but if you are stuck using a macro it may be the only way. That being said, Carlos' suggestion to use a stored procedure may be more straightforward and efficient to handle this conditional logic.
Junior Supporter

Re: Help Neeed: conditional execution in macro

>>"@Carlos..Cannot we handle this in a macro?"

Sure:

REPLACE MACRO Mymacro( id integer,
param_2 varchar(10),
param_3 date format 'YYYY-mm-dd')
as
(
CALL MYSP(:id, :param_2, :param_3);
);

;-)

I think that if you want the logic to execute one and only one of the two selects (tab1/tab2) you must issue an IF (or CASE) construct inside a SP.

Cheers.

Carlos.