ProDataSet Question...

Posted by Darrick Bush on 14-Jul-2016 15:26

Please review the code below...my question is in regards to the "FILL-WHERE-STRING".

I want to be able to have a where clause on the srceidlu AND another one for the xacctref.

I need "srceidlu.systype = "PAS" and xacctref.acct = 29

Is this possible...and if so, how?

Thanks,

Darrick

 

DEFINE VARIABLE hbfttAcctRef AS HANDLE NO-UNDO.
DEFINE VARIABLE cWhereClause AS CHARACTER NO-UNDO.
DEFINE VARIABLE iBatchSize AS INTEGER NO-UNDO.
DEFINE VARIABLE icounter AS INTEGER NO-UNDO.
DEFINE VARIABLE rrowid AS ROWID NO-UNDO.
DEFINE VARIABLE icnt AS INTEGER NO-UNDO.

DEFINE TEMP-TABLE ttAcctCrossRef NO-UNDO SERIALIZE-NAME "ttAcctCrossRef" LIKE xacctref.
DEFINE TEMP-TABLE ttsrceidlu NO-UNDO SERIALIZE-NAME "ttsrceidlu" LIKE srceidlu.

DEFINE DATASET OneTIS_AcctCrossRef FOR ttAcctCrossRef,ttsrceidlu
DATA-RELATION drLink FOR ttAcctCrossRef,ttsrceidlu RELATION-FIELDS (srceid,srceid) NESTED.
DEFINE DATA-SOURCE dsAcctCrossRef FOR xacctref.
DEFINE DATA-SOURCE dssrceidlu FOR srceidlu.
BUFFER ttAcctCrossRef:HANDLE:ATTACH-DATA-SOURCE (DATA-SOURCE dsAcctCrossRef:HANDLE).
BUFFER ttsrceidlu:HANDLE:ATTACH-DATA-SOURCE (DATA-SOURCE dssrceidlu:HANDLE).
ASSIGN hbfttAcctRef = BUFFER ttAcctCrossRef:HANDLE.
BUFFER ttAcctCrossRef:HANDLE:BATCH-SIZE = iBatchSize.

DATA-SOURCE dssrceidlu:FILL-WHERE-STRING = SUBSTITUTE("WHERE srceidlu.systype = '&1'","PAS").

REPEAT:

IF rRowid NE ? THEN
DO:
DATA-SOURCE dsAcctCrossRef:RESTART-ROWID(1) = rRowid.
END.

DATASET OneTIS_AcctCrossRef:EMPTY-DATASET().
DATASET OneTIS_AcctCrossRef:FILL().

hbfttAcctRef:WRITE-JSON("FILE", SUBSTITUTE("/sccs/work/ayb/xacct/acctcrossref_&1.json",STRING(icounter)), TRUE).

ASSIGN rRowid = DATA-SOURCE dsAcctCrossRef :NEXT-ROWID().
IF rRowid = ? THEN LEAVE.
END.

OpenEdge Development - Forum

All Replies

Posted by Peter Judge on 15-Jul-2016 03:00

Take a look at using the QUERY attribute instead. This lets you create a completely free-form query for the FILL event.

Posted by Frank Meulblok on 15-Jul-2016 03:34

[quote user="Darrick Bush"]

Please review the code below...my question is in regards to the "FILL-WHERE-STRING".

I want to be able to have a where clause on the srceidlu AND another one for the xacctref.

I need "srceidlu.systype = "PAS" and xacctref.acct = 29

 

Is this possible...and if so, how?

...

DEFINE DATA-SOURCE dsAcctCrossRef FOR xacctref.
DEFINE DATA-SOURCE dssrceidlu FOR srceidlu.
BUFFER ttAcctCrossRef:HANDLE:ATTACH-DATA-SOURCE (DATA-SOURCE dsAcctCrossRef:HANDLE).
BUFFER ttsrceidlu:HANDLE:ATTACH-DATA-SOURCE (DATA-SOURCE dssrceidlu:HANDLE).
ASSIGN hbfttAcctRef = BUFFER ttAcctCrossRef:HANDLE.
BUFFER ttAcctCrossRef:HANDLE:BATCH-SIZE = iBatchSize.

DATA-SOURCE dssrceidlu:FILL-WHERE-STRING = SUBSTITUTE("WHERE srceidlu.systype = '&1'","PAS").

...

[/quote]

Since you're using two datasources for the 2 different tables, specifying an appropriate fill-where-string for  each of them should work.

In this case that'd be adding a line:

DATA-SOURCE dsAcctCrossRef:FILL-WHERE-STRING = SUBSTITUTE("WHERE xacctref.acct = &1","29"). 

somewhere before the FILL().

Posted by Darrick Bush on 15-Jul-2016 08:02

I tried that...and it ignores the 2nd one.

Posted by Frank Meulblok on 20-Jul-2016 06:59

Ah. spotted another gotcha.

Data-source for the child table will already get an automatically generated FILL-WHERE-STRING based on the data-relations. And you'll want to append to that, not overwrite it.

Quick sample using sports2000 database schema follows. This will fetch customers 1-4, all shipped orders for those, and nothing outside those specifications:

define temp-table ttCustomer no-undo like customer .

define temp-table ttOrder no-undo like order .

define dataset dsCustOrd for ttCustomer,ttOrder
data-relation relCustord for ttCUstomer,ttOrder relation-fields(custnum,custnum) nested.

define data-source srcCustomer for Customer.
define data-source srcOrder for Order.

buffer ttCustomer:attach-data-source(data-source srcCustomer:handle).
buffer ttOrder:attach-data-source(data-source srcOrder:handle).

message data-source srcOrder:fill-where-string. 
data-source srcOrder:fill-where-string = data-source srcOrder:fill-where-string + " and orderstatus = 'shipped' ".
message data-source srcOrder:fill-where-string.

data-source srcCustomer:fill-where-string = "where custnum < 5".

dataset dsCustOrd:FILL().
dataset dsCustOrd:write-json("FILE","custord.json",TRUE).

/* validate outcomes. */
for each ttCustomer, each ttOrder of ttcustomer:
disp ttcustomer.custnum ttorder.ordernum ttorder.orderstatus.
end.

message can-find (first ttorder where ttorder.custnum >= 5).
message can-find (first ttorder where ttorder.orderstatus <> "Shipped").

This thread is closed