problem faced during data extraction.

[shafee212]

with some help from progress talk ,, i have managed to extract data from master tables for only few selected fields..

but only one record in being extracted

my code is as follws
--------------------------------------------
---------------------------------------------
define variable tth As handle no-undo.
define variable qry_hndl as handle no-undo.
define variable tablbuf_hndl as handle no-undo.
define variable tablbuf_hndl2 as handle no-undo.
define variable qry_hndl2 as handle no-undo.
define variable v_tablename as char no-undo .
define variable v_space as char init " " no-undo .
define variable i as integer no-undo .
create query qry_hndl.
create query qry_hndl2.
FOR EACH tttable no-lock:
IF count = 0 THEN DO:
m_line2 = f2.
END.
ELSE
DO:
m_line2 = m_line2 + "," + f2.
END.
ASSIGN count = count + 1.
END.
create temp-table tth.
update v_tablename.
create buffer tablbuf_hndl for table v_tablename.
qry_hndl:set-buffers(tablbuf_hndl).
qry_hndl:query-prepare("for each" + v_space + v_tablename).
qry_hndl:query-open().
tth:CREATE-LIKE(v_tablename).
tth:temp-table-prepare(v_tablename).
tablbuf_hndl2 = tthEFAULT-BUFFER-HANDLE .
output to "/home/shafeem/source.csv".
mainloop:
repeat:
qry_hndl:get-next().
if qry_hndl:query-off-end then leave .
do i = 1 to tablbuf_hndl:num-fields:
put unformatted
tablbuf_hndl:buffer-field(i):NAME + ";" +
tablbuf_hndl:buffer-field(i):buffer-value() skip.
end.

tablbuf_hndl2:BUFFER-CREATE.
tablbuf_hndl2:BUFFER-COPY(tablbuf_hndl).
leave.
END.
output close.


qry_hndl2:SET-BUFFERS(tablbuf_hndl2).
qry_hndl2:QUERY-PREPARE("for each" + v_space + v_tablename).
qry_hndl2:QUERY-OPEN().

i = 0 .
output to "/home/shafeem/target.csv".
repeat:
qry_hndl2:get-next().
if qry_hndl2:query-off-end then leave.
DO i = 1 TO tablbuf_hndl2:NUM-FIELDS:
IF LOOKUP( tablbuf_hndl2:BUFFER-FIELD(i):NAME, m_line2 ) > 0 then
PUT UNFORMATTED
tablbuf_hndl2:BUFFER-FIELD(i):NAME + ";" +
tablbuf_hndl2:BUFFER-FIELD(i):BUFFER-VALUE()
SKIP.
END.

leave.
end.
output close.
qry_hndl:query-close().
qry_hndl2:query-close().
delete object qry_hndl.
delete object qry_hndl2.
delete object tablbuf_hndl.

----------------------------------
---------------------------------



the output i am getting after i did it on pt_mstr is


