April 21, 2005 at 3:17 am
Hi,
I get an error message
Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
when selecting from views based on several base tables:
create view viewname (col1, col2, col3,...,coln)
as
select 'T', '111', col3, ..., coln from table1
UNION
select 'T', '222', col3, ..., coln from table2
UNION
select 'T', '333', col3, ..., coln from table3
UNION
...
select 'T', 'nnn', col3, ..., coln from anotherDB..tablem
where n * m is lalways ess than 700 if that has any importance.
Many of these queries run OK but some of them gives you the error mentioned above. In some ETL-workflows I could join two views like this and had to join the third one later in the ETL wf because of this error (but anyway two was joinable). The application from which I'm gathering data uses this kind of structure i.e. there is nothing I can do about the UNION thing should that be the problem...
Any idea what might cause this error and any quic&dirty workarounds?
Sql Server 7.0 on wint4 sp6. I'm not sure what is the version of ODBC drivers on the client computer..
Ville
April 21, 2005 at 4:45 am
Well, if all you are doing is SELECT then I would guess that the offending column in one of the tables in the VIEW is NOT NULLable.
When the UNION operation happens the NOT NULL constraint is taking precedence in TEMPDB
April 21, 2005 at 5:24 am
This was my original idea too but
I did check the nulls - none. Only NOT nullable fields were the ones in the PK's in the tables and none of them was null in any of the tables..?
select A.name, B.name, B.isnullable
from sysobjects A, syscolumns B
where A.id=B.id and B.isnullable = 0 and A.name like 'vins%'
order by B.name
Ville
April 21, 2005 at 6:58 am
What happens if you create and explicit temporary table of the require structure and do several insert selects instead of the union query?
April 21, 2005 at 1:46 pm
Hi,
I found out that (in this case) the sql run OK when the 'order by' clause was removed. Well, the (Informatica ETL) lookup sql requires the condition colums to be ordered ==> I created another data flow inside the the workflow which loads the dimension data (with several UNIONs) into the target DW table without any order by and then use this table as the lookup source in the actual data flow. As expected, works fine.
Still, the UNION-VIEW problem remains a mystery to me
Thanks for your interest
Ville
April 21, 2005 at 1:56 pm
Have you tried MAXDOP =1 ?
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply