Trying to make recordset from pl/sql oracle server

ClimoC · May 23, 2012

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.

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

Joe4 · May 23, 2012

I would do adding the following line after your complete your construction of code:

Code:

MsgBox var1

and inspect it carefully to see if it looks like you wrote your code correctly.

You can also trying the code to an existing query to see if you can locate where the issue is. To do this, just create any dummy query and name it something like "QueryTest".

Then in your code, after you have created the SQL code, use this line to assign that SQL code to your dummy query:

Code:

CurrentDb.QueryDefs("QueryTest").SQL = var1

If you still cannot figure it out, try manually creating the query that does what you want. Then switch it to SQL View, and compare that SQL code to the code just above that you are building and look for differences.

Kyle123 · May 23, 2012

Is the db fussy about you correctly terminating with a ";"? You don't have one at the end of your SQL

ClimoC · May 23, 2012

Joe4 said:
I would do adding the following line after your complete your construction of code:

Code:

MsgBox var1

and inspect it carefully to see if it looks like you wrote your code correctly.

You can also trying the code to an existing query to see if you can locate where the issue is. To do this, just create any dummy query and name it something like "QueryTest".

Then in your code, after you have created the SQL code, use this line to assign that SQL code to your dummy query:

Code:

CurrentDb.QueryDefs("QueryTest").SQL = var1

If you still cannot figure it out, try manually creating the query that does what you want. Then switch it to SQL View, and compare that SQL code to the code just above that you are building and look for differences.

Thanks Joe.

Problem is, whilst I can 'say' it looks right, that's based ony my limited exposure to this stuff. Like I said, I can form a basic OLEDB/JET connection to an Access or text file and pull stuff with basic SQL, joining tables etc I'm a bit new to (used to only 2d arrays)

But the error messages I keep getting aren't specific, they're more about 'incorrect syntax', 'missing keyword', 'missing character', 'not ended properly' etc

ClimoC · May 23, 2012

Kyle123 said:
Is the db fussy about you correctly terminating with a ";"? You don't have one at the end of your SQL

Yeah that was my first thought Kyle. Doesn't seem to make a difference (even when I can get it to the point where the error it 'not ended properly', I append the semicolon, no difference. Have tried semicolon, semicolon then CrLf then forward slash, without forward slash, END, END; etc etc) - I've tried every google-able approach I can.

Kyle123 · May 23, 2012

Ok, think I've found some dodgy syntax, bear in mind im only MSSQL though not oracle

this

Code:

join channel c.channel_name  ON c.channel_code = si.channel_code

I think should be

Code:

join channel  C ON c.channel_code = si.channel_code

So you get:

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)
 FROM schedule_item si,
 channel c
 join channel C
 ON c.channel_code = si.channel_code
 join purchase p
 ON p.purchase_id = si.purchase_id
 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 c.channel_code = si.channel_code
 AND si.schedule_date BETWEEN '20110701' AND '20110705'
 AND p.tv_prem_live_none != 'R'
 ORDER BY c.channel_name DESC

Kyle123 · May 23, 2012

Actually I think you can exclude the "channel c" in your FROM statement as you're joining it anyway.

Revised:


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)
 FROM schedule_item si
 join channel C
 ON c.channel_code = si.channel_code
 join purchase p
 ON p.purchase_id = si.purchase_id
 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 c.channel_code = si.channel_code
 AND si.schedule_date BETWEEN '20110701' AND '20110705'
 AND p.tv_prem_live_none != 'R'
 ORDER BY c.channel_name DESC

ClimoC · May 23, 2012

Well at least now I get "SI"."CHANNEL_CODE": invalid identifier

Had this before... should it not be

Code:

join channel c on c.channel_code = si.channel_code

? Shooting in the dark here :P

ClimoC · May 23, 2012

ClimoC said:
Well at least now I get "SI"."CHANNEL_CODE": invalid identifier

Had this before... should it not be

Code:

join channel c on c.channel_code = si.channel_code

? Shooting in the dark here :P

Doing so, (changing to as above), I get 'column ambiguously defined' - which I think is why I tried it the way I originally had it, because it stopped that error coming up.

This is the problem with trying to just 'figure it out'. I can tweak and tweak to try and knock off each individual error as it comes up, but without knowing what my tweaks do to the rest, I'm possibly just going round in circles. In fact I think I might be

Kyle123 · May 23, 2012

I think that's what I changed it to?

channel_code is definitely a field name right? I think the syntax is right - Well at least SQL server says it is when I remove the function names, I'd double check the field names

Trying to make recordset from pl/sql oracle server

ClimoC

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Kyle123

Well-known Member

ClimoC

Well-known Member

ClimoC

Well-known Member

Kyle123

Well-known Member

Kyle123

Well-known Member

ClimoC

Well-known Member

ClimoC

Well-known Member

Kyle123

Well-known Member

Forum statistics

Share this page

Trying to make recordset from pl/sql oracle server

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock