Prodataset issue - OpenEdge Development - Forum

All Replies

Posted by Elsworth Burmeister on 24-Feb-2015 00:18

Hi I think your problem lies with your query….. its defined for customer and order

DEFINE QUERY qOrder FOR CUSTOMER, ORDER.

Your Temp tables are like order and order line…. So your query should contain both these tables.

DEFINE TEMP-TABLE ttOrder LIKE order.

DEFINE TEMP-TABLE ttOrderLine LIKE orderline

  FIELD ProductRef AS CHAR

  FIELD ProductDescription AS CHAR.

Then your datasource needs to contain keys for both order and orderline tables.

DEFINE DATA-SOURCE srcOrderLine FOR ORDERLINE KEYS (ORDER_NUMBER), PRODUCT KEYS (PRODUCT_NUMBER).

Then your query prepare should contain the tables referenced in your table definitions…

Try doing it this way… the following code is take from the documentation and might not work as it is.

DEFINE TEMP-TABLE ttOrder LIKE Order

FIELD OrderTotal AS DECIMAL

FIELD CustName LIKE Customer.Name

FIELD RepName LIKE SalesRep.RepName.

DEFINE TEMP-TABLE ttOline LIKE OrderLine.

DEFINE DATASET dsOrder FOR ttOrder, ttOline

DATA-RELATION OrderLine FOR ttOrder, ttOline

RELATION-FIELDS (OrderNum, OrderNum).

DEFINE QUERY qOrder FOR Order, OrderLine.

DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder.

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = 1,EACH orderline OF order").

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE,"Customer.Name,CustName").

DATASET dsOrder:FILL().

BUFFER ttOrder:DETACH-DATA-SOURCE().

Elsworth Burmeister

Developer - Managed Services

Cell: +27 83 777 3072 Email: eburmeister@elcb.co.za

ELCB Information Services (Pty) Ltd

Customer Service Email elcb@elcb.co.za · www.elcb.co.za

E A S T L O N D O N

Tel: +27(43) 704 0700

Fax: +27(43) 704 0701

J O H A N N E S B U R G

Tel: +27(11) 879 6179

Fax: +27(11) 454 0384

P O R T E L I Z A B E T H

Tel: +27(41) 373 0529

Fax: +27(86) 650 0135

Disclaimer

[collapse]

From: oedev [mailto:bounce-oedev@community.progress.com]
Sent: 23 February 2015 05:52 PM
To: TU.OE.Development@community.progress.com
Subject: [Technical Users - OE Development] Prodataset issue

Prodataset issue

Thread created by oedev

Taking the following example code, I can an error when running "Must assign a user query to a data-source that is a join before filling" (11980)

I've tried changing the data source srcOrderLine to a query as well (on the tables ORDER_LINE and PRODUCT), but I then get all the orderline records in the database.

Must be doing something obvious and simple wrong?!

DEFINE TEMP-TABLE ttOrder LIKE order.

DEFINE TEMP-TABLE ttOrderLine LIKE orderline

  FIELD ProductRef AS CHAR

  FIELD ProductDescription AS CHAR.

DEFINE DATASET dsOrder FOR ttOrder, ttOrderLine

  DATA-RELATION OrderOrderLine FOR ttOrder, ttOrderLine

    RELATION-FIELDS (ttOrder.order_number, ttOrderLine.order_number).

DEFINE QUERY qOrder FOR CUSTOMER, ORDER.

DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder.

DEFINE DATA-SOURCE srcOrderLine FOR ORDERLINE KEYS (ORDER_NUMBER), PRODUCT KEYS (PRODUCT_NUMBER).

QUERY qOrder:QUERY-PREPARE("FOR EACH CUSTOMER WHERE CUSTOMER_REFERENCE = '1111111' NO-LOCK, " +

"LAST ORDER OF CUSTOMER NO-LOCK").

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE).

BUFFER ttOrderLine:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrderLine:HANDLE).

DATASET dsOrder:FILL().

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by oedev on 24-Feb-2015 01:30

Thanks for the reply, but not sure that works for me.

Probably a better example (with a different error!) is against the sports 2000 database. Take the example below, which gives an error stating that "Must assign a user query to a data-source that is a join before filling" 11980

