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] <mailto:[email protected]> | +011 (909) 238-9012 c
| +011 (909) 912-8678 f
------ Original Message ------
From: "A. Razzak Memon" <[email protected] <mailto:[email protected]> >
To: [email protected] <mailto:[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] <mailto:[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]
<mailto:[email protected]>
>[email protected]
<mailto:[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
<https://groups.google.com/d/msgid/rbase-l/emefc63529-209a-44f7-a4c9-390b1f709fa9%40pathfinder?utm_medium=email&utm_source=footer>
&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]
<mailto:[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]
<mailto:[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.
