NULL in dimensions: entry

Showing posts with label entry. Show all posts

Wednesday, March 21, 2012

Number of entry query.

MSSQL2K
SP4

Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.

--drop table #foo
create table #foo
(empID int,
transDate datetime,
transType varchar(10))

insert into #foo values(1, '01/01/06 01:01:01','Insert')
insert into #foo values(1, '01/01/06 01:01:02','Update')
insert into #foo values(1, '01/01/06 01:01:03','Delete')
insert into #foo values(2, '01/01/06 01:01:01','Insert')
insert into #foo values(2, '01/01/06 01:01:02','Update')

select f.empID, Change =
(select count(transDate) from #foo f2
where f2.empID = f.empID
group by empID),
f.transDate, f.transType
from #foo f

Current results:

1 3 2006-01-01 01:01:01.000 Insert
1 3 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 2 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

Desired results:

1 1 2006-01-01 01:01:01.000 Insert
1 2 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 1 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

TIA, CFRwhere f2.empID = f.empID AND f2.transdate <= f.transdate

Which presumes no 2 can have the same date/time value.|||So close, yet so far. Thanks.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.
Nah, I am pretty sure he means that until your assist, CFR was so closer and yet so far from their solution.

After all, they would have asked for more help other wise, or said what they were getting wrong.

I guess we should be glad that they at least wrote back to thank you. Some folks get the answer, and then disappear.|||As Code Carpenter mentioned, I was so close yet so far. The code did exactly what I needed. As far as disappearing, Im afraid you folks are stuck with me for a while.

Thanks again!

Monday, March 12, 2012

Nulls Versus Blanks PArt 2

