Hi all.

Has anyone got a method of doing the following in a macro? I want to check two variables against each other. If they're the same do one thing, else do another. I thought the dqm macro case statement wou;d handle this. Alas, it looks like it can't take variables in the search. As an example:

(and yes, the above is a really silly thing!)

The above demos what I am trying to do, test a variable against another variable (in the above I'm just using a single variable to demo).

So I'm looking for examples of how to do the above.

Thanks.

PS

The workaround I have at the moment is...

------------------------------
Marc Reed
------------------------------

Hi Marc,

where do you want to use the macro?

If it's in a report why not combine regular coding with macros? Here's a simple filter expression on GO data (quarter and year are the same source):

Hi all.

Has anyone got a method of doing the following in a macro? I want to check two variables against each other. If they're the same do one thing, else do another. I thought the dqm macro case statement wou;d handle this. Alas, it looks like it can't take variables in the search. As an example:

(and yes, the above is a really silly thing!)

The above demos what I am trying to do, test a variable against another variable (in the above I'm just using a single variable to demo).

So I'm looking for examples of how to do the above.

Thanks.

PS

The workaround I have at the moment is...

------------------------------
Marc Reed
------------------------------

Yes, I could do a mixed SQL / Macro solution.
I like doing full macro solutions as they ensure the expression is as simple as possible before code gets sent to the database. Otherwise we rely upon the cognos SQL trying to simplify the SQL first. Sometimes it does a good job, sometimes it ends up sending case statements to the db, when i know should be able to fully resolve it first.

Admittedly the engine has gotten better over time and does simplify stuff a lot better. But my using macros I know it will always be simplified, regardless of what happens to the engine.

Hi Marc,

where do you want to use the macro?

If it's in a report why not combine regular coding with macros? Here's a simple filter expression on GO data (quarter and year are the same source):

Hi all.

Has anyone got a method of doing the following in a macro? I want to check two variables against each other. If they're the same do one thing, else do another. I thought the dqm macro case statement wou;d handle this. Alas, it looks like it can't take variables in the search. As an example:

(and yes, the above is a really silly thing!)

The above demos what I am trying to do, test a variable against another variable (in the above I'm just using a single variable to demo).

So I'm looking for examples of how to do the above.

Thanks.

PS

The workaround I have at the moment is...

------------------------------
Marc Reed
------------------------------

Certainly using macros be very effective at injecting what you "really need" vs SQL-expressions.

That said, query engine (DQM) has been able to apply  the concept of constant folding during query parsing for several years.

For example 10 * 20 could be replaced with the value 200.

Similarly, expressions (i.e. person using prompts) could logically created something like the following, which reduces to SNO = 'P1'.

SNO = 'P1' AND 'All' = 'All' AND 'All' = 'All' AND 'All' = 'All' AND 'All' = 'All' AND (
    'All' LIKE '%All%' AND 1 = 1 OR
    CASE PNO WHEN 'P1'  THEN 'Male' WHEN 'P2' THEN 'Female' ELSE 'Unknown' END IN ('All')) AND (
    'All' LIKE '%All%'  AND 1 = 1 OR PNO IN ('P1')
)

If an expression were written as _add_days( current_date, 20 ), the value cannot be constant folded as current_date must be evaluated at execution time.

Meanwhile, if the expression had been written as _add_days ( #$current_date#, 20) then it would be generated as a date literal such as DATE '2023-05-02'.
 

Yes, I could do a mixed SQL / Macro solution.
I like doing full macro solutions as they ensure the expression is as simple as possible before code gets sent to the database. Otherwise we rely upon the cognos SQL trying to simplify the SQL first. Sometimes it does a good job, sometimes it ends up sending case statements to the db, when i know should be able to fully resolve it first.

Admittedly the engine has gotten better over time and does simplify stuff a lot better. But my using macros I know it will always be simplified, regardless of what happens to the engine.

Hi Marc,

where do you want to use the macro?

If it's in a report why not combine regular coding with macros? Here's a simple filter expression on GO data (quarter and year are the same source):

Hi all.

Has anyone got a method of doing the following in a macro? I want to check two variables against each other. If they're the same do one thing, else do another. I thought the dqm macro case statement wou;d handle this. Alas, it looks like it can't take variables in the search. As an example:

(and yes, the above is a really silly thing!)

The above demos what I am trying to do, test a variable against another variable (in the above I'm just using a single variable to demo).

So I'm looking for examples of how to do the above.

Thanks.

PS

The workaround I have at the moment is...

------------------------------
Marc Reed
------------------------------

@NIGEL CAMPBELL does any of that make it into product documentation? I haven't seen it anywhere.

A silly example, in the past i knew if i wrote an expression with an IF statement rather than a CASE it had more chance of being simplified pre sql. Over time the query engine changed and it's only by constantly looking at the SQL from one version of Cognos to another, that you realise things that you used to do are no longer the case.

As I said in my response, i have spotted that CA has gotten better over time at simplifying the SQL. But it is only by being a geek and pouring over sql that I have noticed this.

The above examples you give, and when it changes from version to version, are exactly what we need in product documentation, and what's new lists.

Certainly using macros be very effective at injecting what you "really need" vs SQL-expressions.

That said, query engine (DQM) has been able to apply  the concept of constant folding during query parsing for several years.

For example 10 * 20 could be replaced with the value 200.

Similarly, expressions (i.e. person using prompts) could logically created something like the following, which reduces to SNO = 'P1'.

SNO = 'P1' AND 'All' = 'All' AND 'All' = 'All' AND 'All' = 'All' AND 'All' = 'All' AND (
    'All' LIKE '%All%' AND 1 = 1 OR
    CASE PNO WHEN 'P1'  THEN 'Male' WHEN 'P2' THEN 'Female' ELSE 'Unknown' END IN ('All')) AND (
    'All' LIKE '%All%'  AND 1 = 1 OR PNO IN ('P1')
)

If an expression were written as _add_days( current_date, 20 ), the value cannot be constant folded as current_date must be evaluated at execution time.

Meanwhile, if the expression had been written as _add_days ( #$current_date#, 20) then it would be generated as a date literal such as DATE '2023-05-02'.
 

Yes, I could do a mixed SQL / Macro solution.
I like doing full macro solutions as they ensure the expression is as simple as possible before code gets sent to the database. Otherwise we rely upon the cognos SQL trying to simplify the SQL first. Sometimes it does a good job, sometimes it ends up sending case statements to the db, when i know should be able to fully resolve it first.

Admittedly the engine has gotten better over time and does simplify stuff a lot better. But my using macros I know it will always be simplified, regardless of what happens to the engine.

Hi Marc,

where do you want to use the macro?

If it's in a report why not combine regular coding with macros? Here's a simple filter expression on GO data (quarter and year are the same source):

Hi all.

Has anyone got a method of doing the following in a macro? I want to check two variables against each other. If they're the same do one thing, else do another. I thought the dqm macro case statement wou;d handle this. Alas, it looks like it can't take variables in the search. As an example:

(and yes, the above is a really silly thing!)

The above demos what I am trying to do, test a variable against another variable (in the above I'm just using a single variable to demo).

So I'm looking for examples of how to do the above.

Thanks.

PS

The workaround I have at the moment is...

------------------------------
Marc Reed
------------------------------

Hi all.

Has anyone got a method of doing the following in a macro? I want to check two variables against each other. If they're the same do one thing, else do another. I thought the dqm macro case statement wou;d handle this. Alas, it looks like it can't take variables in the search. As an example:

(and yes, the above is a really silly thing!)

The above demos what I am trying to do, test a variable against another variable (in the above I'm just using a single variable to demo).

So I'm looking for examples of how to do the above.

Thanks.

PS

The workaround I have at the moment is...

------------------------------
Marc Reed
------------------------------