DW Retrive options

Hi Namik,

You can avoid those records either specifying the conditions into the
WHERE statement of dw's SQL source or applying a filter to the dw :

 - in dw painter : Rows > Filter
 - in script ...
      dw_1.SetFilter("<filter expression>")
      dw_1.Filter()

Alternatively, if you only want to hide some columns, in dw painter
add an expression to the "Visible" property for each colum.

Regards

Hi Namik,

You can copy your rows to other DW and print from the second DW.  2nd DW has the same dataobject, but you do not need to populate it with a  Retrieve.

// First find those rows you want to print
// for example, getRow() is the selected row
// and you are going to copy 10 rows

dw_1.RowsCopy( dw_1.GetRow(), 10, Primary!, dw_2, 1, Primary!)

// Make DW sort and group, next print
dw_2.GroupCalc()
dw_2.Print()


Regards,

BerX

Tried Rows > Filter and put the same condition as for the flag , doesn't give me any row
Thenk tried Rows> Filter and put the isnull( belowminimum) in the condition
Nothing worked.

Thanks

Namik,
Could you please post here the conditions or expression you're using for the flag ?

Here is DW Structue:

Header
Header Group owner : Owner
Header Group Location_oid: location_id, location_description
Header Group oid:  item_type, id, item_master_description, min_level, max_level, curr_level, on_order_qty, on_request_qty

Detail : Alternate_id, Alternate_curr_level
Trailer Group Oid: sum( alternate_curr_level for group 3) + curr_level,  
belowminimumflag

Belowminimumflag = if ( (min_level  >  grand_total and  on_order_qty =0 and  on_request_qty =0), "BELOW MINIMUM", "")

Here is the SQL for this DW:
SELECT Inventory_level.oid,
      Inventory_level.location_oid,
      Inventory_level.item_oid,
      Inventory_level.curr_level,
      Alternate_item.alternate_item_oid,
      Inventory_level.min_level,
      Inventory_level.max_level,
      Inventory_level.reserved_qty,
      Inventory_level.Annual_Usage,
      Inventory_level.Quarterly_Usage,
      Inventory_level.Monthly_Usage,
      Inventory_level.On_Order_Qty,
      Inventory_level.On_Request_Qty,
      Inventory_level.Purch_Lead_Time_Days,
      Inventory_level.Calculation_Date,
      Inventory_level.Recommended_Min_Level,
      Inventory_level.Recommended_Max_Level,
      Item_master.id,
      "item_master_oid"=[item_master].[oid],
      "item_master_description"=[Item_master].[description],
      Location_master.location_id,
      "location_description"=[Location_master].[description],
      'item_type'=Left([code].[id],1),
      "alternate_curr_level"=Inventory_level_1.curr_level,
      "alternate_min_level"=Inventory_level_1.min_level,
      "alternate_location"=Inventory_level_1.location_oid,
      "alternate_oid"=Inventory_level_1.item_oid,
      "owner"=[Generic_company].[Description],
      "alternate_id"=Item_master_1.id
FROM (((((((Inventory_level INNER JOIN Location_master ON Inventory_level.location_oid = Location_master.oid) INNER JOIN Item_master ON Inventory_level.item_oid = Item_master.oid) INNER JOIN Code ON Item_master.type_oid = Code.Oid) LEFT JOIN Alternate_item ON Inventory_level.item_oid = Alternate_item.item_oid) LEFT JOIN Inventory_level AS Inventory_level_1 ON Alternate_item.alternate_item_oid = Inventory_level_1.item_oid) LEFT JOIN Location_master AS Location_master_1 ON Inventory_level_1.location_oid = Location_master_1.oid) LEFT JOIN Generic_company ON Inventory_level.owner_oid = Generic_company.oid) LEFT JOIN Item_master AS Item_master_1 ON Alternate_item.alternate_item_oid = Item_master_1.oid
WHERE
 Inventory_level.curr_level<=[inventory_level].[min_level] AND
isnull(Inventory_level.min_level,0) > 0
and (Inventory_level_1.location_oid=[Inventory_level].[Location_oid] Or (Inventory_level_1.location_oid) Is Null)
union

SELECT Inventory_level.oid,
      Inventory_level.location_oid,
      Inventory_level.item_oid,
      Inventory_level.curr_level,
      Alternate_item.alternate_item_oid,
      Inventory_level.min_level,
      Inventory_level.max_level,
      Inventory_level.reserved_qty,
      Inventory_level.Annual_Usage,
      Inventory_level.Quarterly_Usage,
      Inventory_level.Monthly_Usage,
      Inventory_level.On_Order_Qty,
      Inventory_level.On_Request_Qty,
      Inventory_level.Purch_Lead_Time_Days,
      Inventory_level.Calculation_Date,
      Inventory_level.Recommended_Min_Level,
      Inventory_level.Recommended_Max_Level,
      Item_master.id,
      "item_master_oid"=[item_master].[oid],
      "item_master_description"=[Item_master].[description],
      Location_master.location_id,
      "location_description"=[Location_master].[description],
      'item_type'=Left([code].[id],1),
      "alternate_curr_level"=0,
      "alternate_min_level"=0,
      "alternate_location"="",
      "alternate_oid"=0,
      "owner"=[Generic_company].[Description],
      "alternate_id"=""
