Trying to make recordset from pl/sql oracle server

[ClimoC]

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.

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

 

[ClimoC]

Fairly sure of the field names... removing the joining of 'channel c' creates 'table or view does not exist'

The original code, I took from a screenshot by one of the actual developers. Which looked like this

SELECT 
       c.channel_name,
       dstr(si.schedule_date)     tx_date,
       tstr(si.start_time) AS start_tm,
       tstr(si.duration)    AS slot_duration,
       t.programme_name     AS title,
       decode_tv_flag.table_description, 
       d.deal_name          ,
       d.deal_code          ,
       tstr(d.deal_slot_duration) , 
       'FIN'                     
FROM
       schedule_item si     -- Things in the planning grid
       JOIN channel c       ON c.channel_code = si.channel_code -- just for the name
       JOIN purchase p ON p.purchase_id = si.purchase_id -- joins many things!
       JOIN title t ON t.title_id = p.title_id
       JOIN deal d   ON d.deal_id = p.deal_id
       LEFT JOIN table_line decode_tv_flag    
              ON decode_tv_flag.table_id = 'PROGPR'
              AND decode_tv_flag.table_code = p.tv_prem_live_none
WHERE
       si.schedule_date BETWEEN '20110701' AND '20110705'     --'20120603'
AND    si.channel_code IN ('SKD1')       
AND    p.tv_prem_live_none != 'R'

 

[Kyle123]

so if you run the below as they gave you does it error? You've added some extra criteria which *I think* is redundant

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 & "       'FIN' " & vbCrLf
var1 = var1 & "FROM   schedule_item si  " & vbCrLf
var1 = var1 & "-- Things in the planning grid " & vbCrLf
var1 = var1 & "       join channel c " & vbCrLf
var1 = var1 & "         ON c.channel_code = si.channel_code  " & vbCrLf
var1 = var1 & "-- just for the name " & vbCrLf
var1 = var1 & "       join purchase p " & vbCrLf
var1 = var1 & "         ON p.purchase_id = si.purchase_id  " & vbCrLf
var1 = var1 & "-- joins many things! " & 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 & "         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  si.schedule_date BETWEEN '20110701' AND '20110705'  " & vbCrLf
var1 = var1 & "--'20120603' " & vbCrLf
var1 = var1 & "       AND si.channel_code IN ( 'SKD1' ) " & vbCrLf
var1 = var1 & "       AND p.tv_prem_live_none != 'R' "

 

[ClimoC]

Table or view does not exist

I tried simplifying it down to

var1 = ""
var1 = var1 & "SELECT schedule_item.channel_code "

var1 = var1 & "FROM   schedule_item "

var1 = var1 & "WHERE  schedule_date BETWEEN '20110701' AND '20110705'"

and still get table or view does not exist.

I might just go back to them and see if they've mistyped something (or at least sent me a copy of their script that was wrong?)

Thanks for your help Kyle

 

[Kyle123]

I'd say someone's removed the schedule_item view/table