The definition is CREATE VIEW `vwDVDKeywords` (DVDTitle, Keyword, DVDKeywordRecNo, DVDRecNo, KeywordRecNo, GenreSectionRecNo, ParentKeyWord, ParentKeywordRecno, ChildKeyWord, ChildKeyWordRecNo) AS SELECT t1.DVDTitle,t3.keyword,t2.dvdkeywordrecno, t1.DVDRecNo, t3.keywordrecno, t4.genresectionrecno, t5.ParentKeyWord, ParentKeywordRecno, t6.ChildKeyWord, ChildKeyWordRecno FROM DVD t1,keywords t3,dvdkeywords t2,genresections t4,vwparentkeywords t5,vwchildkeyword t6 WHERE t3.keywordrecno = t2.keywordrecno AND t1.dvdrecno = t2.DVDRecNo AND t4.genresectionrecno = t1.genresectionrecno AND t4.parentkeywordrecno = t5.parentkeywordrecno AND t4.parentkeywordrecno = t5.parentkeywordrecno AND t4.childkeywordrecno = t6.childkeywordrecno
the definition of the two nested views are CREATE VIEW `vwParentKeyWords` AS SELECT DISTINCT t1.KeyWord AS ParentKeyWord,t1.KeyWordRecNo AS ParentKeyWordRecNo FROM KEYWORDS t1,GenreSections t2 WHERE t1.KeyWordRecNo = t2.ParentKeyWordRecNo CREATE VIEW `vwChildKeyWord` AS SELECT t1.KeyWord AS ChildKeyWord, t1.KeyWordRecNo AS ChildKeyWordRecNo FROM KEYWORDS t1,GenreSections t2 WHERE t1.KeyWordRecNo = t2.ChildKeyWordRecNo ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Downall Posted At: Thursday, May 15, 2008 10:46 AM Posted To: RB7-L Conversation: [RBASE-L] - RE: Select syntax Subject: [RBASE-L] - RE: Select syntax Charles, What is the definition of the view vwDVDKeywords? Bill On Thu, May 15, 2008 at 11:42 AM, Charles Parks <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote: I still get the error "-ERROR- INTERNAL ERROR - in sort processing (2290)" with the revised syntax of select keyword from vwDVDKeywords where parentkeyword = 'fantasy' and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'adventure') and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'comedydrama') union select ChildKeyword from vwDVDKeywords where parentkeyword = 'fantasy' ________________________________ From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Stephen Markson Posted At: Thursday, May 15, 2008 9:58 AM Posted To: RB7-L Conversation: [RBASE-L] - RE: Select syntax Subject: [RBASE-L] - RE: Select syntax Try without DISTINCT Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForenSys.ca<http://www.ForenSys.ca> <http://www.forensys.ca/> 416 512 6950 ________________________________ From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Charles Parks Sent: May 15, 2008 10:47 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Select syntax When I try a select statement I get "-ERROR- INTERNAL ERROR - in sort processing (2290)". The syntax I'm using is select distinct keyword from vwDVDKeywords where parentkeyword = 'fantasy' and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'adventure') and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'comedydrama') union select ChildKeyword from vwDVDKeywords where parentkeyword = 'fantasy' The individual statements work select distinct keyword from vwDVDKeywords where parentkeyword = 'fantasy' and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'adventure') and keyword not in (select keyword from vwDVDKeywords where parentkeyword = 'comedydrama') and select ChildKeyword from vwDVDKeywords where parentkeyword = 'fantasy' Keyword and ChildKeyword have the same datatype. What would be the correct way to union the two commands? No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.16/1434 - Release Date: 15/05/2008 7:24 AM No virus found in this outgoing message. Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.16/1434 - Release Date: 15/05/2008 7:24 AM --- RBASE-L =======================3D======================3 D= TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected]<mailto:[email protected]> (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) =======================3D======================3 D= TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected]<mailto:[email protected]> In the message SUBJECT, put just one word: INTRO =======================3D======================3 D= TO UNSUBSCRIBE: Send a plain text email to [email protected]<mailto:[email protected]> In the message SUBJECT, put just one word: UNSUBSCRIBE =======================3D======================3 D= TO SEARCH ARCHIVES: Send a plain text email to [email protected]<mailto:[email protected]> In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. =======================3D======================3 D=
