problem in data extraction

S

shafee212

Member
can any one help me out . i need to extract data of certain fields which is listed in a temp-table filed ( like a ttable.f2 )

tttable.f2 ->

pt_part
pt_desc1
pt_desc2

etc
all these are presnt in filed f2 of temptable ttable sequentially

i need to extract data present in master file for all these fields

there could be any no of fields present in ttable.f2 and can be from any master table. here in this case it is pt_mstr


while searching from net i have got a code which ectracts data for the whole master table . but i require only for few fields which are listed in ttable.f2

programs accepts table name and extracts the data from all fields

/* Dynamic Query to buffer-copy records from */
/* Dynamic DB Table to Dynamic Temp-table */
define variable qry_hndl as handle no-undo.
define variable qry_hndl2 as handle no-undo.
define variable tablbuf_hndl as handle no-undo.
define variable tablbuf_hndl2 as handle no-undo.
define variable v_tablename as char format "x(10)" no-undo.
define variable v_space as char init " " no-undo.
define variable v_fldcnt as inte init "1" no-undo.
define variable i as integer no-undo.
create query qry_hndl.
create query qry_hndl2.
/* Temp-table Definition */
define variable tt_target as handle.
create temp-table tt_target.
/* Accept input DB Table */
update v_tablename.
/* Buffer for DB Table */
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().
/* Create Dynamic Temp-table like the Input table */
tt_target:CREATE-LIKE(v_tablename).
tt_target:TEMP-TABLE-PREPARE(v_tablename).
/* Buffer for Temp-Table */
tablbuf_hndl2 = tt_targetEFAULT-BUFFER-HANDLE.
/* main logic */
output to "source.prn".
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 v_space + " " +
tablbuf_hndl:BUFFER-FIELD(i):BUFFER-VALUE() skip.
end.
/* Buffer-copy */
tablbuf_hndl2:BUFFER-CREATE.
tablbuf_hndl2:BUFFER-COPY(tablbuf_hndl).
leave. /* need just one record */
end. /* repeat */
output close.
/* Show records from Temp-table */
qry_hndl2:SET-BUFFERS(tablbuf_hndl2).
qry_hndl2:QUERY-PREPARE("for each" + v_space + v_tablename).
qry_hndl2:QUERY-OPEN().
i = 0.
output to "target.prn".
repeat:
qry_hndl2:GET-NEXT().
IF qry_hndl2:QUERY-OFF-END THEN LEAVE.
DO i = 1 to tablbuf_hndl2:NUM-FIELDS:
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.
-------------------------------------------------------------------

can any one suggest me changes i should made in above code
or is there any alternate solution
 
The first change you ought to make is to put CODE tags around the code portion of your post. Then it might actually be readable :awink:

If I've understood your question, and assuming that the posted code mostly works, I think that you might want to do something like change this:

Code: 
DO i = 1 TO tablbuf_hndl2:NUM-FIELDS:

  PUT UNFORMATTED
     tablbuf_hndl2:BUFFER-FIELD(i):NAME + " " +
     tablbuf_hndl2:BUFFER-FIELD(i):BUFFER-VALUE()
  SKIP.

END.

to this:

Code: 
DO i = 1 TO tablbuf_hndl2:NUM-FIELDS:

  IF LOOKUP( tablbuf_hndl2:BUFFER-FIELD(i):NAME, "field1,field2,field3" ) > 0 then

  PUT UNFORMATTED
     tablbuf_hndl2:BUFFER-FIELD(i):NAME + " " +
     tablbuf_hndl2:BUFFER-FIELD(i):BUFFER-VALUE()
  SKIP.

END.

But I could have completely misunderstood you and I really can't make much sense out of the posted code without putting a lot more thought into it than it merits right now.
 
Hi,
I am using Progress version 10.2.
I created a Customer record :
Code: 
find first customer no-error.
 
if avail customer then
 
create customer.
 
assign
 
customer.application# = 3 .
 
message rowid(customer).

The ROWID for that record is 0x000000000001c059 .
I wanted to extract the data from a ROWID field using the TO-ROWID function. I tried :
Code: 
Define temp-table Audit_History no-undo
 
  field NEW_FLD_VAL as character.
 
 
 
 
define variable tablbuf_hndl  as handle no-undo.
 
define variable qry_hndl as handle no-undo.
 
def var abc as char no-undo.
 
 
 
 
CREATE BUFFER tablbuf_hndl FOR TABLE "customer".
 
CREATE QUERY qry_hndl.
 
qry_hndl:SET-BUFFERS(tablbuf_hndl).
 
abc = "for each customer where ROWID( customer ) = TO-ROWID(string(0x000000000001c059)) no-lock:" .
 
message abc.
 
qry_hndl:QUERY-PREPARE(abc).
 
qry_hndl:QUERY-OPEN().
 
repeat:
 
 
 
IF qry_hndl:QUERY-OFF-END THEN LEAVE.
 
find first _File where _File._File-Name = "customer" NO-LOCK NO-ERROR.
 
 
 
FOR EACH _Field OF _File NO-LOCK:
 
CREATE Audit_History.
 
ASSIGN
 
Audit_History.NEW_FLD_VAL = tablbuf_hndl:BUFFER-FIELD(_Field._Field-name):BUFFER-VALUE.
 
message Audit_History.NEW_FLD_VAL .

The error I am getting is :

Code: 
field application#
 
 
 
 
** No record is available. (91)
 
 
 
 
Unable to extract BUFFER-VALUE for field application#. (7366)

Can anyone tell me that how do we extract value from a ROWID field.
Any help is appreciated.
Thanks.
 
Try something like

abc = "for each customer where ROWID( customer ) = TO-ROWID(~"0x000000000001c059~") no-lock:" .
 
I tried :
Code: 
abc = "for each customer where ROWID( customer ) = TO-ROWID(~"0x000000000001c059~") no-lock:"
It is giving the error :

* Incompatible Data types .
 
A basic example - so that you can hopefully spot your errors:

Code: 
DEFINE VARIABLE hb      AS HANDLE.
DEFINE VARIABLE crowid  AS CHAR.
 
DEFINE TEMP-TABLE tt
   FIELD cc AS CHAR.
 
hb = TEMP-TABLE tt:DEFAULT-BUFFER-HANDLE.
 
 
CREATE tt. tt.cc = "hello".
 
crowid = STRING( hb:ROWID ).
 
RELEASE tt.
 
hb:FIND-UNIQUE( SUBSTITUTE( 'where rowid( tt ) = to-rowid( "&1" )', crowid ) ).
 
MESSAGE hb::cc VIEW-AS ALERT-BOX.
 
You must log in or register to reply here.
Back
Top