Query Dynamically

[sharika_ganju]

Hi,

I had a query. Request someone to please help me for the same. I am trying to query a table based on values defined in another table.
The scenerio is something like this -

I have a table xyz where there are 3 fields -
1) fieldname
2) fieldvalue
3) field description

eg lets suppose the records could be like
1) ad_state 2) US 3) United States of America

I have another table yyy which has to be queried according to the values defined in the first table xyz

So something like
find first yyy where yyy.ad_state = xyx.ad_state and
yyy.<ad_state field value> = Any of the fieldvalues defined in the xyz table or
yyy.<ad_state field value> = Any of the field description defined in xyz table no-lock no-error.
if available yyy then do:
........

end.

How can I perform such an operation. I tried using dynamic queries but as I am not that comfortable with dynamic queries I could not perform this query.

Can someone pls help.

Thanks in Advance.
Sharika.

 

[RKR]

Hi,

I had a query. Request someone to please help me for the same. I am trying to query a table based on values defined in another table.
The scenerio is something like this -

I have a table xyz where there are 3 fields -
1) fieldname
2) fieldvalue
3) field description

eg lets suppose the records could be like
1) ad_state 2) US 3) United States of America

I have another table yyy which has to be queried according to the values defined in the first table xyz

So something like
find first yyy where yyy.ad_state = xyx.ad_state and
yyy.<ad_state field value> = Any of the fieldvalues defined in the xyz table or
yyy.<ad_state field value> = Any of the field description defined in xyz table no-lock no-error.
if available yyy then do:
........

end.

How can I perform such an operation. I tried using dynamic queries but as I am not that comfortable with dynamic queries I could not perform this query.

Can someone pls help.

Thanks in Advance.
Sharika.

I have 2 samples for you using the sports2000 database:
The first is the normal approach using static FOR EACH statement and the other is by using a dynamic query.

=====================================================

/* Using static query */
DEFINE TEMP-TABLE ttOrder LIKE Order.
FOR EACH customer WHERE Customer.CustNum = 10 NO-LOCK:
FOR EACH order WHERE order.custnum = customer.custnum NO-LOCK:
CREATE ttOrder.
BUFFER-COPY order TO ttOrder.
END.
END.
FOR EACH ttOrder:
DISP ttOrder.
END.

/* Using dynamic query */
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO.
DEFINE TEMP-TABLE ttOrder LIKE Order.
FIND customer WHERE customer.custnum = 10 NO-LOCK NO-ERROR.
IF AVAILABLE customer THEN
DO:
CREATE QUERY hQuery.
CREATE BUFFER hBuffer FOR TABLE "Order".
hQuery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE("FOR EACH Order NO-LOCK WHERE Order.CustNum = " + STRING(customer.custnum)).

hQuery:QUERY-OPEN.
hQuery:GET-FIRST.
DO WHILE NOT hQuery:QUERY-OFF-END:
CREATE ttOrder.
BUFFER ttOrder:BUFFER-COPY(hBuffer).
hQuery:GET-NEXT.
END.
DELETE OBJECT hBuffer.
DELETE OBJECT hQuery.
END.
FOR EACH ttOrder:
DISP ttOrder.
END.