[RESOLVED] SELECT Statment with vbs Functions

[eckertc1]

Hey all,

I am trying to use a Select statment inside a VBScript that calls to a function in the script for one of its select arguments. anyway, ITS NOT WORKING

the select statment WITHOUT the function works perfect, i just cant get it to work with the function. here is a sample of what i am using for code.

objDB.Execute("SELECT Colmn1, Colmn2, '" & myfunction(Colmn1) & "' As NewColmn FROM MyDB.MyTable")

myfunction(Var1)

Dim NewValue
NewValue = (Var1 / 100)
myfunction = NewValue

End Function

if i remove the "Colmn1" part of my Select Statment function call and replace it with a static value such as "111019" the code works but only for that static value, i want it to use Colmn1 value as the value for the function.


any help would be GREAT

[si_the_geek]

SQL statements run within the database itself, not within your code. You cannot call your own functions from an SQL statement - unless your functions are in the database itself.

[eckertc1]

SQL statements run within the database itself, not within your code. You cannot call your own functions from an SQL statement - unless your functions are in the database itself.

thats somewhat correct. but you CAN call a user defined function like what im trying to accomplish or the following code would not work and it does.

objDB.Execute("SELECT Colmn1, Colmn2, '" & myfunction("111111") & "' As NewColmn FROM MyDB.MyTable")

myfunction(Var1)

Dim NewValue
NewValue = (Var1 / 100)
myfunction = NewValue

End Function

In other words, i can run my function within the SQL statment if i replace the call to "Colmn1" in my function with a static value such as "111111"

does this make sence at all? the root of my problem at the moment is that i can not seem to get the value of "Colmn1" into my function.

[si_the_geek]

You have misunderstood I'm afraid.. that is not calling your function from within the SQL statement - that is appending the result of the function to a string, which is then being run as the SQL statement. What you have is the same as this:

strSQL = "SELECT Colmn1, Colmn2, '" & myfunction("111111") & "' As NewColmn FROM MyDB.MyTable"
objDB.Execute(strSQL)

No matter what you do, you can only append static values to the string (and hence, SQL statement).

To be able to run a function as you want to, the function must be inside the database itself - and unfortunately, not all database systems support that (and those that do all have different languages to do it).

An alternative method would be to re-create the function as equivalent SQL for the database system you are using, and put that into your SQL string.

[eckertc1]

Gotcha

thanks for the info Si.

I guess that leads me to the next question. is there more then one way to skin this cat. I unfortunatly will not have access to the SQL Server and have no way to add this function to the server itself (this would be too easy).

Sooo I have to find a way to get the value of Colmn1 into my function. maybe a whole new select statment to record the values of Colmn1 and pass them in one at a time to my function?

[si_the_geek]

If your function is as it appears to be (simply dividing the value by 100), you can easily put that into the SQL statement:

 "SELECT Colmn1, Colmn2, Colmn1 / 100 As NewColmn FROM MyDB.MyTable"

..if your function was much more complex, it might be better to return the values from the database first, and then work on the values it returns (but this depends on a few things, so it would be best to ask us).

By the way, are you aware that currently you aren't actually getting the results from the query? You should be assigning it to a recordset or similar, and then working with that.

[eckertc1]

Unfortunatly the function is a bit more complicated then a simple math routine

I am aware that this code as is does nothing really, it is just a snippet of the code where the problems are taking place. In my current script the results of this select statment returns a recordset to a text file.

anyway, thank you so much for all your help. at least i learned somthing today.

LESSON LEARNED: "If at first the answer looks simple, your screwed"

[si_the_geek]

There may still be a way to convert your function to SQL.. if you post it (and preferably the way you use the recordset too), we can make appropriate suggestions.


(note that I might not be able to reply for 24hours!)

[eckertc1]

Hmmm, I had a thought

Maybe handling the function in the SELECT Statement is the wrong way to go about this anyway. I have a Do While loop that sends data from the Record Set to a text file and there may be a way to accomplish what I’m trying to do here inside this loop. See the following loop for details.

DBRows = 0
Do While Not RecSet.EOF
  DBRows = DBRows + 1
For Each DBColumn In RecSet.Fields
      oFiletxt.Write(DBColumn.Value & ",")
  Next
  RecSet.MoveNext
Loop

This loop simply takes each column value in the current row and writes it to a text file along with a (Comma) to separate the fields in the text file. so that leave me with the following question; Is there a way to run my function on a single column during each loop through. See example below:

DBRows = 0
Do While Not RecSet.EOF
  DBRows = DBRows + 1
For Each DBColumn In RecSet.Fields
If DBColumn = Colmn1 Then
     oFiletxt.Write(myfunction(DBColumn.Value) & ",")
Else
      oFiletxt.Write(DBColumn.Value & ",")
End If
  Next
  RecSet.MoveNext
Loop

I’m sure I’m not using this correctly but hopefully this will spark an idea in someone more knowledgeable then me and generate a solution to this issue.

[eckertc1]

WOOT!!! Got it!!

the code I used to resolve this issue is as follows:

DBRows = 0
Do While Not RecSet.EOF
  DBRows = DBRows + 1
For Each DBColumn In RecSet.Fields
If DBColumn.Name = "Colmn1" Then
oFiletxt.Write(DBColumn.Value & ",")
oFiletxt.Write(MyFunction(DBColumn.Value) & ",")
Else
oFiletxt.Write(DBColumn.Value & ",")
      End If
  Next
  RecSet.MoveNext
Loop

thanks for all your help! your suggestions moved me in the right direction to getting this resolved.

[si_the_geek]

That looks good to me.


As you have the solution, could you please do us a little favour, and mark this thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".