SQL Query from within Excel using date variables Quote:Hi there,
I was wondering if your SQL XL software would allow me to run normal SQL queries within VBA code of an excel file?
Here is an example query:
SELECT
Q.NAME AS QueueName,
CONVERT(CHAR(10),fnLocalTime(QH.TIME),101) AS Date,
CONVERT(CHAR(10),fnLocalTime(QH.TIME),8) AS Time,
QH.CALLVOLUME AS ContactVolume,
QH.ABANDONS AS Abandons
FROM
dbo.HISTORYTIMESERIES QH
INNER JOIN
dbo.QUEUE Q
ON
QH.QUEUEID = Q.ID
WHERE
fnLocalTime(QH.TIME) BETWEEN DateFrom AND DateTo
AND Q.NAME = QueueName
ORDER BY
QH.TIME
This query returns 4 columns of data of a specified queue and timeframe.
I am wanting to make this query have variables of the dates and run in VBA.
Does your program make this possible to run normal queries like this?
This is what SQL*XL does best. Absolutely no problem.
What I recommend you to do is to get the trial version from
www.oraxcel.com/downloads. You can work 10 days to see whether it does what you want it to do.
After install, first use it interactively. The VBA is as simpel as recording a macro. Do that as a last step.
Try to connect to your database. The connection wizard is recommended to be used. Just go to the connection dialog and press new...
Then, run your query without parameters first. Open the SQL dialog and type the SQL. You can save the SQL as a favorite or as a file for convenience...
Parameterise your query. Please note that time parameters are never easy. Decide between substitution variables (text replacement variables really) or bind variables (true dates).
A substitution variable is prefixed with & and a bind var with a :. In the following a is a subst var and b is a bind var:
select * from mytable where cola = &a and colb = :b
In general substitution variables have the least unexpected behaviour but bind variables tend to be more accurate. Be aware for implicit conversions with these parameters. The usual source of problems.
Finally, when you have got your parameterised query ready to use record a macro. Restart Excel so SQL*XL does not remember parameter definitions etc and then record a session where you connect, run the SQL, define the values and disconnect. Observe what SQL*XL generated. First test it as it is. Then tweak the code. You may want to prompt for variables, etc.
Pages: 1