SQL Question

This is not a complete code you can run, this is just a code snippet. You need to add a declaration for "rows" variable, for example

Dim rows, number

and also database connect/disconnect commands and something to do with the retrieved data.

Here is the complete code that I have

Code:

Dim exported_rows, Number
Dim message, String

Dim MySql,string

set MySql,"SELECT AC_BI.ACCOUNT_NUMBER, AC_BI.short_name, AC_BI.ADDRESS_01, AC_BI.ADDRESS_02 FROM SQLUser.AC_BI AC_BI"

// Every time this job runs it updates corresponding "run date"

// column in the AC_BI table

Dim rows, Number

Dim problem, Boolean

// connect to database described in the TRQ profile

DatabaseConnect( "TRP" )

// build data store and retrieve job record from the database

// we must include job_id in the syntax to allow updates

DatabaseRetrieve( mysql ,rows)

//NotEqual( rows, 1, problem )

IfThen( problem, stop )

// save copy of this table as a Excel 5 file

DatabaseSave( "C:\\test.xls", "xls", rows )
set message,"Complete"
// all done

DatabaseDisconnect

Exit

STOP:

It works fine with this, however, the "Set Mysql" statement that I need would be your code snippet example to do a more robust sql. However, when I use that snippet, it doesn't work.

Code:

Dim MySql, string
Dim rows, number
Dim message, String, "Invalid number of records found in table AC_BI"
Dim problem, Boolean

ConcatEx( "SELECT AC_BI.ACCOUNT_NUMBER, AC_BI.short_name, ", &
"AC_BI.ADDRESS_01, AC_BI.ADDRESS_02 ", &
"FROM SQLUser.AC_BI AC_BI", &
MySql )

// Every time this job runs it updates corresponding "run date"
// column in the AC_BI table

// connect to database described in the TRQ profile
DatabaseConnect( "TRP" )

// build data store and retrieve job record from the database
// we must include job_id in the syntax to allow updates
DatabaseRetrieve( mysql, rows)

NotEqual( rows, 1, problem )
IfThen( problem, STOP )

// save copy of this table as a Excel 5 file
DatabaseSave( "C:\\test.xls", "xls", rows )
Set( message, "Complete, data saved in C:\\test.xls file")
// all done

STOP:
DatabaseDisconnect
LogAddMessageEx( @V"job_id", "@V"job_name"", "INFO", message)

Great!

Thanks for your help.