Thanks for the suggestions. I’m running into some difficulties implementing.
I’ll start with Karen’s suggestion. This is my attempt:
OUTPUT testfile.txt
SET HEADINGS OFF
SELECT ‘COPY ‘, DSFile1 +
FROM ((Products t1 +
INNER JOIN Manufacturer t2 ON t1.MfrID = t2.MfrID) J1 +
INNER JOIN docstoredata t3 ON t3.DSModel = j1.ProdModel) J2 +
WHERE Prodcode IN +
(SELECT ProdCode +
FROM Equipment +
WHERE CustomerID IN &vCustomerList)
SET HEADINGS ON
OUTPUT SCREEN
What ends up in the testfile.txt is:
COPY Filename.pdf
And that’s expected. What I am trying to do is add the destination to the same
line of text so that the testfile.txt reads:
COPY Filename.pdf destination
I thought the obvious would be to add it to the first SELECT statement as:
SELECT ‘COPY ‘, DSFile, Destination +
But whatever follows “DSFile,” is not written to the text file.
So I tried to adapt Buddy’s solution and came up with this:
SET VAR vdocopy TEXT = NULL
DECLARE cursor99 CURSOR FOR SELECT DSFile1 +
FROM ((Products t1 +
INNER JOIN Manufacturer t2 ON t1.MfrID = t2.MfrID) J1 +
INNER JOIN docstoredata t3 ON t3.DSModel = j1.ProdModel) J2 +
WHERE Prodcode IN +
(SELECT ProdCode +
FROM Equipment +
WHERE CustomerID IN &vCustomerList )
OPEN cursor99
FETCH cursor99 INTO +
vcontrolfilename INDICATOR vi1
WHILE SQLCODE <> 100 THEN
IF vfilename IS NULL THEN
GOTO getnextone
ELSE
SET VAR vdocopy = ('COPY' & .vfilename &
.vFolderName)
&docopy
SET VAR vdocopy = NULL
ENDIF
LABEL getnextone
FETCH c1 INTO +
vfilename INDICATOR iv1
ENDWHILE
DROP CURSOR cursor99
This one bombs on the DECLARE statement with Error #11 is an undefined table
and then fails to OPEN and FETCH. I’m at a loss on this one because the SELECT
statement works fine in both R:Prompt and in Karen’s suggestion.
Any help or redirection appreciated.
Thanks,
Jim McPherson
From: 'Karen Tellef' via RBASE-L <[email protected]>
Sent: Saturday, May 2, 2020 6:20 AM
To: [email protected]
Subject: Re: [RBASE-L] - COPY command using SELECT?
Someone else had asked on this list about using such a thing to delete files
and I suggested something that could be used for this instance too:
set headings off
output testfile.txt
select 'Copy', FileNameLocation, .vDirectoryFileName FROM Table WHERE
CustomerID IN &vCustomerList
output screen
run testfile.txt
Karen
-----Original Message-----
From: Buddy Walker <[email protected] <mailto:[email protected]> >
To: [email protected] <mailto:[email protected]>
Sent: Fri, May 1, 2020 8:36 pm
Subject: RE: [RBASE-L] - COPY command using SELECT?
Jim
I assuming vfilename and vdirectoryfilename has the full path and filename
I would do something like this
Buddy
---Begin Here
SET VAR vfilename TEXT = NULL
SET VAR vdirectoryfilename TEXT = ('newFolderName')
SET VAR vdocopy TEXT = NULL
DECLARE c1 CURSOR FOR SELECT filenamelocationColumn FROM sometable +
WHERE customerid IN (&vcustomerlist)
OPEN c1
FETCH c1 INTO +
vfilename INDICATOR iv1
WHILE SQLCODE <> 100 THEN
IF vfilename IS NULL THEN
GOTO getnextone
ELSE
SET VAR vdocopy = ('COPY' & .vfilename & .vdirectoryfilename)
&docopy
SET VAR vdocopy = NULL
ENDIF
LABEL getnextone
FETCH c1 INTO +
vfilename INDICATOR iv1
ENDWHILE
DROP CURSOR c1
RETURN
--End Here
From: [email protected] <mailto:[email protected]>
<[email protected] <mailto:[email protected]> > On Behalf Of Jim
McPherson
Sent: Friday, May 1, 2020 4:49 PM
To: [email protected] <mailto:[email protected]>
Subject: [RBASE-L] - COPY command using SELECT?
All,
We keep a list of files in our database and I am looking to copy a select few
to a different location as part of the programming. I’ve sorted out the SELECT
statement. Can that be used as a source file location? If so, what’s the
syntax?
Here’s what I’m trying:
COPY (SELECT FileNameLocation FROM Table WHERE CustomerID IN &vCustomerList)
&vDirectoryFileName
I’m also open to any other ways to accomplish the task.
Thanks,
Jim
--
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/002201d61ff9%24e847ddb0%24b8d79910%24%40jjcalibrations.com
<https://groups.google.com/d/msgid/rbase-l/002201d61ff9%24e847ddb0%24b8d79910%24%40jjcalibrations.com?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]
<mailto:[email protected]> .
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/000c01d62022%241d4534e0%2457cf9ea0%24%40comcast.net
<https://groups.google.com/d/msgid/rbase-l/000c01d62022%241d4534e0%2457cf9ea0%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]
<mailto:[email protected]> .
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/766360155.151548.1588425582505%40mail.yahoo.com
<https://groups.google.com/d/msgid/rbase-l/766360155.151548.1588425582505%40mail.yahoo.com?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/006601d6231a%24e004ef70%24a00ece50%24%40jjcalibrations.com.
