Re: Column Names Returned from a View
This is a follow up to my original posting. Thanks to everyone that
has offered help.
I created a simple view:
CREATE VIEW test
( C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,
C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,
C31,C32,C33,C34,C35,C36,C37,C38,C39,C40,C41,C42,C43,C44,
C45,C46,C47,C48,C49,C50,C51,C52,C53,C54,C55,C56,C57,C58,
C59,C60,C61,C62,C63,C64,C65,C66,C67,C68,C69,C70,C71,C72,
C73,C74,C75,C76 ) AS
SELECT '1','2','3','4','5','6','7','8','9','10','11','12','13',
'14','15','16','17','18','19','20','21','22','23','24',
'25','26','27','28','29','30','31','32','33','34','35',
'36','37','38','39','40','41','42','43','44','45','46',
'47','48','49','50','51','52','53','54','55','56','57',
'58','59','60','61','62','63','64','65','66','67','68',
'69','70','71','72','73','74','75','76'
FROM SysTables
WHERE TabId =3D 99;
I can create this view without a problem and selecting from it works
as I would expect. However, if I alter "CREATE VIEW test" to=20
"CREATE VIEW t292", the view created has the same problem I reported
in my first posting. Namely, I'm seeing one of the column identifiers
being returned incorrectly.
Since the system only seems to have a problem with the view when it is
named t292, I'm led to believe that there is some reference to the =
original
table that wasn't deleted from the information tables when I originally=20
dropped (the table) t292. Note that I did not encounter any errors when
I dropped t292. I can use a different name for the view, but I'd still=20
like to know why this view won't function properly unless I name it =
some-
thing other than t292.
Any ideas?=20
Larry Kemmerling
AT&T Wireless Services
Aviation Communications Division
> Experts,
>=20
> I've come across a problem that I'm hoping someone can help me with.
> First, the particulars:
>=20
> HW: Sun UE4000
> OS: Solaris 2.5.1
> DB: ODS 7.23.UC4
>=20
> I've created a view with the following syntax:
>=20
> BEGIN WORK;
> CREATE VIEW "owner".name
> ( C1,C2,C3 . . . . CN ) AS
> SELECT '000001', SP_X('arg1','arg2','arg3','arg4'),=20
> SP_X('arg1','arg2','arg3','arg4'),
> SP_X('arg1','arg2','arg3','arg4'),
> .,
> .,
> .,
> SP_X('arg1','arg2','arg3','arg4')
> FROM SysTables
> WHERE TabId =3D 99;
> COMMIT WORK;
>=20
> SP_X is a stored procedure that returns an integer value.
>=20
> When I select from the view I get the single row returned
> with the proper column values that I would have expected.
> However, I noticed while doing a "select * from view" that=20
> the 33rd column identifier is always mangled. Further, if
> I select just this column the column name will also be re-
> turned with the first four characters being something other
> than what they should be. Repeatedly selecting this column=20
> shows that the first four characters are always different.=20
>=20
> If I eliminate one or more of the columns that precede the 33rd
> column from the view the 33rd column of this "new" view is still
> coming back wrong. If I "OUTPUT TO" the select statement, the=20
> mangled column name appears in the output file.
>=20
> I've got several similar views in production but I have never=20
> seen this behavior before. Has anyone run across a similar
> problem?
>=20
> TIA
>=20
> Larry Kemmerling
> AT&T Wireless Services
> Aviation Communications Division