source.csv :-
----------------------
pt_part;10-10000
pt_desc1;OASIS(TM) COOLING SYSTEM
pt_desc2;HOME/INDUST
pt_um;ee
pt__qad13;
pt__qad12;0
pt_draw;
pt_prod_line;
pt_group;
pt_part_type;
pt_status;AC
pt_abc;
pt_iss_pol;yes
pt_phantom;no
pt_loc;200
pt__qad01;0
pt__qad02;0
pt_abc_amt;0
pt__qad03;0
pt__qad04;0
pt_avg_int;90
pt__qad05;28/05/92
pt_cyc_int;120
?
?
?
pt_ms;yes
pt_plan_ord;yes
pt_mrp;no
pt_ord_pol;
pt_ord_qty;0
pt_ord_per;7
pt_sfty_stk;0
pt_sfty_time;0
pt_rop;0
pt_buyer;
pt_vend;
pt__qad09;0
pt_pm_code;C
pt_mfg_lead;1
pt_pur_lead;0
pt_insp_rqd;no
pt_insp_lead;0
pt_cum_lead;8
pt_ord_min;0
pt_ord_max;0
pt_ord_mult;0
pt_yield_pct;100
pt__qad16;0
pt_setup;0
pt_setup_ll;0
pt_run_ll;0
pt_run;2
pt_price;3900
pt_xmtl_tl;0
pt_xlbr_tl;0
pt_xbdn_tl;0
pt_xsub_tl;0
pt_xmtl_ll;0
pt_xlbr_ll;0
pt_xbdn_ll;0
pt_xsub_ll;0
pt_xtot_cur;0
?
pt_xmtl_stdtl;0
pt_xlbr_stdtl;0
pt_xbdn_stdtl;0
pt_xsub_stdtl;0
pt_xtot_std;0
?
pt_ll_code;-3
pt_abc_qty;0
pt__qad10;0
pt__qad11;0
pt_routing;
pt_lot_ser;S
pt_timefence;0
pt_xmtl_stdll;0
pt_xlbr_stdll;0
pt_xbdn_stdll;0
pt_xsub_stdll;0
pt_rev;
?
pt__qad15;no
pt__qad17;yes
pt_qc_lead;0
pt_auto_lot;no
pt_assay;0
pt_batch;1
?
pt_user3;
pt_user1;
pt_user2;
pt_net_wt;50
pt_net_wt_um;KG
pt_size;1
pt_size_um;CM
pt_taxable;no
pt_taxc;
pt_rollup;no
pt_xovh_ll;0
pt_xovh_tl;0
pt_xovh_stdll;0
pt_xovh_stdtl;0
pt_site;10000
pt_shelflife;0
pt_critical;no
pt_sngl_lot;no
pt_upc;
pt_hazard;
?
pt__chr01;
pt__chr02;
pt__chr03;
pt__chr04;
pt__chr05;
pt__chr06;
pt__chr07;
pt__chr08;
pt__chr09;
pt__chr10;
?
?
pt__dec01;0
pt__dec02;0
pt__log01;no
pt__log02;no
pt__qad18;0
pt__qad21;0
pt__qad19;0
pt__qad20;0
pt_length;0
pt_height;0
pt_width;0
pt_dim_um;
pt_pkg_code;
pt_network;
pt_ll_drp;0
pt_fr_class;00000010
pt_spec_hdlg;
pt_bom_code;
pt_loc_type;
pt_transtype;
pt_cover;S
pt_unit_isb;yes
pt_article;
pt_po_site;10000
pt_ship_wt;53
pt_ship_wt_um;KG
pt_formula;no
pt_dea;no
pt__qad26;no
pt__qad22;no
pt__qad25;0
pt__qad24;0
pt__qad23;0
pt_comm_code;
pt_inst_call;yes
pt_sys_type;S
pt_tariff;
pt_origin;
pt_sttr;10
pt_mfg_mtbf;0
pt_mfg_mttr;0
pt_fru;yes
pt_ven_warr;no
pt_svc_group;
pt_svc_type;P
pt_mtbf;9999
pt_mttr;1
pt_isb;yes
pt_pvm_days;365
pt_warr_cd;W-1
pt_mod_date;09/07/08
pt_userid;mfg
?
pt_pvm_bom;PM10-10000
pt_pvm_route;PM10-10000
pt_pvm_um;
pt_rp_bom;REP10-10000
pt_rp_route;REP10-10000
pt_rp_vendor;
pt_rctpo_status;
pt_rollup_id;
pt_spec_grav;0
pt_joint_type;
pt_mfg_pct;0
pt_pur_pct;0
pt_drp_pct;0
pt_pou_code;
pt_wks_avg;6
pt_wks_max;14
pt_wks_min;2
pt_pick_logic;0
pt_fiscal_class;
pt_dsgn_grp;
pt_drwg_loc;
pt_ecn_rev;
pt_drwg_size;
pt_model;
pt_repairable;yes
pt_rctwo_status;
pt_lot_grp;
pt_rctpo_active;no
pt_rctwo_active;no
pt_break_cat;
pt_fsc_code;ITEMS
pt_trace_active;no
pt_trace_detail;no
pt_pm_mrp;no
pt_ins_call_type;
pt_ins_bom;
pt_ins_route;
pt_promo;pg2
pt_meter_interval;0
pt_meter_um;
pt_wh;yes
pt_btb_type;01
pt_cfg_type;1
pt_app_owner;
pt_op_yield;no
pt_run_seq1;
pt_run_seq2;
pt_atp_enforcement;0
pt_atp_family;no

and target.csv as
-------------------------
pt_um;ee
pt_site;10000




i want to extract data for those fields which are names are stored in tttable.f2 (temp table already created)

only one record is being extracted .

just tell me whether source.csv and target.csv are correct
and can any one could modify my code so that all records present in table is displayed as i have limited knowledge about dynamic queries.

 

[Casper]

I don't know what you exactly want but there are two things which probably need attention:

First you have the following code:

tablbuf_hndl2:BUFFER-CREATE.
tablbuf_hndl2:BUFFER-COPY(tablbuf_hndl).
[COLOR=red]leave.[/COLOR]

within the repeat block, so only 1 iteration will be made.

2nd:
same applies for the second query you run:

output to "/home/shafeem/target.csv".
repeat:
    qry_hndl2:get-next().
    if qry_hndl2:query-off-end then leave. 
    do i = 1 to tablbuf_hndl2:num-fields:
        if lookup( tablbuf_hndl2:buffer-field(i):name, m_line2 ) > 0 then
        put unformatted
        tablbuf_hndl2:buffer-field(i):name + ";" +
        tablbuf_hndl2:buffer-field(i):buffer-value()
        skip.
    end.
    [COLOR=red]leave.[/COLOR]
end.
output close.

Casper.