Query across all colums

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassanot really|||Ok, thanks was worth a question.|||If you are comparing all the columns in one table to all the columns in another, you may be able to use the CHECKSUM or BINARYCHECKSUM functions:

select subA.APKey, sbuB.BPKey
from
(select A.PKey as APKey, CHECKSUM(*) as ChecksumA from A) subA
full outer join
(select B.PKey as BPKey, CHECKSUM(*) as ChecksumB from B) subB
on subA.ChecksumA = subB.ChecksumB
where ...|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:|||While Rudy is quite right in terms of how SQL itself does things, there are extensions to many database engines that make this kind of search easier. In MS-SQL, this is called Full Text Indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_5dro.asp).

-PatP|||pat, all things considered, full text indexing is not "simpler" (one of shabassa's original requirements)

:)|||From the standpoint of managing the server, you are quite right. Full-Text Indexing brings its own problems to the table.

From the standpoint of writing a search query though, I feel that it is simpler and I don't think anyone would argue it is much less code.

-PatP|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:

Could be. Without a doubt he needs to be more specific.|||I doubt it though....

I think they're looking for duplicates...

Or they're just trying to establish a join between to related tables, just in the keys?|||Or attempting to create a search feature for their application that searches against a catalog of objects where the entry could match a value in any number of fields (Ex: Name, Description, Price, etc).|||Step right up, folks! Play "Guess the user requirements" and win a prize for the little lady! Who's next? Every player has a chance to win! You sir, yes you...!|||Well if I was programming an application that searched across the table there would be no problem doing it in some kind of loop. I was just lazy and wanted to look for a certain row where I entered a date in an application accessing that db. But I didn't know to which colum the text field in the app corresponded to. So I wanted to search across all columns at once using the Enterprise Manager.
I hope that clears things up. ;)

How would this be possible with full text indexing? Do I magically get some kind of search field?

Shabassa

query

Can I have conatins clause like following
where contains(col_name ,'%')
where contains (col_name , '/*')
basically looking for special characters in text columnsHi.
Perhaps you want to look at this example
CREATE TABLE #TEST
(
COL VARCHAR(50)
)
INSERT INTO #TEST VALUES ('FF')
INSERT INTO #TEST VALUES ('F%F')
INSERT INTO #TEST VALUES ('FF%')
INSERT INTO #TEST VALUES ('NNFF')
INSERT INTO #TEST VALUES ('F88F')
INSERT INTO #TEST VALUES ('*FF')
SELECT * FROM #TEST WHERE CHARINDEX('%',COL)>0
<anonymous@.discussions.microsoft.com> wrote in message
news:085e01c39dfe$ff0d6db0$a501280a@.phx.gbl...
> Can I have conatins clause like following
>
> where contains(col_name ,'%')
> where contains (col_name , '/*')
> basically looking for special characters in text columns
>

Queries using bitwise?

Hi Everyone,
I've using the query below to extract some information. ONe of the items in
the where clause is a check for an integer column for 0x1.
Question: What is the correct syntax for checking this column? As below, I
have pm.ProductStatus = 0x1. This is returning rows. Am I correct in this?
Thanks in advance
Larry
SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
FROM
Account_Master am
INNER JOIN ClientProductCatalog cpc
ON am.AccountID = cpc.AccountID
INNER JOIN ProductMaster pm
ON cpc.ProductID = pm.ProductID
INNER JOIN ClientProductWorkflowStatus cpws
ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
INNER JOIN WorkflowStatusTypeLkp wfst
ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
INNER JOIN WorkflowTypeLkp wtl
ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
customer?
AND cpc.Deactivated = 0 -- Product still active?
AND pm.ProductStatus = 0x1 -- Awaiting status?> Question: What is the correct syntax for checking this column? As below,
I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in
this?
The equal operator is NOT a bitwise operator. Therefore, the answer is "NO"
if you are attempting to limit the resultset to rows where ProductStatus has
the lowest bit set. If this is actually what you are after, then this
highlights a potential flaw in your design. Product status appears to
contain multiple pieces of information in a single column, a violation of
first normal form. On the flip side, perhaps you want status values that are
odd?
Assuming you want to continue down this path, then have a look in BOL for
the bitwise operators. As a hint, you will need to use the bitwise AND
operator, using an expression in the form of "(mycolumn AND y) = z" (note -
pseudocode).|||Larry,
How can we know if this is the correct syntax to check if you don't
say what you are trying to check? The query you posted will select
rows where pm.productStatus equals 1. If that's what you want, then
that's what you'll get. If that's not what you want, you need to tell us
what it is that you want. You say the query is returning rows. Have
you tried to run this on some test data to see if they are the rows you
want?
It's not clear why you are expressing the number 1 as 0x1, by the way.
The query you are posting here would be easier to read if you wrote
pm.productStatus = 1.
Please provide more information.
Larry wrote:

