Avoid Having clause and use Where clause

[bo5i]

I am trying to generate a report which generates the following SQL in MicroStrategy resulting incorrect results. I am interested in creating a direct SQL which generates correct results .(as mentioned below at the end). How can i avoid having clause in SQL and i am looking for a where clause in the SQL.

create table ZZMQ00 nologging as
select a11.ITEM ITEM_NBR_1
from Fact a11,
LT_SHIP_TO a12
where a11.SHIP_TO = a12.SHIP_TO
and a12.PARENT in (104440)
group by a11.ITEM
having sum(a11.ON_QTY) < 2.0

select a11.ITEM ITEM_1,
max(a13.ITEM_DESCR) ITEM_DESCR,
count(a11.SHIP_TO) WJXBFS1
from FT_Fact a11,
ZZMQ00 pa1,
LT_SHIP_TO a12,
LT_ITEM a13
where a11.ITEM = pa1.ITEM_1 and
a11.SHIP_TO = a12.SHIP_TO and
a11.ITEM = a13.ITEM
and a12.PARENT in (104440)
group by a11.ITEM
------------------------------------------------------

select item,count(a.ship_to)
from Fact a
,lt_ship b
where a.ship_to=b.ship_to
and b.parent=104440 and on_qty < 2
group by item

 

[entaroadun]

If you have a filter on a metric, and the metric has an agg function (like Sum), then the filter will be applied to the Sum function, which has to exist in Having clause.

If this is a one-off problem and you don't want any data model changes, then create a custom filter using:

ApplyComparison("(a11.ON_QTY < 2)",[Attribute]@ID)

where the [Attribute] is an attribute that sits only on your fact table.


It seems like your report is aggregating information from order line items where the item count is < 2. Based on that, you should model in ON_QTY as an attribute that sits only on the fact table. After all, the item count (ON_QTY) would be an attribute of the order line item that you want to filter on.

 

[bo5i]

Thanks entaroadun, i am able to get the desired results.