Strange Aggregate Filter

Strange Aggregate Filter

  

In Java Stack with DB Oracle, if the aggregate filter is:

colId = NullIdentifier()

it is true. 

Unfortunately if i change the code to :

colId = if(1=1,NullIdentifier(),NullIdentifier())

it results false.

regards, 

A


Hi Anonymous,

last week there was a question from a developer about how to use an If function in an aggregate filter. I've discovered that the solution was to transform the If(, ,) into If(, ,) = True. In your case it would be 

colId = if(1=1,NullIdentifier(),NullIdentifier()) = True

I don't know if that solves your problem but it deserves a try :) 

Regards,

António Pereira

Thank you, Sir. I'll try it later. I have another way that i have tried that works so far by giving if in both side:

if(parm=nullidentifier(), 1, colid)=if(parm=nullidentifier,1,parm)

Regards,

A

I can't see a logic in using it "if" that way:

colId = if(1=1,NullIdentifier(),NullIdentifier())


Anonymous wrote:

In Java Stack with DB Oracle, if the aggregate filter is:

colId = NullIdentifier()

it is true. 

Unfortunately if i change the code to :

colId = if(1=1,NullIdentifier(),NullIdentifier())

it results false.

regards, 

A




In SQL you can never do a = operation with NULL, Because that condition in most databases returns "undefined" ...so its neither true or false and never returns anything.

The platform detects when you do a "= NullIdentifier()" and automatically transforms it into a " Is NULL" operation...because otherwise it would never work.

When you make that "If" condition it doesn't do that for you because you could actually want to to really be NULL in some cases.


Can you post what is the condition that you really are trying to write (and the reason for it)? That way we could actually help you in getting a good filter instead of just telling what a valid syntax is.


Regards

João Rosado

Formiga wrote:

I can't see a logic in using it "if" that way:

colId = if(1=1,NullIdentifier(),NullIdentifier())


Anonymous wrote:

In Java Stack with DB Oracle, if the aggregate filter is:

colId = NullIdentifier()

it is true. 

Unfortunately if i change the code to :

colId = if(1=1,NullIdentifier(),NullIdentifier())

it results false.

regards, 

A




It's only to simplify, the true business is not like that, but the same pattern with that simple one.


João Rosado wrote:

In SQL you can never do a = operation with NULL, Because that condition in most databases returns "undefined" ...so its neither true or false and never returns anything.

The platform detects when you do a "= NullIdentifier()" and automatically transforms it into a " Is NULL" operation...because otherwise it would never work.

When you make that "If" condition it doesn't do that for you because you could actually want to to really be NULL in some cases.


Can you post what is the condition that you really are trying to write (and the reason for it)? That way we could actually help you in getting a good filter instead of just telling what a valid syntax is.


Regards

João Rosado

thank you for the reply. Here the case:

- in an aggregate i want to get different results, given two input parameters - say it parmA and parmB.

- the parameters correspond to different columns in the aggregate, say it colA and colB

- if parmB is null the aggregate should return records that has filter colA = parmA, regardless the colB, and vice versa - if parmB is not null the aggregate should return records filtered by colB = parmB, regardless column colA.

I use this query because i want to reuse the screen i have created. I can separate to different screens and so it means two different aggregates, but this is not what i want.

So, the aggregate filter i create is like this:

if(colB=nullIdentifier(),colA,1) = if(colB=nullIdentifier(),parmA,1) AND if(colB=nullIdentifier(),1,colB) = if(colB=nullIdentifier(),1,parmB)

Before i tried using (does not work):

colA =  if(colB=nullIdentifier(),parmA,colA) AND colB = if(colB=nullIdentifier(),colB,parmB)

regards,

A


Solution

Hi,


Thanks for explaining. It's really hard to read the condition like that.

Still not clear what you want to return if both are null or when both are not null.


This should work


(paramA = NullIdentifier() or colA = parmA) and (parmB = NullIdentifier() or colB = parmB)


Assuming I didnt mess up on the operators (its pretty late xD)

  •  if paramA is not null it filters by colA
  •  if paramB is not null it filters by colB
  •  if both are null returns everything
  •  if both are not null it filters by both


Regards,

João Rosado


Solution