>Hi Everyone,
>I've using the query below to extract some information. ONe of the items i
n
>the where clause is a check for an integer column for 0x1.
>Question: What is the correct syntax for checking this column? As below,
I
>have pm.ProductStatus = 0x1. This is returning rows. Am I correct in this
?
>Thanks in advance
>Larry
>
>SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
>cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
>FROM
>Account_Master am
>INNER JOIN ClientProductCatalog cpc
>ON am.AccountID = cpc.AccountID
>INNER JOIN ProductMaster pm
>ON cpc.ProductID = pm.ProductID
>INNER JOIN ClientProductWorkflowStatus cpws
>ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
>INNER JOIN WorkflowStatusTypeLkp wfst
>ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
>INNER JOIN WorkflowTypeLkp wtl
>ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
>WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
>customer?
>AND cpc.Deactivated = 0 -- Product still active?
>AND pm.ProductStatus = 0x1 -- Awaiting status?
>|||Larry - The bitwise operators (from BOL) are
bitwise logical And ... &
bitwise logical or ... |
bitwise NOT ............ ~
bitwise exclusive or ... ^
So to test if an integral value has a particular bit SET
(Defined by a bitmask with all zeros but one)
Bitmask must be decimal 1,2,4,8,16, etc, or hex 0x1, 0x2, 0x4, 0x8, 0x10,
0x20, etc
use logical And ....... Where (Value & @.BitMask) <> 0
- If the result is non-zero, the Bit was set
To test if a particular bit is NOT SET
flip the bits of the value with NOT (~) and test that with the bitmask using
And (&)
Where (~Value & @.BitMask) <> 0
"Larry" wrote:

> Hi Everyone,
> I've using the query below to extract some information. ONe of the items
in
> the where clause is a check for an integer column for 0x1.
> Question: What is the correct syntax for checking this column? As below,
I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in thi
s?
> Thanks in advance
> Larry
>
> SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
> cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
> FROM
> Account_Master am
> INNER JOIN ClientProductCatalog cpc
> ON am.AccountID = cpc.AccountID
> INNER JOIN ProductMaster pm
> ON cpc.ProductID = pm.ProductID
> INNER JOIN ClientProductWorkflowStatus cpws
> ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
> INNER JOIN WorkflowStatusTypeLkp wfst
> ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
> INNER JOIN WorkflowTypeLkp wtl
> ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
> WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
> customer?
> AND cpc.Deactivated = 0 -- Product still active?
> AND pm.ProductStatus = 0x1 -- Awaiting status?|||You can use a simple integer one constant for this. Why do you want to
make the code proprietary, unreadable and unmaintainable?
There are many things we do not do in SQL and one of them is low-level,
bit and byte operations. That is what assembly languages and things
like C are for. The next error is writing a lot of flags into the
schema, to mimic an old punch card file system. For example,
"deactivated" looks like a flag which should be a more general status
code or an even date.
We also do not construct data element names like "WorkflowTypeLkpID",
since a thing cannot be both a type code and an identfier. Where is
the "ClientProducts" or perhaps "ClientProductWorkflow" table which
would have "ClientProductWorkflowStatus" in a column, since a status
code is not an entity?
You confuse data and metadata concepts. I would bet you are used to an
OO language with lots of low-level constructs, without even a year of
data modeling.|||Is it not the case that bit wise operators are part of the SQL 99
specification?
Thomas
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:0F8877FE-B2B3-416C-9803-C2501F1C60A5@.microsoft.com...
> Hi Everyone,
> I've using the query below to extract some information. ONe of the items
> in
> the where clause is a check for an integer column for 0x1.
> Question: What is the correct syntax for checking this column? As below,
> I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in
> this?
> Thanks in advance
> Larry
>
> SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
> cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
> FROM
> Account_Master am
> INNER JOIN ClientProductCatalog cpc
> ON am.AccountID = cpc.AccountID
> INNER JOIN ProductMaster pm
> ON cpc.ProductID = pm.ProductID
> INNER JOIN ClientProductWorkflowStatus cpws
> ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
> INNER JOIN WorkflowStatusTypeLkp wfst
> ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
> INNER JOIN WorkflowTypeLkp wtl
> ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
> WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
> customer?
> AND cpc.Deactivated = 0 -- Product still active?
> AND pm.ProductStatus = 0x1 -- Awaiting status?|||Politics. There were products that still had a low-level
implementation based on particular hardware. Did you look at the
SQL:2003 that deprecared bit data types? Did you look at the problems
in implementations in actual products?|||Only AND, OR, and NOT, and only for data of type BOOLEAN, are
in the SQL-99 standard as far as I know. I wouldn't expect the standard
to specify bitwise operations for numerical data, or any other operators
that behaved as though the data were represented in a particular way.
Steve Kass
Drew University
Thomas C wrote:

>Is it not the case that bit wise operators are part of the SQL 99
>specification?
>
>Thomas
>"Larry" <Larry@.discussions.microsoft.com> wrote in message
>news:0F8877FE-B2B3-416C-9803-C2501F1C60A5@.microsoft.com...
>
>
>