Hello everyone! I have a VB 6.0 / Oracle 9i questions. Basically, I have this query that uses "with clause" as a temp in-line view. The reasons I have to do that is because I can't use PL/SQL (our group is not allowed to create objects in the database) so this temp view is used many times in the query thus busing the performance. Here is where the problem is: I wrapped the query in VB 6.0 so it can be schedule to run daily, however, I get "Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal" - What this looks to me like is that VB 6.0 doesn't recognize Oracle Sql - with clause. Can anyone suggest a work around? I really need to have the query use "with clause" as without it it runs longer than 12 hrs and the report that it generates is a daily one. Below is the code for both sql and vb...
Thanks in advance for your help!!! Valeriya
Code:
with driver as
(select
distinct
rx.xuser
,src.trc_value
,ra.TAB_NAME
,ra.field_name
,ra.old_char_value
,ra.SVCBR_ID
,ra.PRESCRIPTION_ID
,ra.REFILL_NO
,ra.xdate
from
rx_audit ra
,prescriptions_table rx
,(select p1.therapy_type, p2.trc_value
from pt_trc_phones_dtl p1, pt_trc_phones_hdr p2
where p1.trc_id = p2.trc_id) SRC
where ra.PRESCRIPTION_ID = rx.PRESCRIPTION_ID
and ra.SVCBR_ID = rx.SVCBR_ID
and ra.REFILL_NO = rx.refill_no
and ra.XDATE between (trunc(sysdate)-10 + 0.01/24)
and (trunc(sysdate)-10 + 23.99/24)
and ra.TAB_NAME = 'Main'
and rx.therapy_type = src.therapy_type (+)
) ---- Acts as an inline view
select
all_data.xuser user_name
,all_data.trc src
,sum(all_data.profiled_rx) profiled
,sum(all_data.profile_locked) profile_locked
,sum(all_data.set_hid) set_hid
,sum(all_data.right_lock) right_locked
,sum(all_data.RPH_LOCK) rph_locked
,sum(all_data.voided) voided
from
(
select distinct
ra.XUSER,
ra.trc_value
,count(ra.prescription_id) profiled_rx
,0 profile_locked
,0 set_hid
,0 right_lock
,0 RPH_LOCK
,0 voided
from
driver ra
where ra.field_name = 'Rx Status'
and ra.refill_no = 0
and ra.old_char_value = 'PROFILE'
group by ra.XUSER, ra.trc_value
UNION ALL
select distinct
ra.XUSER
,ra.trc_value
,0
,count(ra.prescription_id) profile_locked
,0
,0
,0
,0
from
driver ra
where tab_name = 'Main'
and field_name = 'Lock'
and ra.refill_no = 0
and ra.XDATE = (select min(ra1.xdate)
from driver ra1
where ra1.prescription_id = ra.prescription_id
and ra1.SVCBR_ID = ra.SVCBR_ID
and ra1.field_name = 'Lock'
and ra1.refill_no = 0)
group by ra.XUSER, ra.trc_value
UNION ALL
select distinct
ra.XUSER
,ra.trc_value
,0
,0
,count(ra.prescription_id) set_HID
,0
,0
,0
from
driver ra
where ra.tab_name = 'Main'
and ra.field_name = 'HID'
and ra.xdate = (select min(ra1.xdate)
from
driver ra1
where ra1.prescription_id = ra.prescription_id
and ra1.field_name = 'HID'
and ra1.svcbr_id = ra.SVCBR_ID)
group by ra.XUSER, ra.trc_value
UNION ALL
select distinct
driver.xuser
,driver.trc_value
,0
,0
,0
,RL.right_lock
,0
,0
from
driver
,(select distinct
rx.RORT_XUSER
,ph.trc_value
,count(distinct rx.prescription_id) right_lock
from
prescriptions_table rx
,pt_trc_phones_dtl pd
,pt_trc_phones_hdr ph
where pd.trc_id = ph.trc_id
and rx.refill_no > 0
and rx.therapy_type = pd.therapy_type(+)
and rx.RORT_DATE between (trunc(sysdate)-10 + 0.0004/24)
and (trunc(sysdate)-10 + 23.99/24)
group by rx.RORT_XUSER, ph.trc_value) RL
Where driver.xuser = RL.RORT_XUSER
and driver.trc_value = RL.trc_value
UNION ALL
select distinct
ra.XUSER
,ra.trc_value
,0
,0
,0
,0
,count(r.prescription_id) RPH_LOCK
,0
from
rxh_custom.mi_prescriptions_table r
,driver ra
where ra.PRESCRIPTION_ID = r.PRESCRIPTION_ID
and ra.SVCBR_ID = r.SVCBR_ID
and ra.REFILL_NO = r.REFILL_NO
and ra.field_name = 'Lock'
group by ra.XUSER, ra.trc_value
UNION ALL
select distinct
driver.xuser
,driver.trc_value
,0
,0
,0
,0
,0
,VOID.voided
from
driver
,(select
rx.xuser
,ph.trc_value
,count(rx.void_date) voided
from
prescriptions_table rx
,pt_trc_phones_dtl pd
,pt_trc_phones_hdr ph
where pd.trc_id = ph.trc_id
and rx.void_date between trunc(sysdate)-10 and (sysdate)-10
and rx.therapy_type = pd.therapy_type(+)
group by rx.xuser, ph.trc_value) VOID
Where driver.xuser = VOID.xuser
and driver.trc_value = VOID.trc_value
) all_data
group by all_data.xuser, all_data.trc_value
-------------- below is the vb code----------
Option Explicit
Private rstRecordSet As ADODB.Recordset
Private rst As DAO.Recordset
Private sSQL As String
Private sFolderLoc As String
Private xlapp As Excel.Application
Private Const Output_Dest = "S:\output"
Private Const Output_Dest_Test = "S:\output_test"
Private sxlsfile As String
Private sFileName As String
Private sInput As String
Const SQL_Home = "S:\ALLSHARE\Productivity_rprt\"
Private iCnt As Integer
Public Sub Main()
On Error GoTo main_error
DoEvents
Screen.MousePointer = vbHourglass
If UCase(frmLogin.txtServer.Text) = "production1" Then
sFolderLoc = Output_Dest
ElseIf UCase(frmLogin.txtServer.Text) = "production2" Then
sFolderLoc = Output_Dest
Else
sFolderLoc = Output_Dest_Test
End If
Screen.MousePointer = vbHourglass
Set rstRecordSet = New ADODB.Recordset
sFileName = sFolderLoc & "\Productivity_Report.csv"
Call Remove_Files
Call create_excelobj
Call Produce_Report
sxlsfile = sFolderLoc &"\Productivity_Report" & Format(Now,"mmddyyyy")&".xls"
If Len(Dir(sxlsfile)) > 0 Then
Kill sxlsfile
End If
xlapp.ActiveWorkbook.SaveAs sxlsfile, xlNormal
xlapp.ActiveWindow.Close savechanges:=False
' xlapp.Quit
Call Remove_Files
Screen.MousePointer = vbDefault
Call clean_shutdown
Screen.MousePointer = vbNormal
' MsgBox "Finished"
End
main_error:
Screen.MousePointer = vbDefault
MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical, "Prod_Report"
If Not rstRecordSet Is Nothing Then
If rstRecordSet.State <> 0 Then
rstRecordSet.Close
End If
Set rstRecordSet = Nothing
End If
If Not conn Is Nothing Then
conn.Close
Set conn = Nothing
End If
Call clean_shutdown
End
End Sub
Private Sub Build_Query(sFileName As String)
Open sFileName For Input As #1
Line Input #1, sInput
sSQL = sInput
Do While Not EOF(1)
Line Input #1, sInput
sSQL = sSQL & Chr(10) & sInput
Loop
Close #1
End Sub
Private Sub Produce_Report()
Call Build_Query(SQL_Home & "Productivity_report.sql")
Call Capture_Data(sFileName)
'Debug.Print sFileName
Call Create_Report
End Sub
Private Sub Capture_Data(sname As String)
On Error GoTo capture_data_error
rstRecordSet.Open sSQL, conn, adOpenForwardOnly
Open sFileName For Output As #1
For iCnt = 0 To rstRecordSet.Fields.Count - 2
If IsNull(rstRecordSet.Fields(iCnt).Name) Then
Write #1, "";
Else
Write #1, Trim(Replace(rstRecordSet.Fields(iCnt).Name, ",", ""));
End If
Next iCnt
If IsNull(rstRecordSet.Fields(iCnt).Name) Then
Write #1, ""
Else
Write #1, Trim(Replace(rstRecordSet.Fields(iCnt).Name, ",", ""))
End If
Do While Not rstRecordSet.EOF
For iCnt = 0 To rstRecordSet.Fields.Count - 2
If IsNull(rstRecordSet.Fields(iCnt).Value) Then
Write #1, "";
Else
Write #1, Trim(Replace(rstRecordSet.Fields(iCnt).Value, ",", ""));
End If
Next iCnt
If IsNull(rstRecordSet.Fields(iCnt).Value) Then
Write #1, ""
Else
Write #1, Trim(Replace(rstRecordSet.Fields(iCnt).Value, ",", ""))
End If
rstRecordSet.MoveNext
Loop
Close #1
rstRecordSet.Close
Exit Sub
capture_data_error:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical, "Prod_Report"
End Sub
Private Sub Create_Report()
On Error GoTo create_report_error
With xlapp
.Workbooks.Open FileName:=sFileName
.Cells.Select
.Selection.Columns.AutoFit
.Range("A1").Select
End With
Exit Sub
create_report_error:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbCritical, "Prod_Report"
Call clean_shutdown
End
End Sub
Private Sub clean_shutdown()
If Not rstRecordSet Is Nothing Then
If rstRecordSet.State <> 0 Then
rstRecordSet.Close
End If
Set rstRecordSet = Nothing
End If
If Not conn Is Nothing Then
conn.Close
Set conn = Nothing
End If
If Not xlapp Is Nothing Then
xlapp.Quit
Set xlapp = Nothing
End If
End Sub
Private Sub Remove_Files()
If Len(Dir(sFileName)) > 0 Then
Kill sFileName
End If
End Sub
Public Sub create_excelobj()
Set xlapp = CreateObject("Excel.Application")
End Sub
------code behind login form-------
Option Explicit
Private strstart As String
Private strEnd As String
Private Sub cmdCancel_Click()
End
End Sub
Private Sub cmdOk_Click()
Dim sSQL As String
Dim connStr As String
On Error GoTo cmdOk_Click_error
If Trim(txtUserName) = "" Then
MsgBox "User name must be entered!", vbCritical, "User Name Error"
txtUserName.Text = ""
txtUserName.SetFocus
Exit Sub
End If
If Trim(txtPassword) = "" Then
MsgBox "Password must be entered!", vbCritical, "Password Error"
txtPassword.Text = ""
txtPassword.SetFocus
Exit Sub
End If
connStr = "PROVIDER=MSDASQL;" & _
"DRIVER={microsoft odbc for oracle};" & _
"SERVER=" & txtServer.Text & ";" & _
"UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";"
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open connStr
Call Main
' frmLogin.Hide
'end
cmdOk_Click_error:
MsgBox "Connection not established- " & Err.Description, vbCritical,"Login Error"
txtUserName.Text = ""
txtPassword.Text = ""
txtServer.Text = "production1"
txtUserName.SetFocus
End Sub
Private Sub Form_Load()
'
' txtServer.Text = "production1"
' txtUserName.Text = "jf;alkj;f"
' txtPassword.Text = "111222222"
'
' connStr = "PROVIDER=MSDASQL;" & _
' "DRIVER={microsoft odbc for oracle};" & _
' "SERVER=" & txtServer.Text & ";" & _
' "UID=" & txtUserName.Text & ";PWD=" & txtPassword.Text & ";"
'
' Set conn = New ADODB.Connection
'
' conn.CursorLocation = adUseClient
'
' conn.Open connStr
'
' Call Main
End Sub
