Using for each ... next

[#1]

I'm trying to run a report out of our main data system but becasue it's such a slow / complicated report in the back group I need to run the query for each site code (contract) in turn rather than just using a wild card.

I think the for each ... next function will do this but I'm just not quite clear on how to actually do it! Below is the report as it stands with a single base code in. Ideally I'd like to load the list of base codes I want to search for from an excel document.

Thanks, Emma

SQL select

contract,

purch_order_no,

purch_line_no,

purch_rel_no,

part_no,

serial_no,

lot_batch_no,

qty_received,

qty_shipped,

unit_meas,

received_date,

shipped_date,

cust_order_no,

cust_line_no,

cust_rel_no,

cust_line_item_no,

from_site,

state,

to_char(ifsapp.Inventory_Part_Config_API.Get_Inventory_Value_By_Method(contract, part_no, '*'), 'B99999990.99') unit_cost,

IFSAPP.PART_CATALOG_API.Get_Description(part_no) description

from

ifsapp.inventory_parts_in_transit

where

contract = '2SABD'

and

part_no between '0' and 'ZZZZZZZZZZZZZ';

[hector · Accepted solution]

Hi, maybe you can use the for each in this way

For each contract in '2SABD','2SABC','3SABD'

DATA:
SQL select
 ...
 contract,
 purch_order_no,
 ...
 to_char(ifsapp.Inventory_Part_Config_API.Get_Inventory_Value_By_Method(contract, part_no, '*'), 'B99999990.99') unit_cost,
 IFSAPP.PART_CATALOG_API.Get_Description(part_no) description
from
 ifsapp.inventory_parts_in_transit
where
 contract = '$(contract)' and
 part_no between '0' and 'ZZZZZZZZZZZZZ'
;
next

I hope this can help u.

Regards.

[shumailh]

Hi all,

I have a small problem would appreciate if any one can help. I have developed an application which generates a qvd on daily basis now i want to update the process to merge qvd's in to 1 on daily basis. I tried to use for each statement and use concatenation but the concatenation not working fine... it create lots of dublications... may be i am not working right with the table alias "Active_Card" while loading. need help!!!

$(include=Configuration.txt);

let path= '$(prepath)' & '\Output\' & '$(mn)' & '\Cards\QVDs\';

let a = 1;

for each File in filelist ('$(path)ActiveCard_*.qvd')

let CurrentFileName = right('$(File)', (len('$(File)') - len('$(path)')) ) ;
let AC = 'ActiveCard_' & right('00' &'$(CurrentDay)', 2) & '.qvd';

 if a = 1 then
 Active_Card:
 LOAD *
 FROM [$(path)$(CurrentFileName)] (qvd);

 else

 Active_Card:
 LOAD
 limit,
 bal,
 delq,
 AIF
 RESIDENT Active_Card;
 CONCATENATE
 LOAD
 limit,
 bal,
 delq,
 AIF
 FROM [$(path)$(CurrentFileName)] (qvd);

 END IF

 a = a+1

next File ;

Regards,
Shumail Hussain

[#4]

Hi,

To remove the duplications have you tried using something like NOT EXISTS(FieldName) in the load statement? The field name you use should be unique of course.

Hope this helps,

Emma

[shumailh]

Hi Emma,

Thanks for the reply... actually dublication here is some abnormal number of records... but i already resolve this issue.

Basically i was looking out the records by loading it in debug mode where i put a limit of 10 records and uncertainly i am getting higher / abnormal number of records with respect to higher date file... attached snapshot is showing the date field i have in each qvd.

when i use the full reload regardless of in debug mode... i got the valid number of records. I still don't know why this debug mode showing me this abnormal count..