Buddy: Good call.
By replacing the embedded ampersand variables with literals in your
fashion, and tidying up a bit, this string did the job:
SET VAR vcol_date TEXT = 'cpoDate'
SET VAR vtableName TEXT = 'cpoHeader'
SET VAR vstring TEXT = +
('SELECT (MIN(' + .vcol_date + ')) INTO vfts_dateStart INDICATOR vind1
FROM' +
& .vtableName & 'WHERE (IYR(' + .vcol_date + ')) =' &
(CTXT(.vyearStart)) )
&vstring
Perhaps I was "trying too hard", by generating and then embedding
ampersand variables for the functions into an ampersand variable
"shell".
Still unclear on why mixing embedded ampersand variables with an
embedded dot variable should break; but I'll take the win. Learned a
lot.
Thanks Razzak, Karen, Doug and Buddy
Merry Christmas, and the happiest and most relaxing holidays to all.
Bruce
Bruce A. Chitiea | SafeSectors, Inc.
112 Harvard Ave #272 | Claremont CA 91711-4716 | USA
[email protected] | +011 (909) 238-9012 c | +011 (909) 912-8678 f
------ Original Message ------
From: "Buddy Walker" <[email protected]>
To: [email protected]
Sent: 12/11/2021 4:51:25 AM
Subject: RE: Re[2]: [RBASE-L] - Ampersanding in the Dark
Bruce
I believe you are using the & wrong when setting up the string.
To concantenate the string the & indicates a space
Added vyearStart as integer which I assume is the 4 digit you are
setting
I commented out two variables don’t need them unless you are using
them elsewhere in the code.
Try this
***********start here
SET VAR vcol_date TEXT = 'cpoDate'
SET VAR vtableName TEXT = 'cpoHeader'
--SET VAR vamp_dateCol_min TEXT = ('(MIN(' + .vcol_date + '))')
--SET VAR vamp_dateCol_iyr4 TEXT = ('(IYR4(' + .vcol_date + '))')
SET VAR vyearStart INTEGER = 2020
SET VAR vstring TEXT = +
('SELECT MIN(' + .vcol_Date + ') INTO vfts_dateStart INDICATOR vind1
FROM' & .vtableName & 'WHERE (IYR(' + .vcol_date + ')) =' &
CTXT(.vyearStart))
&vstring
*********end here
Buddy
From:[email protected] <[email protected]> On Behalf Of
Bruce Chitiea
Sent: Friday, December 10, 2021 8:08 PM
To:[email protected]
Subject: Re[2]: [RBASE-L] - Ampersanding in the Dark
Razzak: Thank you.
If I understand your sample, my code should read:
SET VAR vcol_date TEXT = 'cpoDate'
SET VAR vtableName TEXT = 'cpoHeader'
SET VAR vamp_dateCol_min TEXT = ('(MIN(' + .vcol_date + '))')
SET VAR vamp_dateCol_iyr4 TEXT = ('(IYR4(' + .vcol_date + '))')
SET VAR vstring TEXT = +
('SELECT &vamp_dateCol_min INTO vfts_dateStart INDICATOR vind1 FROM'
& .vtableName & 'WHERE &vamp_dateCol_iyr4 = .vyearStart')
&vstring
...which returns the error: "Column or variable MIN not found (2515)"
Perplexing. But! Progress. By defining the "vamp_" variables in your
correct syntax (as shown and run above), this code now works:
SELECT &vamp_dateCol_min INTO vfts_dateStart INDICATOR vind1 +
FROM &vtableName WHERE &vamp_dateCol_iyr4 = .vyearStart
Sadly, if I insert this code into parens as the vstring definition, the
error: "Syntax is incorrect for the command SELECT (2045)" is returned.
So, still one level of mystery remains.
Best wishes, Bruce
Bruce A. Chitiea | SafeSectors, Inc.
112 Harvard Ave #272 | Claremont CA 91711-4716 | USA
[email protected] | +011 (909) 238-9012 c | +011 (909) 912-8678 f
------ Original Message ------
From: "A. Razzak Memon" <[email protected]>
To: [email protected]
Sent: 12/10/2021 1:41:19 PM
Subject: Re: [RBASE-L] - Ampersanding in the Dark
Bruce,
Try this very simple approach to form a command with correct syntax to
execute.
SET VAR vTableName TEXT = 'cPOHeader'
SET VAR vString TEXT = ('SELECT ... FROM'&.vTableName)
&vString
See how the variable "vString" is executed.
Have fun!
Razzak
At 04:21 PM 12/10/2021, Bruce Chitiea wrote:
Gurus and Gurettes:
My greatest coding challenge remains the use of ampersand variables.
I get the concept, but my execution ... hoo boy.
So here's what works:
  SELECT (MIN(cpoDate)) INTO vdateStart INDIC vind1 +
   FROM cpoHeader WHERE (IYR4(cpoDate)) = .vyearStart
