0

I currently try to modify an sql script from a colleague as my first steps into SQL.

Basically 2 queries and output into 2 files with a static name work as intended from one sql-script input file.

However, I would like to add the current date as YYYYMMDD into the filename e.g. CONCAT(prefix, date, suffix). However, this is where I hit the wall one way or the other and end up having my files named "CONCAT", errors or output into shell.

In the end I want:

query1name20222403.txt

query2name20222403.txt

What I have so far:

set echo off ;
set list on ;
set heading on ;

out "query1name.txt";

select alotofvalues as alotofnames
 from table a
  left join othertable o on a.something = o.somethingelse
 where(some statemens)
 order by o.someval;

out "query2name.txt";

select alotofvalues as alotofnames
 from anothertable a
  left join anotherothertable o on a.something = o.somethingelse
 where(some statemens)
 order by o.someval;
out;

I hope that someone here can guide me into the right direction on how to integrate the date into my outfile names. I already got current_date+0 to display the YYYYMMDD.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Fabian Billenkamp
  • 21
  • 2

1 Answers1

1

This is not possible in ISQL. You cannot reference SQL results in the ISQL commands. You will need to solve this externally, for example by using a scripting language to generate your ISQL script with the right filename, and then call ISQL with that generated script.

Alternatively, keep your ISQL script as is, and use a scripting language to rename the files afterwards.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thanks a lot. The best reply I could hope for. Will prevent me from researching in the wrong direction. The shell script I already wrote for my colleague works, just found renaming awkward. – Fabian Billenkamp Mar 24 '22 at 23:53
  • @FabianBillenkamp If you're on Windows, look at [cmd line rename file with date and time](https://stackoverflow.com/questions/4984391/cmd-line-rename-file-with-date-and-time) and for Linx (Bash), look at [Moving a file and adding the date to the filename](https://stackoverflow.com/questions/17597775/moving-a-file-and-adding-the-date-to-the-filename) – Mark Rotteveel Mar 25 '22 at 06:27
  • Sorry, if my reply Was a bit unclear due to updating. I tried to reduce from 2 bat my colleague had to a cleaner solution. Reduced to 1 bat at least now. MV or RENAME were just tickling me a bit style wise. – Fabian Billenkamp Mar 29 '22 at 17:15