FROM (((((((Inventory_level INNER JOIN Location_master ON Inventory_level.location_oid = Location_master.oid) INNER JOIN Item_master ON Inventory_level.item_oid = Item_master.oid) INNER JOIN Code ON Item_master.type_oid = Code.Oid) LEFT JOIN Alternate_item ON Inventory_level.item_oid = Alternate_item.item_oid) LEFT JOIN Inventory_level AS Inventory_level_1 ON Alternate_item.alternate_item_oid = Inventory_level_1.item_oid) LEFT JOIN Location_master AS Location_master_1 ON Inventory_level_1.location_oid = Location_master_1.oid) LEFT JOIN Generic_company ON Inventory_level.owner_oid = Generic_company.oid) LEFT JOIN Item_master AS Item_master_1 ON Alternate_item.alternate_item_oid = Item_master_1.oid
WHERE
 Inventory_level.curr_level<=[inventory_level].[min_level] AND
isnull(Inventory_level.min_level,0) > 0

and Inventory_level.item_oid not in
(SELECT Inventory_level.item_oid
FROM (((((((Inventory_level INNER JOIN Location_master ON Inventory_level.location_oid = Location_master.oid) INNER JOIN Item_master ON Inventory_level.item_oid = Item_master.oid) INNER JOIN Code ON Item_master.type_oid = Code.Oid) LEFT JOIN Alternate_item ON Inventory_level.item_oid = Alternate_item.item_oid) LEFT JOIN Inventory_level AS Inventory_level_1 ON Alternate_item.alternate_item_oid = Inventory_level_1.item_oid) LEFT JOIN Location_master AS Location_master_1 ON Inventory_level_1.location_oid = Location_master_1.oid) LEFT JOIN Generic_company ON Inventory_level.owner_oid = Generic_company.oid) LEFT JOIN Item_master AS Item_master_1 ON Alternate_item.alternate_item_oid = Item_master_1.oid
WHERE
Inventory_level.curr_level<=[inventory_level].[min_level] AND
isnull(Inventory_level.min_level,0) > 0
AND (Inventory_level_1.location_oid=[Inventory_level].[Location_oid] Or (Inventory_level_1.location_oid) Is Null  )
)


Sorry , its so long,

May be I should do the sum if Alternative in the Query then I will not need the grouping.
Let me know what you think, Thanks a lot everyone.

I am still struggiling with this one.
Probably the option with printing to another Dw would be the most sufficient for me, since I need both possibilities:
the user would choose either to print all the records or just the records with the Belowminimum flag displayed.

I need more help with sytax and writing the code for that.
I am sorry, but I've been using PowerBuilder only for few months and don't know all details yet.
I appreciate any help here.

Thanks alot

Namik,

The filter expression should be :

"min_level  > grand_total and on_order_qty = 0 and on_request_qty = 0"

I see that 'min_level', 'on_order_qty' and 'on_request_qty' are DB cols
but ... 'grand_total' ? ..I suppose it's a computed field, what does it sum ?

sorry for delaying
Regards

That's what I put for the filter but it doesn't work.
Or mabe be I put the phole if statement.
 I will try without and see what I get

The Grand Total sums the curr_level and Alternative_curr_level for the group.
Ex. Main item curr_level = 2, there is 3 alternative items with curr_level respectfully 1,2,3,
The grand total will be 2+1+2+3 = 8.

Then it compares the min_level of main item to the grand total and checks for qty_ordered and qty_onhand. Based on that the flag is being printed.

I will try the filter statement without the "if" and see if it will work
Thanks a lot for your continuouse support

for some reason,
when I put the filter in the dw and save it , there is no rows in the output at all.
Then, I tried to run the dw ( run/preview), got all 632 rows, then did rows> filter with that expression, it gives me the correct results.

how do I do that in the script, do I put the filter statement before Retrive or after?

Thanks again

I did it in the script, it seams like I have to do 2 retrives.
When I put SetFilter and Filter statements before the Retrieve there is not rows returned, but when I do the Retrive and then Filter then retrive again, that  worked.

tell me if I am doing it wrong, may be I don't understand something and have some extra steps there,

Thanks

Namik, there's no need to do 2 retrieves.
these are the general steps :

    // Turn off drawing
    dw_1.SetRedraw(FALSE)

    // Clear any previous filter
    dw_1.SetFilter("")
    dw_1.Filter()

    // Retrieve data
    dw_1.Retrieve(<arguments>)

    // Apply new filter if needed
    dw_1.SetFilter("<filter_expression>")
    dw_1.Filter()

    // Sort & Recalc groups
    dw_1.Sort()
    dw_1.GroupCalc()

    // Turn drawing on
    dw_1.SetRedraw(TRUE)

That should work.
Regards