SEO

Hi,

I want to run queries on a table that has binary fields in it. How do I
filter on a binary field? E.g. One of the fields is called
'Account_Manager_ID' which is binary - I would like to do a simple Select *
from company where Account_Manager_ID = 'blah blah blah'

When I do this, it returns no data. How do I get round this?

Thanks!

Mintyman (mintyman@ntlworld.com) writes:
> I want to run queries on a table that has binary fields in it. How do I
> filter on a binary field? E.g. One of the fields is called
> 'Account_Manager_ID' which is binary - I would like to do a simple
> Select * from company where Account_Manager_ID = 'blah blah blah'
>
> When I do this, it returns no data. How do I get round this?

WHERE Account_Manager_ID = convert(varbinary, 'blah blah blah')

is what I woudl guess from your description.

But it's probably better if you post the CREATE TABLE statement for
the table, some sample data and an actual query. That would reduce the
amount of guessing.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I tried your suggestion but it still returns no results!

Here is the information you requested:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[_COMPANY]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[_COMPANY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo._COMPANY
AS
SELECT dbo.Company.Company_Id, dbo.Company.Rn_Descriptor,
dbo.Company.Rn_Create_Date, dbo.Company.Rn_Create_User,
dbo.Company.Rn_Edit_Date,
dbo.Company.Rn_Edit_User, dbo.Company.Company_Name,
dbo.Company.Address_1, dbo.Company.Address_2, dbo.Company.Address_3,
dbo.Company.Country, dbo.Company.Zip,
dbo.Company.Phone, dbo.Company.Fax, dbo.Company.WWW,
dbo.Company.Industry_Type,
dbo.Company.State_, dbo.Company.City,
dbo.Company.Lead_Source_Id, dbo.Company.Account_Code,
dbo.Company.Business_Unit,
dbo.Company.Account_Manager_Id,
dbo.Company.Lead_Source_Type, dbo.Territory.Territory_Name,
dbo.Territory.Account_Manager_Id AS Expr1,
dbo.Material.Material_Name
FROM dbo.Company INNER JOIN
dbo.Territory ON dbo.Company.Territory_Id =
dbo.Territory.Territory_Id INNER JOIN
dbo.Material__Bridge ON dbo.Company.Company_Id =
dbo.Material__Bridge.To_Company INNER JOIN
dbo.Material ON dbo.Material__Bridge.From_Materials =
dbo.Material.Material_Id

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Some sample data:

0x00C0000000003B86 Applied Ceramics Incorporated, Fremont - +1 (510)
249-9700 2001-08-21 05:29:15.453 0x0000000000000105 2006-08-02 12:39:42.923
0x00000000000001A6 Applied Ceramics Incorporated 48630 Milmont Drive NULL
NULL United States 94538 +1 (510) 249-9700 NULL appliedceramics.com NULL CA
Fremont NULL NULL NULL 0x00E0000000000003 NULL US - Logitech - West
0x00E0000000000003 Aluminiumoxide
0x00C0000000003B86 Applied Ceramics Incorporated, Fremont - +1 (510)
249-9700 2001-08-21 05:29:15.453 0x0000000000000105 2006-08-02 12:39:42.923
0x00000000000001A6 Applied Ceramics Incorporated 48630 Milmont Drive NULL
NULL United States 94538 +1 (510) 249-9700 NULL appliedceramics.com NULL CA
Fremont NULL NULL NULL 0x00E0000000000003 NULL US - Logitech - West
0x00E0000000000003 Silicon Nitride
0x00C000000000481C Spectrum Petrographics, Winston - +1 (541) 679-5163
2001-08-21 05:34:02.687 0x0000000000000105 2006-03-01 09:49:33.050
0x00000000000000D6 Spectrum Petrographics 499 Dillard Gardens Rd NULL NULL
United States 97496 +1 (541) 679-5163 NULL NULL NULL OR Winston NULL NULL
NULL 0x00E0000000000003 NULL US - Logitech - West 0x00E0000000000003 Ores
0x00C000000000686C Lumenyte International, Irvine - +1 (949) 829-5224
2003-06-19 13:12:13.877 0x00C000000000001B 2006-01-27 11:43:05.553
0x00000000000000D6 Lumenyte International 12 Whatney NULL NULL United States
92618 +1 (949) 829-5224 +1 (949) 829-5267 NULL NULL CA Irvine NULL NULL NULL
0x00E0000000000003 NULL US - Logitech - West 0x00E0000000000003 Acrylic
0x00C00000000068C3 Lawrence Livermore National Labs x, L-250, Livermore - +1
(925) 423-0191 2003-06-19 13:12:13.877 0x00C000000000001B 2006-01-27
11:39:49.550 0x00000000000000D6 Lawrence Livermore National Labs x PO Box
808 NULL NULL United States 94550 +1 (925) 423-0191 +1 (925) 423-0191 NULL
NULL CA Livermore NULL NULL NULL 0x00E0000000000003 NULL US - Logitech -
West 0x00E0000000000003 ADP


The query i'm using is:

select * from _COMPANY where Company_Id = convert(varbinary,
'0x00C0000000003B86')

Hope that helps!

Mintyman (mintyman@ntlworld.com) writes:
> The query i'm using is:
>
> select * from _COMPANY where Company_Id = convert(varbinary,
> '0x00C0000000003B86')

I would have preferred to see the underlying table definition, to be
sure what the data type is. But assuming that it is binary, this should
work:

select * from _COMPANY where Company_Id = 0x00C0000000003B86

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>> I want to run queries on a table that has binary fields [sic] in it.<<

Fields and columns are totally different concepts. If you knew that,
you would not use low-level physical concepts like a binary field in an
SQL database. SQL is based on higher level abstractions. It also uses
a concept called "Normal Forms" that you need to learn; that means that
every column is a scalar value. That means that the bits in the
physical implementation have no meaning in themselves.

>> How do I filter on a binary field? <<

With propriertary, hardware dependent (high end or low end? ones or
twos complement math? how big is a word? etc.) kludges or with
unreadable math expressions, like all bad programmers!

--CELKO-- (jcelko212@earthlink.net) writes:
> Fields and columns are totally different concepts.

For people with low experience of SQL, I should clarify what Joe Celko
is talking about: "Fields" starts with "F", columns starts with "C".

SQL people normally use "rows" and "columns", but we understand "records"
and "fields" without problem. It can even happen that we use them ourselves.
Even Celko has been caught doing it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>> SQL people normally use "rows" and "columns", but we understand "records"and "fields" without problem. <<

NO. NO, NO! Newbies -- not SQL people -- use "records"and "fields"
because they are still thinking in file systems terms and not RDBMS.
They keep thinking in terms of file systems and not RDBMS. Trust me;
I teach these people how to program in SQL.

--CELKO-- wrote:

>>> SQL people normally use "rows" and "columns", but we understand "records"and "fields" without problem. <<
>
> NO. NO, NO! Newbies -- not SQL people -- use "records"and "fields"
> because they are still thinking in file systems terms and not RDBMS.
> They keep thinking in terms of file systems and not RDBMS. Trust me;
> I teach these people how to program in SQL.

It'd help if you would explain what "thinking in file systems terms"
and "thinking in RDBMS terms" actually mean (for records/rows and for
fields/columns). Writing it once and subsequently giving out the URL
would likely be the most efficient approach.

Without such an explanation, newbies are prone to think "oh, this guy
is just some kook obsessed with unimportant jargon" and dismiss it all.

Ed Murphy (emurphy42@socal.rr.com) writes:
> --CELKO-- wrote:
>>>> SQL people normally use "rows" and "columns", but we understand
>>>> "records"and "fields" without problem. <<
>>
>> NO. NO, NO! Newbies -- not SQL people -- use "records"and "fields"
>> because they are still thinking in file systems terms and not RDBMS.
>> They keep thinking in terms of file systems and not RDBMS. Trust me;
>> I teach these people how to program in SQL.
>
> It'd help if you would explain what "thinking in file systems terms"
> and "thinking in RDBMS terms" actually mean (for records/rows and for
> fields/columns). Writing it once and subsequently giving out the URL
> would likely be the most efficient approach.
>
> Without such an explanation, newbies are prone to think "oh, this guy
> is just some kook obsessed with unimportant jargon" and dismiss it all.

Ah, but that what it is! We used to called it subroutines or procedures.
Then Smalltalk came along and now its all "methods".

As for SQL people not using "records" or "fields", I have a very trust-
worthy testimony reporting that Celko said "record" in a conferernce
presentation a year ago. But maybe that's why he keeps nagging this
point: to remind himself.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>>It'd help if you would explain what "thinking in file systems terms" and "thinking in RDBMS terms" actually mean (for records/rows and for fields/columns). <<

I have posted details, I wrote a book on SQL PROGRAMMING STYLE and I am
working on my seventh book now under title "Thinking in Sets" which
deals with table abd data driven solutions versus procedural ones.

>> Writing it once and subsequently giving out the URL would likely be the most efficient approach. <<

One of my resolutions is to start taking care of my website instead of
spending so much time in newsgroups. Probably put something there ..