Hi,
We have a very simple window with a very simple browse on the order table. In the window is a customer-record available (find first customer) The query on the browse is: for each order where order.custnum = customer.custnum. This query is set in the &scoped-define query-string. If I run this window, the browser shows the orders of customer with custnum = 1.
The issue is now that we want to “grab” this query and change it but that seems not possible.
Showing the {&query-string-{&browse-name}} in a message gives this: “for each order where order.custnum = customer.custnum” and that’s the correct query-string.
But using the same query-string in a query-prepare statement (query brw_update:query-prepare(“{&query-string-{&browse-name}}”).) gives an error message because customer.Custnum is not known.
Question 1: I understand why I get error message (customer is not a buffer in the query) but what is the reason that the static preprocessor query works? The exact value of customer.custnum is not known at compile-time so there must be some translation of customer.custnum at run-time as well. So why can’t that be done with the query-prepare version?
Question 2: is there in this set up somehow a way to get the “real” executed query. In this case I want a string like this “for each order where order.custnum = 1”?
We work with OE 11.5, 32-bit on windows 8, 64-bit but I think this issue is relevant for all platforms/OE-versions.
Kind regards,
Peggy
Example:
&Scoped-define BROWSE-NAME Brw_Update
&Scoped-define QUERY-STRING-Brw_Update FOR EACH ORDER WHERE ORDER.Custnum = customer.Custnum NO-LOCK INDEXED-REPOSITION
&Scoped-define OPEN-QUERY-Brw_Update OPEN QUERY Brw_Update FOR EACH ORDER WHERE ORDER.Custnum = customer.custnum NO-LOCK INDEXED-REPOSITION.
/* Define window */
define var C-Win as widget-handle no-undo.
create window C-Win assign
hidden = yes
title = "Order from customer"
height = 16
width = 80
max-height = 16
max-width = 80
virtual-height = 16
virtual-width = 80
resize = yes
scroll-bars = no
status-area = no
bgcolor = ?
fgcolor = ?
keep-frame-z-order = yes
three-d = yes
message-area = no
sensitive = yes
triggers:
on window-close do:
delete widget C-Win.
apply "close" to this-procedure.
end.
end.
/* Define browse */
define query Brw_Update for Order scrolling.
define browse Brw_Update
query Brw_Update no-lock display
ORDER.OrderDate format "99/99/9999":U
ORDER.Ordernum format "zzz,zz9":U
ORDER.Custnum format "zzz,zz9":U
WITH NO-ROW-MARKERS SEPARATORS SIZE 70 BY 10 FIT-LAST-COLUMN.
/* Define button */
define button BUTTON-1
label "Reopen query"
size 15 by 1.14
triggers:
on choose
do:
run p_open_query.
end.
end.
/* Define frame with browse & button */
define frame DEFAULT-FRAME
BUTTON-1 at row 1.24 col 50
Brw_Update at row 3.14 col 6 help
"Select a record"
with 1 down no-box keep-tab-order overlay
side-labels no-underline three-d
at col 1 row 1
size 80 by 16 widget-id 100.
/* Enable widgets */
enable BUTTON-1 Brw_Update with frame DEFAULT-FRAME in window C-Win.
/* View frame & window */
view frame DEFAULT-FRAME in window C-Win.
view C-Win.
/* Open query for first customer (custnum = 1) */
find first Customer no-lock no-error.
{&open-query-{&browse-name}}
wait-for close of this-procedure.
procedure p_open_query:
find first Customer no-lock no-error.
message "~&query-string: {&query-string-{&browse-name}}"
view-as alert-box.
query brw_update:query-prepare("{&query-string-{&browse-name}}"). /* error customer custnum not available .... */
query brw_update:query-open ().
end procedure.
Wild guess... Try putting customer.custnum between single quotes...
Try putting the actual customer.cust-num value (123,3456,etc) into the query prepare string instead of the field reference.
In the p-open_query procedure, a better solution is to store the predicate in a variable like:
ASSIGN cPred = "FOR EACH ORDER WHERE ORDER.Custnum = " + string (customer.custnum) + " NO-LOCK INDEXED-REPOSITION ".
query brw_update:query-prepare(cPred).
query brw_update:query-open ().
You need to replace those fields/variables yourself as Kevin demonstrated
Some enhancements to Kevin's answer. Use substitute & quoter, syntax checked by webeditor ;-)
Don't forget to add some error handling .
cPred = substitute("FOR EACH ORDER WHERE ORDER.Custnum = &1 NO-LOCK INDEXED-REPOSITION ", quoter (customer.custnum)).
query brw_update:query-prepare(cPred).
query brw_update:query-open ().
I was afraid that that was the only solution I had in this case. Normally we use dynamic queries but in this particular window was that not available nor easy to implement but obviously there is no alternative :-). Back to the drawing table...
Thanks for the suggestions!!
Get the query handle
qh = QUERY qName:HANDLE.
and use it just like a dynamic query.
Get the query handle
qh = QUERY qName:HANDLE.
and use it just like a dynamic query.
Flag this post as spam/abuse.
I have, in the past, changed the query associated with a browse - although, IIRC, I had to keep the same buffer structure for it to work. In the pre-9.0 / dynamic query days, this worked well.
It'd be worth trying to see what would happen if one took a standard browse, changed the associated query to one with a different buffer structure, and saw what would happen. I'm thinking it should work, as long as the buffers the browse uses is still in in the query buffers.
> It'd be worth trying to see what would happen if one took a standard browse, changed the associated query to one with a different buffer structure, and saw what would happen.
OE 11.5 Error 8340 : To change a BROWSE's QUERY attribute, the new query must have the same number of tables as the original query. (8340)
> I'm thinking it should work, as long as the buffers the browse uses is still in in the query buffers.
I agree it should work, but
It doesn't, unless you have the same buffers in the same order as the staic query but than you can just as well do a dynamic-query prepare of the statically defined query, saves you the create and clean-up of the query.
That is exactly my issue. Actually, I think the static query should not work because the customer table in this case is not in the query as a buffer (only order is) but for some reason it works .. And I was hoping for the same behaviour while accessing the query in a dynamic way but there seems to be some "translation" difference between the 2 at run-time ...