Bruce,
Thanks for the keen eye! As part of my troubleshooting I commented the WHILE loop out in its entirety and got the same result. Thanks, Jim McPherson From: [email protected] <[email protected]> On Behalf Of Bruce Chitiea Sent: Tuesday, May 5, 2020 2:04 PM To: [email protected] Subject: Re[2]: [RBASE-L] - COPY command using SELECT? &docopy in the rework of Buddy's approach. Bruce ------ Original Message ------ From: "Jim McPherson" <[email protected] <mailto:[email protected]> > To: [email protected] <mailto:[email protected]> Sent: 5/5/2020 1:22:15 PM Subject: RE: [RBASE-L] - COPY command using SELECT? 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] <mailto:[email protected]> > Sent: Saturday, May 2, 2020 6:20 AM To: [email protected] <mailto:[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] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/006601d6231a%24e004ef70%24a00ece50%24%40jjcalibrations.com <https://groups.google.com/d/msgid/rbase-l/006601d6231a%24e004ef70%24a00ece50%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/em698a7390-2837-4841-ad10-8be7f406be68%40pathfinder <https://groups.google.com/d/msgid/rbase-l/em698a7390-2837-4841-ad10-8be7f406be68%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/008501d62320%24e78950f0%24b69bf2d0%24%40jjcalibrations.com.