DEFINE TEMP-TABLE ttOrder LIKE-SEQUENTIAL Order
    FIELD OrderTotal AS DECIMAL
    FIELD CustName LIKE Customer.Name
    FIELD RepName LIKE SalesRep.RepName.

DEFINE TEMP-TABLE ttOline LIKE-SEQUENTIAL OrderLine
    FIELD ItemName LIKE ITEM.ItemName
    FIELD Weight LIKE ITEM.Weight
    FIELD OnHand LIKE ITEM.OnHand
    FIELD OnOrder LIKE ITEM.OnOrder. 

DEFINE DATASET dsOrder FOR ttOrder, ttOline
    DATA-RELATION OrderToOrderLine FOR ttOrder, ttOline
        RELATION-FIELDS (OrderNum, OrderNum).

DEFINE QUERY qOrder FOR Order, Customer, SalesRep.

DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum),
Customer KEYS (CustNum), SalesRep KEYS (SalesRep).

DEFINE DATA-SOURCE srcOline FOR OrderLine KEYS (OrderNum), Item KEYS (ItemNum).

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = 1, " +
"FIRST Customer OF Order, FIRST SalesRep OF Order").

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE,
"Customer.Name,CustName").
BUFFER ttOline:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE).

DATASET dsOrder:FILL().

BUFFER ttOrder:DETACH-DATA-SOURCE().
BUFFER ttOline:DETACH-DATA-SOURCE().

So, replacing the second datasource to reference a query, but that gives an error 7327 (because I've referenced the order table), however, removing the reference to the order table means I get every order line the database;

DEFINE TEMP-TABLE ttOrder LIKE-SEQUENTIAL Order
    FIELD OrderTotal AS DECIMAL
    FIELD CustName LIKE Customer.Name
    FIELD RepName LIKE SalesRep.RepName.

DEFINE TEMP-TABLE ttOline LIKE-SEQUENTIAL OrderLine
    FIELD ItemName LIKE ITEM.ItemName
    FIELD Weight LIKE ITEM.Weight
    FIELD OnHand LIKE ITEM.OnHand
    FIELD OnOrder LIKE ITEM.OnOrder. 

DEFINE DATASET dsOrder FOR ttOrder, ttOline
    DATA-RELATION OrderToOrderLine FOR ttOrder, ttOline
        RELATION-FIELDS (OrderNum, OrderNum).

DEFINE QUERY qOrder FOR Order, Customer, SalesRep.
DEFINE QUERY qOrderLine FOR OrderLine, ITEM.

DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum),
Customer KEYS (CustNum), SalesRep KEYS (SalesRep).

DEFINE DATA-SOURCE srcOline FOR QUERY qOrderLine OrderLine KEYS (LineNum),
ITEM KEYS (ItemNum).

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = 1, " +
"FIRST Customer OF Order, FIRST SalesRep OF Order").

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE,
"Customer.Name,CustName").

QUERY qOrderLine:QUERY-PREPARE("FOR EACH OrderLine OF  Order, " +
"FIRST Item OF OrderLine").

BUFFER ttOline:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE).

DATASET dsOrder:FILL().

BUFFER ttOrder:DETACH-DATA-SOURCE().
BUFFER ttOline:DETACH-DATA-SOURCE().

Posted by Håvard Danielsen on 24-Feb-2015 08:33

Child data sources need to be joined to the parent temp-table, ttOrder in this case. The prepare has to be done after the attach for this to be allowed.

---------

BUFFER ttOline:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE).

QUERY qOrderLine:QUERY-PREPARE("FOR EACH OrderLine OF  ttOrder, " +
"FIRST Item OF OrderLine")

Posted by Matt Gilarde on 24-Feb-2015 09:11

This topic is being discussed in another thread. See

community.progress.com/.../16159.aspx

Posted by oedev on 24-Feb-2015 09:47

Thanks, does the trick. Was that in the documentation ?

Posted by Håvard Danielsen on 24-Feb-2015 16:15

Yes, the "ProDataSets, by John Sadd (Expert Series)" document discusses this in the "Defining a query on a child table" section.

This thread is closed