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!!!)

 

[xenou]

Possibility (untested). I'm assuming the times are true times, in which case like dates I think they must be enclosed in hashes. There is no field "Date" in the select clause so I took it out of the order by clause. Date is a buggy name for a field, if you can't change it then at least always put it in brackets: Order By [Date] Desc

SELECT Col1_ItemID, "Morning" AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215' AND Col2_Time >= #06:00# AND Col2_Time <= #19:59#
GROUP BY Col1_ItemID

UNION ALL

SELECT Col1_ItemID, "Afternoon" AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215' AND Col2_Time >= #20:00# AND Col2_Time <= #20:59#
GROUP BY Col1_ItemID

UNION ALL

SELECT Col1_ItemID, "Evening" AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215' AND Col2_Time >= #21:00# AND Col2_Time <= #06:00#
GROUP BY Col1_ItemID

ORDER BY Col2_Time Desc

Here's an IIF alternative:

SELECT Col1_ItemID, 
IIF([Col2_Time]>=#06:00# AND [Col2_Time]<=#19:59#,"Morning",IIF([Col2_Time]>=#20:00# AND [Col2_Time]<=#20:59#,"Afternoon","Evening")) AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215'
GROUP BY Col1_ItemID

Note: If your times are stored as text representations, enclose in single quotes instead of hashes.
ξ

 

[ClimoC]

Possibility (untested). I'm assuming the times are true times, in which case like dates I think they must be enclosed in hashes. There is no field "Date" in the select clause so I took it out of the order by clause. Date is a buggy name for a field, if you can't change it then at least always put it in brackets: Order By [Date] Desc

SELECT Col1_ItemID, "Morning" AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215' AND Col2_Time >= #06:00# AND Col2_Time <= #19:59#
GROUP BY Col1_ItemID

UNION ALL

SELECT Col1_ItemID, "Afternoon" AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215' AND Col2_Time >= #20:00# AND Col2_Time <= #20:59#
GROUP BY Col1_ItemID

UNION ALL

SELECT Col1_ItemID, "Evening" AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215' AND Col2_Time >= #21:00# AND Col2_Time <= #06:00#
GROUP BY Col1_ItemID

ORDER BY Col2_Time Desc

Here's an IIF alternative:

SELECT Col1_ItemID, 
IIF([Col2_Time]>=#06:00# AND [Col2_Time]<=#19:59#,"Morning",IIF([Col2_Time]>=#20:00# AND [Col2_Time]<=#20:59#,"Afternoon","Evening")) AS TimeBand
FROM table_name 
WHERE [Date] >= '20130215'
GROUP BY Col1_ItemID

Note: If your times are stored as text representations, enclose in single quotes instead of hashes.
ξ

Thanks for your speedy reply xenou. I can't seem to get this to work though, it looks like the Time field I'm working on is stored as a text field - and doesn't hold the full datetime - just literally "06:00:00"

This says 'ORA-00942 table or view does not exist' ... (it does...)

var2 = "SELECT  TITLE, SCHEDULE_DATE, TITLE_ID, ""DAYTIME"" AS TimeBand FROM schedule_info_chris " & _
             "WHERE SCHEDULE_DATE_RAW >= '20130215' AND SCHEDULE_DATE_RAW <= '20130221' AND START_TIME = '06:00:00' " & _
             "GROUP BY TITLE_ID " & vbCrLf & _
             "UNION ALL " & vbCrLf & _
             "SELECT TITLE, SCHEDULE_DATE,  TITLE_ID, ""EIGHT"" AS TimeBand FROM schedule_info_chris " & _
             "WHERE SCHEDULE_DATE_RAW >= '20130215' AND SCHEDULE_DATE_RAW <= '20130221' AND START_TIME = '20:00:00'  " & _
             "GROUP BY TITLE_ID " & vbCrLf & _
             "UNION ALL " & vbCrLf & _
             "SELECT TITLE, SCHEDULE_DATE,  TITLE_ID, ""LATER"" AS TimeBand FROM schedule_info_chris " & _
             "WHERE SCHEDULE_DATE_RAW >= '20130215' AND SCHEDULE_DATE_RAW <= '20130221' AND START_TIME = '21:00:00' " & _
             "GROUP BY TITLE_ID"

 

[xenou]

It looks okay (although I thought we needed less than/greater than for the times - unless the start times are the same in each "time band").

I'm not very familiar with Oracle. If the DB can't find the table, usually what I will do is debug it with a simpler query - just a simple query where you select one or two fields from schedule_info_chris (in this case, maybe union'ing to another simple query to make sure the union syntax is also correct for Oracle). Once you get the basic query working and you know that the table being found isn't the problem, then back to the larger query. It also sometimes help to google the error number to see if anyone has reported a similar case (google MS Access ORA-00942 to get results with Access databases connected to Oracle).

 

[ClimoC]

It looks okay (although I thought we needed less than/greater than for the times - unless the start times are the same in each "time band").

I'm not very familiar with Oracle. If the DB can't find the table, usually what I will do is debug it with a simpler query - just a simple query where you select one or two fields from schedule_info_chris (in this case, maybe union'ing to another simple query to make sure the union syntax is also correct for Oracle). Once you get the basic query working and you know that the table being found isn't the problem, then back to the larger query. It also sometimes help to google the error number to see if anyone has reported a similar case (google MS Access ORA-00942 to get results with Access databases connected to Oracle).

Sorry yes - this was while I was trying out a few things as you said, and because the times weren't DateTimes, I tried changing the query slightly (simplifying it)

The table name is fine, I've gotten soemthing wrong syntaxually or keywordedly. As you suggested, I'll try some simpler code and see what happens. Report back soon

 

[ClimoC]

So turns out that ORA-00942 can also mean 'do not have access'...

As this is a read only view table, is that why the Unions don't work? Even a really simple Union doesn't seem to work

 

[ClimoC]

So turns out that ORA-00942 can also mean 'do not have access'...

As this is a read only view table, is that why the Unions don't work? Even a really simple Union doesn't seem to work

Christ almighty.... 'schedule_EXPORT_chris'.... not 'schedule_info_chris'...

Sorry to waste everyone's time!

 

[ClimoC]

...ORA-00904 "DAYTIME":invalid identifier

Sorry if I didn't clarify earlier, this is a read-only view table, and there is no column 'DAYTIME' as a flag.

 

[Kyle123]

You sure ""DAYTIME"" shouldn't be 'DAYTIME'?

 

[ClimoC]

You sure ""DAYTIME"" shouldn't be 'DAYTIME'?

It's VBA written SQL for Oracle

I seem to have found a way around this - since apparently what I was ultimately trying to do was impossible anyway. But I am hitting snags again.

The first thing I do now, is make a temporary table, that already has the resulting 'Time_Band' column appended and populated to it.

Then, as for my actual recordset, I should be able to do a pretty standard Join and Order By to get the bespokenly distinct records I want.

But it seems to be unhappy with my syntax or something. The following breaks on the populating of the recset ('var10')

var9 = "create global temporary table ChrisTempTable as select " & _
            "case when start_time between '06:00:00' and '19:59:59' THEN 'DAYTIME' WHEN start_time BETWEEN '20:00:00' and '20:59:59' THEN '8PM' Else 'POST 9PM' END As time_Band, " & _
            mysimplefields & _
            " From schedule_export_chris where rtrim(channel_name) in (" & mychannels & ") AND schedule_date_raw <= '20130220' AND schedule_date_raw >= '20130214'"


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"


var11 = "drop table christemptable"


    With cnImportConn
                    .Mode = adModeRead
                    .CursorLocation = adUseClient
                    .Open strConn
                    .CommandTimeout = 0
                    .Execute (var9)
                    Set RecSet = .Execute(var10, adOpenForwardOnly, adLockReadOnly)
                    .Execute (var11)
    End With

Further clarification:
'MysimpleFields' is a string of all the column headers
'MyCfields' is a string identical to 'MySImpleFields', but each field has the 'c.' in front of it to alias the right table

Var10's execution is bugging with error ' statement not ended properly
if I put ( ) around the 'ON' part of the Join (e.g.

"S on (C.title_id = S.title_id, S.channel_name = C.channel_name, c.time_band = s.time_band)"

I get missing right parenthesis - even though I obviously put a Right parenthesis on at the end