How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
emty strings. On the same Token the server should insert <NULLS> by default
My server was configured like that and for some reason now it is behaving
differently. There has to be some global configuration, I am not looking for
individual constraints and default values. If there is no global
configuration am I looking at some kind of bug?
Thanks
IT PHYTOSAN>>How do I configure SQL 2000 to insert <NULLS> when deleting an entry
I assume by deleting you don't mean deleting the whole row like this
DELETE table where SomeColumn =SomeValue
Are you talking about EM (Enterprise Manager) ?
If so then enter CTRL + 0 in the column and a NULL will be inserted
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> emty strings.
There's no such setting as this never happens. Let me explain:
If you by "deleting an entry" mean deleting a row, using the DELETE SQL command, then the row is
gone and there is no place for NULL nor space.
If you mean changing the value of a column for a row, then you set this using the UPDATE statement:
UPDATE tblname
SET colname = NULL
WHERE ...
So you need to dig in the application to see how it modifies the data.
Possibly you has a default constraint for a column, with NULL and that default is not an empty
string. You can refer to a default for a column in an update like:
UPDATE tblname
SET colname = DEFAULT
WHERE ...
See if you do have any default for this column using sp_help.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:FD708941-A75E-4C00-8D3F-706FFD2E7744@.microsoft.com...
> How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> emty strings. On the same Token the server should insert <NULLS> by default
> My server was configured like that and for some reason now it is behaving
> differently. There has to be some global configuration, I am not looking for
> individual constraints and default values. If there is no global
> configuration am I looking at some kind of bug?
> Thanks
> IT PHYTOSAN|||Thanks for your help. Looking at the source of the issue, what I would really
like to accomplish is that varchar and char entries treat empty strings as
<NULL>'s. When deleting a single field (using the delete button on the
keyboard in a table opended enterprise manager for example) I would like to
see a <NULL> inserted rather than an empty string. There must be a simple way
of enforcing meaningfull entries in the tables.
any hints?
IT PHYTOSAN
"Tibor Karaszi" wrote:
> > How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> > emty strings.
> There's no such setting as this never happens. Let me explain:
> If you by "deleting an entry" mean deleting a row, using the DELETE SQL command, then the row is
> gone and there is no place for NULL nor space.
> If you mean changing the value of a column for a row, then you set this using the UPDATE statement:
> UPDATE tblname
> SET colname = NULL
> WHERE ...
> So you need to dig in the application to see how it modifies the data.
> Possibly you has a default constraint for a column, with NULL and that default is not an empty
> string. You can refer to a default for a column in an update like:
> UPDATE tblname
> SET colname = DEFAULT
> WHERE ...
> See if you do have any default for this column using sp_help.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> news:FD708941-A75E-4C00-8D3F-706FFD2E7744@.microsoft.com...
> > How do I configure SQL 2000 to insert <NULLS> when deleting an entry and not
> > emty strings. On the same Token the server should insert <NULLS> by default
> > My server was configured like that and for some reason now it is behaving
> > differently. There has to be some global configuration, I am not looking for
> > individual constraints and default values. If there is no global
> > configuration am I looking at some kind of bug?
> >
> > Thanks
> >
> > IT PHYTOSAN
>|||To create NULL values from Enterprise Manager use CTRL + 0 instead of
delete
Hightlight all the text and then press CTRL + 0
Or switch to Query analyzer and write UPDATE statements like Tibor
showed you that will also give you more control and you will know
what's going on
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I am aware of CTRL + 0 but it does not fix my problem. Right now my client
(InfoPath) will insert empty strings in tables that have no entry and does
not recognize that this field is required but empty. I would like SQL server
to treat empty strings like <NULLS> as mentioned.
Thanks
IT PHYTOSAN
"SQL" wrote:
> To create NULL values from Enterprise Manager use CTRL + 0 instead of
> delete
> Hightlight all the text and then press CTRL + 0
> Or switch to Query analyzer and write UPDATE statements like Tibor
> showed you that will also give you more control and you will know
> what's going on
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||create a trigger on the table|||that won't be recognized by InfoPath as required field. The trigger will
throw an error but for me that's after the fact.
Thanks
IT PHYTOSAN
"SQL" wrote:
> create a trigger on the table
>|||As suggested, this is an application (InfoPath) problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
>I am aware of CTRL + 0 but it does not fix my problem. Right now my client
> (InfoPath) will insert empty strings in tables that have no entry and does
> not recognize that this field is required but empty. I would like SQL server
> to treat empty strings like <NULLS> as mentioned.
> Thanks
> IT PHYTOSAN
> "SQL" wrote:
>> To create NULL values from Enterprise Manager use CTRL + 0 instead of
>> delete
>> Hightlight all the text and then press CTRL + 0
>> Or switch to Query analyzer and write UPDATE statements like Tibor
>> showed you that will also give you more control and you will know
>> what's going on
>>
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>|||Wow I am really stuck now, they can't help me over there either. Any clues?
Thanks anyway
IT PHYTOSAN
"Tibor Karaszi" wrote:
> As suggested, this is an application (InfoPath) problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
> >I am aware of CTRL + 0 but it does not fix my problem. Right now my client
> > (InfoPath) will insert empty strings in tables that have no entry and does
> > not recognize that this field is required but empty. I would like SQL server
> > to treat empty strings like <NULLS> as mentioned.
> >
> > Thanks
> >
> > IT PHYTOSAN
> >
> > "SQL" wrote:
> >
> >> To create NULL values from Enterprise Manager use CTRL + 0 instead of
> >> delete
> >> Hightlight all the text and then press CTRL + 0
> >> Or switch to Query analyzer and write UPDATE statements like Tibor
> >> showed you that will also give you more control and you will know
> >> what's going on
> >>
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >>
>|||You may use a INSTEAD OF type of trigger to do your data validation.
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:E1BDC16A-E880-4919-95CE-FCEA4DE3EC7C@.microsoft.com...
> that won't be recognized by InfoPath as required field. The trigger will
> throw an error but for me that's after the fact.
> Thanks
> IT PHYTOSAN
> "SQL" wrote:
>> create a trigger on the table
>>|||I suggest you open a case with MS Support. If you don't know what caused the change, or even if the
change was at the database level, it is hard to suggest anything. If the change was at the database
level, and you don't know who did it any what they did and don't know what the database definition
is supposed to look like, it is hard to tell anything. MS Support should be able to track this down
for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:3D54C44E-093D-4A78-997F-7E49AECB4D12@.microsoft.com...
> Wow I am really stuck now, they can't help me over there either. Any clues?
> Thanks anyway
> IT PHYTOSAN
> "Tibor Karaszi" wrote:
>> As suggested, this is an application (InfoPath) problem.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
>> news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
>> >I am aware of CTRL + 0 but it does not fix my problem. Right now my client
>> > (InfoPath) will insert empty strings in tables that have no entry and does
>> > not recognize that this field is required but empty. I would like SQL server
>> > to treat empty strings like <NULLS> as mentioned.
>> >
>> > Thanks
>> >
>> > IT PHYTOSAN
>> >
>> > "SQL" wrote:
>> >
>> >> To create NULL values from Enterprise Manager use CTRL + 0 instead of
>> >> delete
>> >> Hightlight all the text and then press CTRL + 0
>> >> Or switch to Query analyzer and write UPDATE statements like Tibor
>> >> showed you that will also give you more control and you will know
>> >> what's going on
>> >>
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >>
>>|||That is good adivce. I'll do that and post back if the solution can be found.
Thans again for your help to both of you.
IT PHYTOSAN
"Tibor Karaszi" wrote:
> I suggest you open a case with MS Support. If you don't know what caused the change, or even if the
> change was at the database level, it is hard to suggest anything. If the change was at the database
> level, and you don't know who did it any what they did and don't know what the database definition
> is supposed to look like, it is hard to tell anything. MS Support should be able to track this down
> for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> news:3D54C44E-093D-4A78-997F-7E49AECB4D12@.microsoft.com...
> > Wow I am really stuck now, they can't help me over there either. Any clues?
> >
> > Thanks anyway
> >
> > IT PHYTOSAN
> >
> > "Tibor Karaszi" wrote:
> >
> >> As suggested, this is an application (InfoPath) problem.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
> >> news:0AF6F82F-CC92-4F15-A988-779391530969@.microsoft.com...
> >> >I am aware of CTRL + 0 but it does not fix my problem. Right now my client
> >> > (InfoPath) will insert empty strings in tables that have no entry and does
> >> > not recognize that this field is required but empty. I would like SQL server
> >> > to treat empty strings like <NULLS> as mentioned.
> >> >
> >> > Thanks
> >> >
> >> > IT PHYTOSAN
> >> >
> >> > "SQL" wrote:
> >> >
> >> >> To create NULL values from Enterprise Manager use CTRL + 0 instead of
> >> >> delete
> >> >> Hightlight all the text and then press CTRL + 0
> >> >> Or switch to Query analyzer and write UPDATE statements like Tibor
> >> >> showed you that will also give you more control and you will know
> >> >> what's going on
> >> >>
> >> >>
> >> >> Denis the SQL Menace
> >> >> http://sqlservercode.blogspot.com/
> >> >>
> >> >>
> >>
> >>
>

