It’s only my 1.52 cents worth (I’m in Canada), but here it is: Running with COLCHECK OFF is quite alright, but the column mismatch error may be a bit of a red herring. I think the problem might be in the processing of the GROUP BY (in the second SELECT of the UNION). If that’s the case and you want to run with COLCHECK ON you could (try to) create two separate views based on the two SELECTs in the UNION and then create your final view by doing a UNION between the two views.
Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Alastair Burr Sent: February-03-17 1:17 PM To: [email protected] Subject: Re: [RBASE-L] - View: Union / Group By - cannot see what's wrong I had what I hoped might be a brainwave to solve the data type mismatch on this view by forcing my non-column data to the type it needed to be. I amended the view definition to: CREATE VIEW `LiveAlbumsViewTEST` + (DiscIdNum, TrackNumber, Concert_Date, Venue, IWasThere, VenueName, + VenueMainName, VenueFirstName, ItemTitle, PlayingTime, MatrixNumber, ReleaseSequence, + DiscPurchase, OrigPurchase, DiscRelease, OrigRelease, ReleaseType_ID, Artist, + Producer, DiscLabel, ArtistName, ArtistMainName, ArtistFirstName, DiscType, + FullDiscType, TrackTitle, TrackTime, TrackArtistName) + AS SELECT + T1.DiscIdNum, T1.TrackNumber, T1.Concert_Date, T1.Venue, T1.IWasThere, T2.Full_Name, + T2.Main_Name, T2.First_Name, T3.AllTitles, T3.PlayingTime, T3.MatrixNumber, T3.ReleaseSequence, + T3.DiscPurchase, T3.OrigPurchase, T3.DiscRelease, T3.OrigRelease, T3.ReleaseType_ID, T3.Artist, + T3.Producer, T3.DiscLabel, T3.FullName, T3.MainName, T3.FirstName, T3.DiscType, + T3.FullDiscType, T4.All_Titles, T4.Playing_Time, T5.Full_Name + FROM LiveAlbums T1, All_Names T2, Track_Zero_View T3, Tracks T4, All_Names T5 + WHERE T1.Venue = T2.IdNum AND T1.DiscIdNum = T3.DiscIdNum AND T1.DiscIdNum = T4.DiscIdNum + AND T1.TrackNumber = T4.TrackNumber AND T4.Artist = T5.IdNum + UNION SELECT + (INT('0')), (INT('0')), T21.Concert_Date, T21.Venue, (ULC('Y')), T22.Full_Name, + T22.Main_Name, T22.First_Name, (ULC('N/A')), (RTIME(0,0,0)), (ULC('N/A')), (INT('0')), + (RDATE(01,01,1900)), (RDATE(01,01,1900)), (INT('190000')), (INT('190000')), (INT('0')), T21.Artist, + (INT('10000')), (INT('10000')), T23.Full_Name, T23.Main_Name, T23.First_Name, (ULC('N/A')), + (ULC('N/A')), (ULC('N/A')), (RTIME(0,0,0)), T23.Full_Name + FROM Concerts T21, All_Names T22, All_Names T23 + WHERE T21.Venue = T22.IdNum AND T21.Artist = T23.IdNum + GROUP BY T21.Concert_Date, T21.Venue, T22.Full_Name, T22.Main_Name, T22.First_Name, + T21.Artist, T23.Full_Name, T23.Main_Name, T23.First_Name When I create the view with COLCHECK set OFF it is created so there are no syntax errors. I checked the displayed data type in the browser and it is a perfect match for the types I expect. In any case, the columns repeated in the union have to match and the alias name is the only variation. The column names tend to indicate their type as well. But: If I take out the column names (all the text in brackets) the view is still created but on checking in the browser the first column shows as a DATE DiscIdNum is without any doubt an INTEGER - it is the primary linking column of the database. Nevertheless, having got my unload file from my problem with the owner password it was fairly easy for me to check through the views for DiscIdNum. In almost every case when an alias is used it is the alias for the first column and the column selection is T1.DiscIdNum. In every other case there is a correct match. In other words everything looks right. Not surprisingly, then, if I create with COLCHECK set ON I get the data mismatch in union error. I have even tried (INT(T1.DiscIdNum)) at the start of the select but it didn't help. Before I just accept having to run with COLCHECK set off has anybody and other ideas? Thanks & regards, Alastair. On 28/01/2017 17:35, Javier Valencia wrote: Alastair, I believe Stephen is on the right track. One way to check is to start adding (or subtracting) columns one at the time, when you add (or subtract) the column that is creating the problem the error will show (or disappear if subtracting columns) and that should give you the name of the column creating the issue and you can investigate further if it has a different definition on an existing table or perhaps on another active view. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Stephen Markson Sent: Friday, January 27, 2017 1:13 PM To: [email protected]<mailto:[email protected]> Subject: RE: [RBASE-L] - View: Union / Group By - cannot see what's wrong Alistair, if the COLCHECK setting is solving the problem, that may indicate that one of view column aliases is the same as a column name in some other table with a different datatype. Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Alastair Burr Sent: January-27-17 12:52 PM To: [email protected]<mailto:[email protected]> Subject: Re: [RBASE-L] - View: Union / Group By - cannot see what's wrong Thank you very much, Razzak, setting ColCheck off did the trick. I notice that if I turn ColCheck back on I immediately get the error message again as soon as I try to use the view. I have looked at the names in the view almost endlessly today to try and see where the error was so I am puzzled as to where R:Base finds a difference. I think I would rather correct that than run with ColCheck set off all the time. Obviously I can change the setting in a command file before and after use but what has raised this problem is the error message is received whenever I reconstruct the database from unloads. (But not with Reload.) This may be a complete misinterpretation but I also have a suspicion that this particular error has been causing problems with my user privileges. Is that possible? If so, I may have to run with ColCheck set to off all the time. With that in mind when was ColCheck introduced and/or was it changed with the upgrade to v10? Again thanks, Regards, Alastair. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
