ClimoC
Well-known Member
- Joined
- Aug 21, 2009
- Messages
- 584
Hello
Trying to get this to work, but get all manner of errors. As I'm doing this in VB, it won't bug on the line in question, but rather on the .Execute command.
Can anyone see why I am getting either 'ORA-00905: missing keyword' or 'SQL command not ended properly'?
I'm fairly good with VB, Can scrape basic SQL, but pl/sql on a massive multi-join is a step over my head, and I can't see what's wrong with my syntax. Table names etc are all correct, but either my syntax is incorrect or my delimiters are wrong/non-existent.
Trying to get this to work, but get all manner of errors. As I'm doing this in VB, it won't bug on the line in question, but rather on the .Execute command.
Can anyone see why I am getting either 'ORA-00905: missing keyword' or 'SQL command not ended properly'?
I'm fairly good with VB, Can scrape basic SQL, but pl/sql on a massive multi-join is a step over my head, and I can't see what's wrong with my syntax. Table names etc are all correct, but either my syntax is incorrect or my delimiters are wrong/non-existent.
Code:
Dim var1 As String, var2 As String
var1 = ""
var1 = var1 & "SELECT c.channel_name, " & vbCrLf
var1 = var1 & " Dstr(si.schedule_date) tx_date, " & vbCrLf
var1 = var1 & " Tstr(si.start_time) AS start_tm, " & vbCrLf
var1 = var1 & " Tstr(si.duration) AS slot_duration, " & vbCrLf
var1 = var1 & " t.programme_name AS title, " & vbCrLf
var1 = var1 & " decode_tv_flag.table_description, " & vbCrLf
var1 = var1 & " d.deal_name, " & vbCrLf
var1 = var1 & " d.deal_code, " & vbCrLf
var1 = var1 & " Tstr(d.deal_slot_duration) " & vbCrLf
var1 = var1 & "FROM schedule_item si, channel c " & vbCrLf
var1 = var1 & " join channel c.channel_name " & vbCrLf
var1 = var1 & " ON c.channel_code = si.channel_code " & vbCrLf
var1 = var1 & " join purchase p " & vbCrLf
var1 = var1 & " ON p.purchase_id = si.purchase_id " & vbCrLf
var1 = var1 & " join title t " & vbCrLf
var1 = var1 & " ON t.title_id = p.title_id " & vbCrLf
var1 = var1 & " join deal d " & vbCrLf
var1 = var1 & " ON d.deal_id = p.deal_id " & vbCrLf
'var1 = var1 & " left join table_line decode_tv_flag " & vbCrLf
'var1 = var1 & " " & vbCrLf
'var1 = var1 & " ON decode_tv_flag.table_id = 'PROGPR' " & vbCrLf
'var1 = var1 & " AND decode_tv_flag.table_code = p.tv_prem_live_none " & vbCrLf
var1 = var1 & "WHERE c.channel_code = si.channel_code AND si.schedule_date BETWEEN '20110701' AND '20110705' " & vbCrLf
var1 = var1 & " AND p.tv_prem_live_none != 'R' " & vbCrLf
var1 = var1 & "ORDER BY c.channel_name DESC"
'On Error Resume Next
With cnImportConn
.CursorLocation = adUseClient
.Open strConn
.CommandTimeout = 0
Set RecSet = .Execute(var1, adOpenForwardOnly, adLockReadOnly)
End With