Monday, February 20, 2012

NULL Entry in Lookup

Hi,

In my Integration Scenario I have a source field 'CompanyTypeCode' which is nullable in 'tblCompany' and There is a specific lookup Table 'tblCompanyType' to map the codes with descriptions for this field. When I want to transform this table, The NULL Entries fail in Lookup and I ran into problem. Please suggest a clean solution for bypassing the lookup when the entry in source is NULL. (If I bypass the lookup, the flow changes since there is not an extra column from the lookup component any more, therefore I must have another OLEDB Destination if the code is NULL).

Regards,
Sassan

Sassan,

You can still have the extra column if the lookup fails. What you do is in your error path from the lookup add a derived column for the description and put an "Unknown type" string in there. That way both the pipelines will have the same number of data columns.
You can then 'union all' the clean and error outputs from the lookup to a single destination.

I don't think you should bypass the lookup, but if you want to do it, how about using a conditional split before the lookup to re-route the NULL rows?

Hope that helps.

NULL encountered in math

I need to subtract two values in a table. One of these values contains a
NULL entry. Each time I use the (Pledge - Credit) as 'Difference' I get the
value of NULL.
How do I get T-SQL to replace the NULL with zero?
I encountered this a long time ago while concatenating data. I was able to
use the command SET CONCAT_NULL_YIELDS_NULL OFF for strings. But I can't
find anything for numbers.
Thanks in advance
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:674BC95B-D16C-42B2-ACEA-7F8D799DD38E@.microsoft.com...
>I need to subtract two values in a table. One of these values contains a
> NULL entry. Each time I use the (Pledge - Credit) as 'Difference' I get
> the
> value of NULL.
> How do I get T-SQL to replace the NULL with zero?
> I encountered this a long time ago while concatenating data. I was able
> to
> use the command SET CONCAT_NULL_YIELDS_NULL OFF for strings. But I can't
> find anything for numbers.
> Thanks in advance
You can use COALESCE or ISNULL. Example:
COALESCE(pledge,0) - COALESCE(credit,0) AS difference
David Portas
SQL Server MVP

Wednesday, March 21, 2012

Number of entry query.

Monday, March 12, 2012

Nulls Versus Blanks PArt 2

Nulls Versus Blanks PArt 2

Monday, February 20, 2012

NULL Entry in Lookup

NULL encountered in math

NULL in dimensions

Blog Archive

About Me