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].
For more options, visit https://groups.google.com/d/optout.
