December 11, 2010 at 10:20 am
A list of the programs on all channels for a specific day showing the channel number, supplier, package, program name, rating code, and show time. This will be similar to a program guide, only not package specific. This is a date-driven report, therefore it should only display programs for a single date specified.
Here is what I have so far but puts the same program names in every channel ansd produces a somewhat infinite loop.
CREATE VIEW PROG_LINEUP AS
SELECT DISTINCT S.PROGTIME AS "SHOWTIME", S.PROGNAME AS "PROGRAM TITLE",
C.CHNUM AS "CHANNEL #", SU.SUPNAME AS "SUPPLIER", R.RATING AS "RATING"
FROM SCHEDULE S, CHANNELS C, SUPPLIERS SU, PROGRAM P, CHANNEL_PACKAGE CP, RATING R
WHERE CP.CHID=S.CHID AND
P.PROGNAME=S.PROGNAME AND
R.RATINGID=P.RATINGID
ORDER BY DISTINCT S.CHID;
Here is the tables and inserts as an attachment
December 11, 2010 at 11:00 am
Consuming the joins from the WHERE clause into ansi-style joins shows that your query will produce a cartesian product:
--CREATE VIEW PROG_LINEUP AS
SELECT DISTINCT
S.PROGTIME AS "SHOWTIME",
S.PROGNAME AS "PROGRAM TITLE",
C.CHNUM AS "CHANNEL #",
SU.SUPNAME AS "SUPPLIER",
R.RATING AS "RATING"
FROM SCHEDULE S, CHANNELS C, SUPPLIERS SU,
INNER JOIN PROGRAM P ON P.PROGNAME = S.PROGNAME
INNER JOIN CHANNEL_PACKAGE CP ON CP.CHID = S.CHID
INNER JOIN RATING R ON R.RATINGID = P.RATINGID
WHERE ...
ORDER BY DISTINCT S.CHID
- because you've no join specified for tables Channels and Suppliers.
It looks to me like you are fairly new to this. So, start small, add a table at a time when you are happy with what you've got.
Try starting with a select against the Schedule table and create a filter on PROGTIME to select for a particular day. When you are happy that it works, introduce the next table, which I reckon should be Program.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2010 at 11:17 am
Thanks for the advice. Yes I an new to sql, been doing it for about a month now learning by mistakes along the way.
December 11, 2010 at 11:34 am
Thanks for being candid.
Try something like this to start:
SELECT p.*, '#' AS '#', s.* -- # is a 'wall' between s-columns and p-columns - a dev trick
FROM PROGRAM p
INNER JOIN SCHEDULE s ON s.PROGNAME = p.PROGNAME
Look closely at the results and compare to your ERD. Is it what you are expecting to see? Then add the date filter in the WHERE clause. Make absolutely sure it does what you are expecting it to. It probably won't if the column you are filtering on is a DATETIME, just ask. When you are certain it's doing what you want it to, start adding the other tables one at a time checking the results as you add each one. When it goes t*ts up, which it will eventually 😉 , post your findings.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2010 at 11:41 am
Ok , but I have no Idea what you have just written. LOL. I have never seen a select statement yet with # symbols in it. SO I have no idea what that is suppose to mean. I to have had a problem displaying date and time together in my insert statements. for example:
INSERT INTO SCHEDULE (SchedID, ChID, ProgName, ProgTime)
VALUES ('1129', 1029, 'NOT HOME ALONE',TO_DATE('18-DEC-10 11:00','DD-MON-YY HH24:MI'));
When I use Select * from Schedule it only displays the date not date and time like it should. Maybe my syntax is incorrect.
December 11, 2010 at 12:36 pm
bickling77 (12/11/2010)
Ok , but I have no Idea what you have just written. LOL. I have never seen a select statement yet with # symbols in it. SO I have no idea what that is suppose to mean. I to have had a problem displaying date and time together in my insert statements. for example:
INSERT INTO SCHEDULE (SchedID, ChID, ProgName, ProgTime)VALUES ('1129', 1029, 'NOT HOME ALONE',TO_DATE('18-DEC-10 11:00','DD-MON-YY HH24:MI'));
When I use Select * from Schedule it only displays the date not date and time like it should. Maybe my syntax is incorrect.
Ah, the hash - it's just a new column, the content is '#' and the name is '#'. The hash (or gate, or whatever) makes a convenient and highly-visible partition, that's all. It could be 'Divider' = '|'.
What's the function "TO_DATE(...)"? Bear in mind this is a SQL2008 forum section!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2010 at 2:02 pm
That is a function we were taught for date and time in Oracle SQL. from my understanding date and time use the same Data type which is DATE in oracle. I still haven't figured out what I am doing wrong with the syntax I posted for my View that I was creating.
December 11, 2010 at 2:18 pm
The DATE datatype in SQL Server 2008 is exactly that - date without time. Try running SELECT CAST(GETDATE() AS DATE).
The script in your attachment will require a few changes to work with SQL Server. Where the column type should include the time component, use DATETIME.
The TO_DATE() function doesn't exist in SQL Server, but it wouldn't take much to write a dbo.TO_DATE() to mimic the features of the Oracle function. Calling it would require the schema qualification, i.e. dbo.TO_DATE()
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 11, 2010 at 2:27 pm
Oh I forgot to tell you that I am using Oracle Express 10g. Don't know if that makes a differnce or not but by the book I have been studying from The TO_DATE function is what was represented in the book. Don't know if the version I have will accept the functio that you have posted.
The error I keep getting when trying to run my view is s.progname invalid identifier. But this needs to be included in my joins. I do not know how to make this identifier valid as I have declare SCHEDULE s in my from clause. This makes no sense and I have re written this several times but still gices me the same error.
December 11, 2010 at 11:43 pm
When people say "SQL Server", they are not talking about the language "SQL". They instead are referring to the name of a Microsoft database product ... I'm just guessing that misunderstanding is why you are posting questions about Oracle's version of the SQL language on this website, which focuses on Microsoft's SQL Server and its own version of the SQL language. (You could try one of these Oracle-specific forums.)
Anyway, your error: "s.progname invalid identifier" is unfortunately misleading. "S.PROGNAME" is not the problem. My guess why Oracle thinks that is the problem is because it is confused by the mix between non-ANSI and ANSI joins (Specifically, the non-ANSI Cartesian/cross joins caused by listing more than one table on the "FROM" line with no joins between the tables in the WHERE clause and then ANSI-style "INNER JOIN"s.)
This SQL gives the "s.progname invalid identifier" error. It is based on ChrisM@home's SQL above with a few minor syntax fixes):
SELECT DISTINCT
S.PROGTIME AS "SHOWTIME",
S.PROGNAME AS "PROGRAM TITLE",
C.CHNUM AS "CHANNEL #",
SU.SUPNAME AS "SUPPLIER",
R.RATINGDESCRIPT AS "RATING" -- Fixed column name.
FROM SCHEDULE S, CHANNELS C, SUPPLIERS SU -- Removed stray ","
INNER JOIN PROGRAM P ON P.PROGNAME = S.PROGNAME
INNER JOIN CHANNEL_PACKAGE CP ON CP.CHID = S.CHID
INNER JOIN RATING R ON R.RATINGID = P.RATINGID
-- WHERE ...
ORDER BY C.CHNUM -- instead of "DISTINCT S.CHID"Result:Error at Command Line:8 Column:37
Error report:
SQL Error: ORA-00904: "S"."PROGNAME": invalid identifier
Instead, be consistent and use all ANSI-style joins like this:
SELECT DISTINCT
S.PROGTIME AS "SHOWTIME",
S.PROGNAME AS "PROGRAM TITLE",
C.CHNUM AS "CHANNEL #",
SU.SUPNAME AS "SUPPLIER",
R.RATINGDESCRIPT AS "RATING"
FROM SCHEDULE S
CROSS JOIN CHANNELS C
CROSS JOIN SUPPLIERS SU
INNER JOIN PROGRAM P ON P.PROGNAME = S.PROGNAME
INNER JOIN CHANNEL_PACKAGE CP ON CP.CHID = S.CHID
INNER JOIN RATING R ON R.RATINGID = P.RATINGID
-- WHERE ...
ORDER BY C.CHNUM
Result:
SHOWTIME PROGRAM TITLE CHANNEL # SUPPLIER RATING
------------------------- -------------------- ---------------------- -------- -----------------
18-DEC-10 COOKING WITH FOOD 1 ABC GENERAL AUDIENCE
18-DEC-10 COOKING WITH FOOD 1 CBS GENERAL AUDIENCE
18-DEC-10 COOKING WITH FOOD 1 CNN GENERAL AUDIENCE
18-DEC-10 COOKING WITH FOOD 1 CW GENERAL AUDIENCE
18-DEC-10 COOKING WITH FOOD 1 FEAR GENERAL AUDIENCE
... rows omitted for brevity ...
18-DEC-10 US OPEN 29 TBS PARENTAL GUIDENCE
18-DEC-10 US OPEN 29 WB PARENTAL GUIDENCE
3,016 rows selected ... Because so many rows are returned, the joins are probably not correct (I didn't take the time to learn your data model, so I can't suggest what could be wrong.)
You also had this question earlier:
When I use Select * from Schedule it only displays the date not date and time like it should. Maybe my syntax is incorrect.
You syntax is fine. Oracle's default format mask for DATE is "DD-MON-YY". To display hours and minutes (but omit seconds), just change the default mask with this Oracle-specific command:
alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI';
December 12, 2010 at 6:26 am
Thank george three for the advice, I have written it a different way then what you have in your post but I cannot get the view to stop displaying all those unnecessary rows. I am stumpped but will continue try to solve this today.
December 12, 2010 at 8:30 am
The rows are not "unnecessary", they are just the result of a "cartesian product" (or "cross join") - described here - because not every table in your view is in a corresponding join. You have 6 tables in your query, so that means you need at least 5 joins (because joins pair up tables) to prevent a cartesian product.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply