How can I check for Null or Empty in an Insert/Select Statement

Is there a way in the following Query to check if the _vocon.COMPANY is
null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
statement 2) as each record is processed. I would like to use something like
IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id +
10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
If the company name is null
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
'01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003Hi
You don't give the version of SQLServer, it is also better to post DDL
(Create table statements etc...), example data (as Insert statements) to
avoid ambiguities.
The behaviour of concatenating with null can be set with the
CONCAT_NULL_YIELDS_NULL option see:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_set-set_2z8s.htm
To update your values you could use:
UPDATE _vocon
SET Company = Id + 10200000
WHERE Company IS NULL
OR LEN(RTRIM(Company)) = 0
John
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uqveIhNzDHA.2568@.TK2MSFTNGP09.phx.gbl...
> Is there a way in the following Query to check if the _vocon.COMPANY is
> null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
> statement 2) as each record is processed. I would like to use something
like
> IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id
+
> 10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM
_vocon
> If the company name is null
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
> '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
> Regards
> Jeff
>
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003
>

How are the drill details determined?

Hello - I am using report builder against models.

Which attributes or properties determine what is shown when you do click/drill into the details? I'm using the adventure works database as my guide and expected the (default,Identifying or Aggregate ) attributes to control it, can you summarize how the drill details are determined? My test from adventure works sample:

Pull Territory Name and #customers into the report Run it and drill into the details on one of the #customers It returns Acct#, CustType, CustName, #SalesOrders, Sum of a bunch of sales order fields and # customer addresses. The first three are the default attributes for a customer and the last sets look to be the default aggregates for the other relations to customer - Sales Order & Address.

Is it safe to say that it pulls in the default attributes of the immediate child and then aggregates of it's children? Does it go recursively?

Thanks in advance,

Toni

You are basically correct. I believe it also pulls in identifying attributes of entities for which there is only 1 related instance. No, it is not recursive. If you want full control over the set of fields displayed, you can build your own Clickthrough template report and use SQL Management Studio to configure it as one of the template reports for the target entity (e.g. Customer).

Hope that helps!

Hotfix Versions

Hi,
Is there someplace where I can find a definitve reference as to which
hotfix has been installed and relate that to the version shown by
'SELECT @.@.VERSION' post SQL Server 2000 SP3?
Most references I have found stop at SP3.
Many thanks
Simonhttp://www.aspfaq.com/show.asp?id=2160
Jacco Schalkwijk
SQL Server MVP
"Simon Adams" <warmtea@.hotmail.com> wrote in message
news:c34aa6c2.0502160534.4f3e04f6@.posting.google.com...
> Hi,
> Is there someplace where I can find a definitve reference as to which
> hotfix has been installed and relate that to the version shown by
> 'SELECT @.@.VERSION' post SQL Server 2000 SP3?
> Most references I have found stop at SP3.
> Many thanks
> Simon

Hotfix Versions

Hi,
Is there someplace where I can find a definitve reference as to which
hotfix has been installed and relate that to the version shown by
'SELECT @.@.VERSION' post SQL Server 2000 SP3?
Most references I have found stop at SP3.
Many thanks
Simon
http://www.aspfaq.com/show.asp?id=2160
Jacco Schalkwijk
SQL Server MVP
"Simon Adams" <warmtea@.hotmail.com> wrote in message
news:c34aa6c2.0502160534.4f3e04f6@.posting.google.c om...
> Hi,
> Is there someplace where I can find a definitve reference as to which
> hotfix has been installed and relate that to the version shown by
> 'SELECT @.@.VERSION' post SQL Server 2000 SP3?
> Most references I have found stop at SP3.
> Many thanks
> Simon

Hotfix Versions

Hi,
Is there someplace where I can find a definitve reference as to which
hotfix has been installed and relate that to the version shown by
'SELECT @.@.VERSION' post SQL Server 2000 SP3?
Most references I have found stop at SP3.
Many thanks
Simonhttp://www.aspfaq.com/show.asp?id=2160
--
Jacco Schalkwijk
SQL Server MVP
"Simon Adams" <warmtea@.hotmail.com> wrote in message
news:c34aa6c2.0502160534.4f3e04f6@.posting.google.com...
> Hi,
> Is there someplace where I can find a definitve reference as to which
> hotfix has been installed and relate that to the version shown by
> 'SELECT @.@.VERSION' post SQL Server 2000 SP3?
> Most references I have found stop at SP3.
> Many thanks
> Simon