But I need to sub-out the table name, and column name 'cpoDate' with
the likes of 'invDate', 'shipDate', etc.Â
This does the trick for the column name:
  SET VAR vcol_date TEXT = 'cpoDate'
  SET VAR vamp_dateCol_min TEXT = '(MIN(&vcol_date))'
  SET VAR vamp_dateCol_iyr4 TEXT = '(IYR4(&vcol_date))'
  SELECT &vamp_dateCol_min INTO vfts_dateStart INDICATOR vind1 +
   FROM cpoHeader WHERE &vamp_dateCol_iyr4 = .vyearStart
... but only so long as the literal tablename 'cpoHeader' is included
in the FROM clause.
This returns a syntax error:
  SET VAR vtableName TEXT = 'cpoHeader'
  SELECT ... FROM .vtableName ...
... as does this:
  SET VAR vtableName TEXT = 'cpoHeader'
  SELECT ... FROM &vtableName ...
... as does this:
  SET VAR vtableName TEXT = 'cpoHeader'
  SET VAR vamp_from_tablename TEXT = 'FROM .vtableName'
  SELECT ... &vamp_from_tableName ...
... as does this
  SET VAR vtableName TEXT = 'cpoHeader'
  SET VAR vamp_from_tablename TEXT = 'FROM &vtableName'
  SELECT ... &vamp_from_tableName ...
Clearly, I'm flailing. Any assistance much appreciated!Â
Bruce
Bruce A. Chitiea |Â SafeSectors, Inc.
112 Harvard Ave #272 |Â Claremont CA 91711-4716 |Â USA
[email protected]Â |Â +011 (909) 238-9012 c |Â +011 (909)
912-8678 f
--
For group guidelines, visit
<http://www.rbase.com/support/usersgroup_guidelines.php>http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to
<mailto:[email protected]>[email protected].
To view this discussion on the web visit
<https://groups.google.com/d/msgid/rbase-l/emefc63529-209a-44f7-a4c9-390b1f709fa9%40pathfinder?utm_medium=email&utm_source=footer>https://groups.google.com/d/msgid/rbase-l/emefc63529-209a-44f7-a4c9-390b1f709fa9%40pathfinder.
-- For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
--- You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/1MTisb-1n7aFe3sNR-00U4Df%40mrelay.perfora.net.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/em69075add-1a16-48d4-8b1b-b0f7e51c4b4c%40pathfinder
<https://groups.google.com/d/msgid/rbase-l/em69075add-1a16-48d4-8b1b-b0f7e51c4b4c%40pathfinder?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/003401d7ee8d%24cf881380%246e983a80%24%40comcast.net
<https://groups.google.com/d/msgid/rbase-l/003401d7ee8d%24cf881380%246e983a80%24%40comcast.net?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/em226ef899-97c9-4b0d-8840-3b2e573a17e0%40pathfinder.
