Visual Basic and Informix problem
OK, I am running the following query in ISQL and it works fine. However,
when I put the same query into visual basic I get a syntax error. I have
several other queries in VB that I sue the access Informix, and they work
fine. Any input would be appreciated.
Jeff
SELECT trunkgrp_name, rate_sch_name, out_rate_sch,
SUM(out_duration) / 60 AS Minutes, SUM(out_cost) as Cost,
SUM(CASE WHEN outbound_duration > 0 THEN 1 ELSE 0 END) AS Attempts,
SUM(CASE WHEN answered_duration > 0 THEN 1 ELSE 0 END) AS Calls
From master_call, rate_schedule, trunk_group
WHERE master_call.out_rateplan = rate_schedule.rate_plan_kf AND
master_call.out_rate_sch = rate_schedule.rate_sch_id_k AND
rate_schedule.rate_plan_kf = trunk_group.trunkgrp_num_k AND
trunkgrp_name='Direct to Philippines'
GROUP BY trunkgrp_name, rate_sch_name, out_rate_sch
ORDER BY trunkgrp_name, rate_sch_name;
Here is all of the VB code. I simply changed the .commandtext with one that
works somewhere else in the application, and it ran fine. It just seems to
be this paticular query. Heres the VB code;
On Error GoTo Error_Handler
If rstRecords.State = adStateOpen Then
rstRecords.Close
Set rstRecords = Nothing
Set cmmRecords = Nothing
End If
'
With cmmRecords
.CommandText = "SELECT trunkgrp_name, rate_sch_name, out_rate_sch,
" _
& "SUM(out_duration) / 60 AS Minutes, SUM(out_cost) as Cost,
" _
& "SUM(CASE WHEN outbound_duration > 0 THEN 1 ELSE 0 END) AS
Attempts, " _
& "SUM(CASE WHEN answered_duration > 0 THEN 1 ELSE 0 END) AS
Calls " _
& "From master_call, rate_schedule, trunk_group " _
& "WHERE master_call.out_rateplan = rate_schedule.rate_plan_kf
AND " _
& "master_call.out_rate_sch = rate_schedule.rate_sch_id_k
AND " _
& "rate_schedule.rate_plan_kf = trunk_group.trunkgrp_num_k
AND " _
& "trunkgrp_name='Direct to Philippines' " _
& "GROUP BY trunkgrp_name, rate_sch_name, out_rate_sch " _
& "ORDER BY trunkgrp_name, rate_sch_name;"
.CommandType = adCmdText
.ActiveConnection = cnnSQL
End With
'
With rstRecords
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open cmmRecords
End With
'
Exit Sub
Error_Handler:
Dim errLoop As Error
Dim strError As String
Dim strTMP As String
Dim intC As Integer
'
On Error Resume Next
'
intC = 1
strTMP = strTMP & vbCrLf & "VB Error # " & Str(Err.Number)
strTMP = strTMP & vbCrLf & " Generated by " & Err.Source
strTMP = strTMP & vbCrLf & " Description " & Err.Description
' Enumerate Errors collection and display properties of
' each Error object.
Set errSQL = cnnSQL.Errors
For Each errLoop In errSQL
With errLoop
strTMP = strTMP & vbCrLf & " ADO Error #" & .Number
strTMP = strTMP & vbCrLf & " Description " & .Description
strTMP = strTMP & vbCrLf & " Source " & .Source
intC = intC + 1
End With
Next
MsgBox strTMP
' Clean up Gracefully
On Error Resume Next
'GoTo Done
Does VB support the CASE statement within a query?
Jeff Bluemel wrote:
> OK, I am running the following query in ISQL and it works fine. However,
> when I put the same query into visual basic I get a syntax error. I have
> several other queries in VB that I sue the access Informix, and they work
> fine. Any input would be appreciated.
>
> Jeff
>
> SELECT trunkgrp_name, rate_sch_name, out_rate_sch,
> SUM(out_duration) / 60 AS Minutes, SUM(out_cost) as Cost,
> SUM(CASE WHEN outbound_duration > 0 THEN 1 ELSE 0 END) AS Attempts,
> SUM(CASE WHEN answered_duration > 0 THEN 1 ELSE 0 END) AS Calls
> From master_call, rate_schedule, trunk_group
> WHERE master_call.out_rateplan = rate_schedule.rate_plan_kf AND
> master_call.out_rate_sch = rate_schedule.rate_sch_id_k AND
> rate_schedule.rate_plan_kf = trunk_group.trunkgrp_num_k AND
> trunkgrp_name='Direct to Philippines'
> GROUP BY trunkgrp_name, rate_sch_name, out_rate_sch
> ORDER BY trunkgrp_name, rate_sch_name;>
> Here is all of the VB code. I simply changed the .commandtext with one that
> works somewhere else in the application, and it ran fine. It just seems to
> be this paticular query. Heres the VB code;
>
> On Error GoTo Error_Handler
> If rstRecords.State = adStateOpen Then
> rstRecords.Close
> Set rstRecords = Nothing
> Set cmmRecords = Nothing
> End If
> '
> With cmmRecords
> .CommandText = "SELECT trunkgrp_name, rate_sch_name, out_rate_sch,
> " _
> & "SUM(out_duration) / 60 AS Minutes, SUM(out_cost) as Cost,
> " _
> & "SUM(CASE WHEN outbound_duration > 0 THEN 1 ELSE 0 END) AS
> Attempts, " _
> & "SUM(CASE WHEN answered_duration > 0 THEN 1 ELSE 0 END) AS
> Calls " _
> & "From master_call, rate_schedule, trunk_group " _
> & "WHERE master_call.out_rateplan = rate_schedule.rate_plan_kf
> AND " _
> & "master_call.out_rate_sch = rate_schedule.rate_sch_id_k
> AND " _
> & "rate_schedule.rate_plan_kf = trunk_group.trunkgrp_num_k
> AND " _
> & "trunkgrp_name='Direct to Philippines' " _
> & "GROUP BY trunkgrp_name, rate_sch_name, out_rate_sch " _
> & "ORDER BY trunkgrp_name, rate_sch_name;"
> .CommandType = adCmdText
> .ActiveConnection = cnnSQL
> End With
> '
> With rstRecords
> .CursorLocation = adUseClient
> .LockType = adLockReadOnly
> .Open cmmRecords
> End With
> '
> Exit Sub
> Error_Handler:
> Dim errLoop As Error
> Dim strError As String
> Dim strTMP As String
> Dim intC As Integer
> '
> On Error Resume Next
> '
> intC = 1
> strTMP = strTMP & vbCrLf & "VB Error # " & Str(Err.Number)
> strTMP = strTMP & vbCrLf & " Generated by " & Err.Source
> strTMP = strTMP & vbCrLf & " Description " & Err.Description
> ' Enumerate Errors collection and display properties of
> ' each Error object.
> Set errSQL = cnnSQL.Errors
> For Each errLoop In errSQL
> With errLoop
> strTMP = strTMP & vbCrLf & " ADO Error #" & .Number
> strTMP = strTMP & vbCrLf & " Description " & .Description
> strTMP = strTMP & vbCrLf & " Source " & .Source
> intC = intC + 1
> End With
> Next
>
> MsgBox strTMP
>
> ' Clean up Gracefully
>
> On Error Resume Next
> 'GoTo Done
--
Madison Pruet
===========================================
Enterprise Replication Product Developement
Dallas, Texas
Informix Software
===========================================