Realising that variables must be concatenated to QUERY-PREPARE statements du to the fact that they are parsed at run-time, I still have what must be a novice's problem in that the famous error (7328) resolves the value of the data variable and tries to parse it as the variable name. The error message comes back as...
1A12343 must be a quoted constant or an unabbreviated, unambiguous buffer/field reference for buffers known to query qStaticData. (7328)
'1A2343' is the value of p-part-num input parameter to the following code...
At the risk of looking rather foolish, can anyone see the problem?
(version 10.2a database linux 64 bit host connected to windows 7 OE Architect client V10.2b )
------------------------------------------------------------------------------------------------------------------
DEFINE TEMP-TABLE ttStaticData
NO-UNDO BEFORE-TABLE before_ttStaticData
FIELD part-num AS CHARACTER LABEL "Part Num" FORMAT "x(20)"
FIELD part-desc AS CHARACTER LABEL "Name" FORMAT "x(15)"
FIELD part-desc2 AS CHARACTER LABEL "Desc" FORMAT "x(15)"
FIELD part-note AS CHARACTER EXTENT 2 LABEL "Part Note" FORMAT "x(210)"
FIELD com-code AS CHARACTER LABEL "Comm Code" FORMAT "x(4)"
INDEX part-num-idx
part-num ASCENDING
.
DEFINE DATASET dsPartsDataCurrent FOR ttStaticData.
/* Data-source definitions */
DEFINE QUERY qStaticData FOR i-partdesc FIELDS(part-num part-desc part-desc2 part-note com-code).
DEFINE DATA-SOURCE srcStaticData FOR QUERY qStaticData.
DEF INPUT PARAMETER p-part-num AS CHAR FORMAT "x(20)" NO-UNDO.
DEF INPUT PARAMETER p-class AS CHAR FORMAT "x(1)" NO-UNDO.
DEF INPUT PARAMETER p-cust-num AS INT NO-UNDO.
DEF INPUT PARAMETER p-sold AS LOGICAL NO-UNDO.
DEF OUTPUT PARAMETER p-return-status AS CHAR NO-UNDO.
DEF OUTPUT PARAMETER dataset FOR dsPartsDataCurrent.
/* *************************** Main Block *************************** */
/* Attach first data source */
BUFFER ttStaticData:attach-data-source
(DATA-SOURCE srcStaticData:handle).
QUERY qStaticData:query-prepare("for each i-partdesc
where i-partdesc.part-num = " + string(p-part-num)).
DATASET dsPartsDataCurrent:FILL().
Apologies for the couple of typos....the important one being that the error message should read...
1A2343 must be a quoted constant or an unabbreviated, unambiguous buffer/field reference for buffers known to query qStaticData. (7328)
Hey Steve,
Did you try to set the search value into quotes? That would mean changing your prepare code as follows
QUERY qStaticData:query-prepare("for each i-partdesc
where i-partdesc.part-num = " + QUOTER (string(p-part-num))).
The reason is that you ate searching a field of the type CHAR.
Regards,
Marko
Magic!!
You're a star Marko...that works fine now.
Many thanks.
:-)
marko.rueterbories wrote:
Hey Steve,
Did you try to set the search value into quotes? That would mean changing your prepare code as follows
QUERY qStaticData:query-prepare("for each i-partdesc
where i-partdesc.part-num = " + QUOTER (string(p-part-num))).
The reason is that you ate searching a field of the type CHAR.
Regards,
Marko
I recommend to always use SUBSTRING in cases like this (especially QUERY-PREPARE):
QUERY qStaticData:query-prepare(SUBSTRING("for each i-partdesc where i-partdesc.part-num = '&1'",
p-part-num)).
or if you prefer:
QUERY qStaticData:query-prepare(SUBSTRING("for each i-partdesc where i-partdesc.part-num = &1",
QUOTER(STRING(p-part-num)))).
for the following reasons:
1. SUBSTRING makes your code more readable, as you see the structure of the resulting expression
2. SUBSTRING does automatic data type conversions (see first example)
3. SUBSTRING protects against the unknown value (?). Without substring any unknown value in the concatenation results in an unknown value for the entire string, which makes it hard to debug.
If you had used SUBSTRING then reason #1 had probably helped you prevent this problem.
HTH
-peter
I recommend to always use SUBSTRING in cases like this (especially QUERY-PREPARE):
Hey Peter,
I feel you meant SUBSTITUTE instead of SUBSTRING.
Regards,
Marko
I think that you mean SUBSTITUTE ....
Julian
I recommend to always use SUBSTRING in cases like this (especially QUERY-PREPARE):
SUBSTITUTE, not SUBSTRING.
Thanks all for the pickup.
SUBSTITUTE it is!
Steve
marko.rueterbories wrote:
I recommend to always use SUBSTRING in cases like this (especially QUERY-PREPARE):
Hey Peter,
I feel you meant SUBSTITUTE instead of SUBSTRING.
Regards,
Marko
Of course!
for the following reasons:
1. SUBSTRING makes your code more readable, as you see the structure of the
resulting expression
2. SUBSTRING does automatic data type conversions (see first example)
3. SUBSTRING protects against the unknown value (?). Without substring any
unknown value in the concatenation results in an unknown value for the entire
string, which makes it hard to debug.
If you had used SUBSTRING then reason #1 had probably helped you prevent this
problem.
>
The one thing that SUBSTITUTE() doesn't do, is take care of single- or double-quotes for you. QUOTER() does that - so you don't need to figure out whether you need a single or double quote in your string, and which belongs where, and whether you’re the variable will break that if it itself contains quotes.
QUOTER() also deals with the unknown value nicely. Also bear in mind that SUBSTITUTE() has a limit of 9 replacement values, so if you're working with a more complex query, you'll need multiple SUBSTITUTES and then #1 goes out the window.
-- peter