SQL SELECT [UNIQUE/DISTINCT] with nonclustered index in a read-only table

[ClimoC]

Hello

Not sure exactly how to go about this, but have googled it to death in multiple ways, even googled the bits separately and can't figure out how to join them together.

For my VBA, it needs a recordset, currently loaded manually (export file from Oracle, loop through cells adding to recordset). But since I now have the SQL back-end access, I can pull the table directly.

This is going to help me a lot, but for one particular script I'd like to skip a step where currently, I use a VB Scripting Collection to identify unique records based on multiple fields. (e.g. 'If not MyColl.exists(VariTable(x,1) & VariTable(x,3)) then... [Recset.add...]'

Now the problem is, the 'groupings' I have are custom 'Time-bands'. Though all records are unique as to their time and date, I need unique examples of where each item by its ID falls into specific Time Categories. Specifically, 6am-7:59pm, 8pm-8:59pm, and 9pm-6am

If each of these groups is called 'Morning', 'Evening' and 'Nighttime', then if it finds 100 records with the same ID spread throughout the various times of the day, I need it to return just 3 (1 for each category)

Just to make it interesting, the Table I have access to is a read-only view table. So I can't Amend the table or add columns. I'm hoping I can do this all in my SELECT statement?

something like:

SELECT DISTINCT Col1_ItemID, (Col2_Time >= 06:00 AND Col2_Time <=19:59) OR (Col2_Time >= 20:00 AND Col2_Time <= 20:59) OR (Col2_Time >= 21:00) OR (Col2_Time <= 06:00) FROM table_name WHERE Date >= '20130215' ORDER BY Date DESC, Col2_Time Desc

Is this possible? Sort only half know what I'm doing in SQL (Dangerous!!!)

 

[Kyle123]

I know, but ""Daytime"" <> 'DayTime', the latter wouldn't work in SQL server since " isn't used for quotes, ' is

 

[xenou]

Maybe the GROUP BY clause should come after the ON clause, not before it:

var10 = "select " & mycfields & " FROM christemptable c " & _
" JOIN (select title_id, channel_name, time_band" & _
" FROM christemptable GROUP BY title_id, channel_name, time_band) S on C.title_id = S.title_id, S.channel_name = C.channel_name, c.time_band = s.time_band"

 

[Russell Hauf]

What does mycfields look like? Sometimes I find it useful to put in debug statements while developing. For example, after the var10 = line, put something like this:

Debug.Print var10

Then take a look at that and see if it truly does look right (and depending on your access, etc. - sometimes I'll take my SQL and paste it into the native app to see if it works there. Sometimes the native app - in your case Oracle - has better error messages and/or may point to exactly where the query is failing).

Just a thought,
Russell

 

[ClimoC]

What does mycfields look like? Sometimes I find it useful to put in debug statements while developing. For example, after the var10 = line, put something like this:

Debug.Print var10

Then take a look at that and see if it truly does look right (and depending on your access, etc. - sometimes I'll take my SQL and paste it into the native app to see if it works there. Sometimes the native app - in your case Oracle - has better error messages and/or may point to exactly where the query is failing).

Just a thought,
Russell

Russell,

thanks for the suggestion.

The string 'Mysimplefields' used in sql string 'var9' is all the fields, comma separated. 'MyCfields' is exactly the same, only with "c." in front of each field for when I alias the table as C

Now what's interesting I discovered last night, is that once Var9 has been executed, and the temp table created, if I then just run a simple "SELECT * FROM CHRISTEMPTABLE", I get all 50 columns, but 0 records.

I was under the impression that "CREATE GLOBAL TEMPORARY TABLE CHRISTEMPTABLE AS SELECT..." generated the records for the table too? It certainly seems to take 5-10seconds to create the temp table...

Doesn't yet quite fix my problem (which is an error in my statements - 'not ended properly'), but I need to fix that up at some point!

And I don't as such have oracle access. The developers gave me my own table view to fire read-only queries at so that I don't have to put in a service request every time I want to get a slightly different table back. So that's why it's all in VB6 (and also because the data manipulation and the shared access database are viewed in Excel, easier to do it from the host application)