DSXchange

I'm working on a project which requires me to read a timestamp from a file and then use that within a WHERE clause. How can I accomplish this?

The only way you can use a variable in a where clause is using a job parameter. Not quite true because you can also use the variable to create an SQL file on the fly and then run the SQL file.

In both cases you first have to get the timestamp. On UNIX this is trivial and fairly common. On Windows you can do this using VBScript, a DOS batch file (maybe) or using DataStage itself. As far as I'm aware, there is no DataStage BASIC function that can return the timestamp of a file. If someone out there knows of one, please let us all know.

My solution would be to list the directory and redirect output to a file. The file can then be parsed to extract the required value. Once you have that value, you can pass it to another job which executes the query.

I do not need to extract the timestamp of the file. there is an actual timestamp string in the txt file. thats all it holds.

jzparad wrote:The only way you can use a variable in a where clause is using a job parameter. Not quite true because you can also use the variable to create an SQL file on the fly and then run the SQL file.

In both cases you first have to get the timestamp. On UNIX this is trivial and fairly common. On Windows you can do this using VBScript, a DOS batch file (maybe) or using DataStage itself. As far as I'm aware, there is no DataStage BASIC function that can return the timestamp of a file. If someone out there knows of one, please let us all know.

My solution would be to list the directory and redirect output to a file. The file can then be parsed to extract the required value. Once you have that value, you can pass it to another job which executes the query.

That makes it much easier. You can create a job sequence with a routine that reads the file and returns the timestamp. The value can then be passed to a job activity that calls your main job.

Lot's of examples on reading a sequential file on this forum.