sql server mseq: Problem with querying a view -- sql server mseq

Re: Problem with querying a view

M.Frielingsdorf
9/25/2003 12:17:35 AM

Thank you for your suggestion but this doesn't solve the
problem.
I still get the same error message and
I really don't know why.
Any other solution?

Monika

[quoted text, click to view]

Re: Problem with querying a view

M.Frielingsdorf
9/25/2003 1:21:22 AM

Here is the table structure.
cu_MitarbeiterOptionen itself is a view, so I also
included the structure for cu_Optionen and
cu_Mitarbeiter_Optionen.

CREATE TABLE [dbo].[Mitarbeiter] (
[Counter] [t_counter] IDENTITY (1, 1) NOT NULL ,
[Vorname] [t_name] NOT NULL ,
[Nachname] [t_name] NOT NULL ,
[Geburtsdatum] [datetime] NULL ,
[Strasse] [t_strasse] NULL ,
[Ort] [t_ort] NULL ,
[PLZ] [t_plz] NULL ,
[Gebiet] [t_gebiet] NULL ,
[Kuerzel] [varchar] (100) COLLATE
Latin1_General_CI_AS NULL ,
[Telefon_Intern] [t_telefon_intern] NULL ,
[Telefon_Extern] [t_telefon_extern] NULL ,
[Fax] [t_fax] NULL ,
[Email] [t_email] NULL ,
[Titel] [t_titel] NULL ,
[Bemerkungen] [t_bemerkung] NULL ,
[Gesperrt] [t_boolean] NOT NULL ,
[Ersteller] [t_ref_counter_nn] NOT NULL ,
[Erstellungs_Datum] [t_erstellungsdatum] NOT NULL ,
[Bearbeiter] [t_ref_counter_nn] NOT NULL ,
[Bearbeitungs_Datum] [t_bearbeitungsdatum] NOT
NULL ,
[Login] [varchar] (50) COLLATE
Latin1_General_CI_AS NOT NULL ,
[Password] [varchar] (20) COLLATE
Latin1_General_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE VIEW dbo.cu_MitarbeiterOptionen
AS
SELECT O.Bezeichnung, M.Mitarbeiter, M.Wert
FROM dbo.cu_Optionen O INNER JOIN
dbo.cu_Mitarbeiter_Optionen M ON
O.Counter = M.OPTION_COUNTER

CREATE TABLE [cu_Optionen] (
[Counter] [t_counter] IDENTITY (1, 1) NOT NULL ,
[Bezeichnung] [t_option_bezeichnung] NOT NULL ,
[Default_Wert] [t_option] NOT NULL ,
[Datentyp] [t_datentyp] NOT NULL CONSTRAINT
[DF_cu_Optionen_Datentyp] DEFAULT (2),
[Kategorie] [varchar] (20) COLLATE
Latin1_General_CI_AS NOT NULL ,
[Beschreibung] [varchar] (200) COLLATE
Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Optionen] PRIMARY KEY CLUSTERED
(
[Counter]
) ON [PRIMARY] ,
CONSTRAINT [UQ_Optionen_1] UNIQUE NONCLUSTERED
(
[Bezeichnung]
) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [cu_Mitarbeiter_Optionen] (
[Counter] [t_counter] IDENTITY (1, 1) NOT NULL ,
[Mitarbeiter] [t_ref_counter_nn] NOT NULL ,
[Option_Counter] [t_ref_counter_nn] NOT NULL ,
[Wert] [t_option] NULL ,
CONSTRAINT [PK_cu_Mitarbeiter_Optionen] PRIMARY
KEY CLUSTERED
(
[Counter]
) ON [PRIMARY] ,
CONSTRAINT [UQ_cu_Mitarbeiter_Optionen_1] UNIQUE
NONCLUSTERED
(
[Mitarbeiter],
[Option_Counter]
) ON [PRIMARY] ,
CONSTRAINT
[FK_cu_Mitarbeiter_Optionen_cu_Optionen] FOREIGN KEY
(
[Option_Counter]
) REFERENCES [cu_Optionen] (
[Counter]
) ON DELETE CASCADE ,
CONSTRAINT
[FK_cu_Mitarbeiter_Optionen_Mitarbeiter] FOREIGN KEY
(
[Mitarbeiter]
) REFERENCES [Mitarbeiter] (
[Counter]
) ON DELETE CASCADE
) ON [PRIMARY]
[quoted text, click to view]

Re: Problem with querying a view

M.Frielingsdorf
9/25/2003 2:35:06 AM

The column WERT is a varchar value and does not only
contain datetime-values. Is this the cause of the problem?
The View Mitarbeiter_Zeitraum only selects those which
have a proper date-format. So I would have thought that
querying this view should be possible.
But it seems that also the other entries in the table
cu_Mitarbeiter_Optionen are evaluated which are not part
of the view View_Mitarbeiter_Zeitraum and this causes the
problem. Is that right?

Is there any way around that problem?

Re: Problem with querying a view

Vishal Parkar
9/25/2003 8:51:41 AM

you will have to use convert function to direct SQL server to know which format has been passed to
datetime values.
your query can be changed as:

select *
from view_mitarbeiter_Zeitraum v where
v.ansichtvon > convert (datetime, '1.1.2002' , 104) -- i assume format to be 104

or it is always better to use format yyyymmdd(which is default accepted by sql server.)

select *
from view_mitarbeiter_Zeitraum v where
v.ansichtvon > '20020101'

--
- Vishal

Re: Problem with querying a view

Vishal Parkar
9/25/2003 1:17:10 PM

can u post table structures of these tables.

dbo.Mitarbeiter
dbo.cu_MitarbeiterOptionen

--
- Vishal
[quoted text, click to view]

Re: Problem with querying a view

Vishal Parkar
9/25/2003 2:36:46 PM

you are using user defined datatype.

what is the datatype for column [Wert] in following table if it is not datetime and if its varchar
then are these values have proper datetime format? it should have consistent format throughout the
column. meaning make use of dd.mm.yyyy or mm.dd.yyyy or any other format. but it should be
consistent for the column in the table.

CREATE TABLE [cu_Mitarbeiter_Optionen] (
[Counter] [t_counter] IDENTITY (1, 1) NOT NULL ,
[Mitarbeiter] [t_ref_counter_nn] NOT NULL ,
[Option_Counter] [t_ref_counter_nn] NOT NULL ,
[Wert] [t_option] NULL ,

--
- Vishal

Re: Problem with querying a view

Vishal Parkar
9/25/2003 11:34:00 PM

[quoted text, click to view]

I guess so... This is the problem. When you do convert
(Datetime, o1.Wert) SQL will try to convert all of the
column's
values to datetime and if it does not find any value that
has correct datetime format it will raise an error.

See following example and some workarounds. (but the most
accurate solution would be to correct the data itself
rather than having such a workarounds.)
Ex:
(Sample table.)
drop table t
create table t(dt varchar(400))

insert into t values ('im not date')
insert into t values ('01.01.2003')
insert into t values ('31.01.2003')
insert into t values ('31.12.2003')

in above table 1row does not contain valid datetime value
while other rows has consistent datetime format of
dd.mm.yyyy
if you have consistent format of date across all the rows
then you can have view as show in following example.

create view v1 as
select convert(datetime, dt, 104) dt from t

--View will get create successfully.

select * from v1 -- will raise an error (because of 1st
invalid row)

--1st solution: delete invalid rows

delete from t where dt like 'im not%'

select * from v1 -- will be successful.

--2nd solution: create another view which will have valid
datetime values and change definition of v1 that will
refer
to this view.

insert into t values ('im not date')

create view v2
as
select * from t where dt like '__[.]__[.]____%'

alter view v1
as
select convert(datetime, dt, 104) dt from v2

select * from v1 --should be successful.

If you have values like
'vv.aa.ttta' in the table t then query to v1 will fail as
well.

HTH

- Vishal

[quoted text, click to view]

Re: Problem with querying a view

M.Frielingsdorf
9/26/2003 12:17:53 AM

I can't change the values of the WERT-column. This table
contains options for the users and therefore can contain
any type of information.
But the view MITARBEITER_ZEITRAUM which I created only
contains those with a valid date-format.
So querying this view shouldn't look at the other rows.
The workaround you send is exactly what I did.
SELECT * FROM View_mitarbeiter_Zeitraum
works fine and shows only proper datetime-values.
but
Select * from View_mitarbeiter_Zeitraum where ansichtbis >
convert(datetime,'20020101')
produces the error.

So there is no workaround for that problem?

Kind regards

Re: Problem with querying a view

M.Frielingsdorf
9/26/2003 4:07:14 AM

The format is dd.mm.yyyy
and is consistent throughout the view.
And using the where-clause
ansichtbis >= convert(datetime, '01.01.2002', 104)
still produces the result
Looking at the definition of the view through the Query
Analyzer also tells me that the columns ansichtvon and
ansichtbis are of datatype 'datetime'.
So there is really no reason, why this query produces the
error.

[quoted text, click to view]

Re: Problem with querying a view

Vishal Parkar
9/26/2003 1:58:44 PM

what is the date format that is return by the View_mitarbeiter_Zeitraum where ansichtbis? is it
dd.mm.yyyy or yyyy-mm-dd etc. you should use that dateformat string in your view.

Also make sure it is consistent through the rows returned by view.

--
- Vishal
[quoted text, click to view]

Re: Problem with querying a view

Vishal Parkar
9/28/2003 11:20:06 AM

See following example and its explaination.

sample data:
drop table t
create table t(dd varchar(50))

insert into t values('23.01.2002')
insert into t values('31.01.2002')
insert into t values('31.10.2002')
insert into t values('23.01.9998')

View creation:

drop view v1
create view v1
as
select convert(datetime,dd) dd from t

sp_help v1 --will show you datatype for column dd as datetime.

but when you fire query againts the view which involve dd column, you will
get an error.

select dd from v1

This is because underlying query in view gets executed when you run the
query against view.
Hence if query contains invalid date format it will raise an error at run
time. Whereas
there won't be any problem while creating view.

Basically why this is happening is becuase you are not directing sql server
"inside view
definition" which date format is being used by your query.

See following modified view definition. you will have to do the same for the
datetime field you've used in the view definition.

drop view v1
create view v1
as
select convert(datetime,dd, 104) dd from t

select dd from v1 --will not produce error.

now you can safely run query against the view.
Ex:
select * from v1
where dd > convert(datetime, '01.01.2002', 104)

HTH
--
-Vishal

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.