query

Hi
I have a column in an SQL table. the column is all numeric
data from 100 to more than 400. I would like to convert
the data in this column to something else. e.g.
micro = <200
mini = between 200 and 300
midi = between 300 and 400
full > 400
can anyone advise me how to write a query to establish the
above task.
Thank you.
Freger
On Thu, 13 May 2004 02:18:23 -0700, Freger wrote:

>Hi
>I have a column in an SQL table. the column is all numeric
>data from 100 to more than 400. I would like to convert
>the data in this column to something else. e.g.
>micro = <200
>mini = between 200 and 300
>midi = between 300 and 400
>full > 400
>can anyone advise me how to write a query to establish the
>above task.
>Thank you.
>Freger
Hi Freger,
This can be accomplished using CASE:
SELECT CASE
WHEN (NumericData <= 200)
THEN 'Micro'
WHEN (NumericData <= 300)
THEN Mini'
WHEN (NumericData <= 400)
THEN 'Midi'
ELSE 'Full'
END AS ColumnAlias
FROM YourTable
Replace column and table names as appropriate.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you, Hugo.[vbcol=seagreen]
>--Original Message--
>On Thu, 13 May 2004 02:18:23 -0700, Freger wrote:
numeric[vbcol=seagreen]
the
>Hi Freger,
>This can be accomplished using CASE:
> SELECT CASE
> WHEN (NumericData <= 200)
> THEN 'Micro'
> WHEN (NumericData <= 300)
> THEN Mini'
> WHEN (NumericData <= 400)
> THEN 'Midi'
> ELSE 'Full'
> END AS ColumnAlias
> FROM YourTable
>Replace column and table names as appropriate.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>

query

Hi,
i have a model column and has all sort of names.
E.g.
Model names
CRUZERCROSSFIRE00512
EXTREME512MBCF
EXTREMECF00256
EXTREMEIIISECUREDIGITAL
ULTRAIIMAGICGATEMP00512
ULTRAIIMAGICGATEMP01024
X00512
I have a new column and this new column is to differentiate the model
belongs to High or Std. Any model name that starts with Extreme or Ultra will
be High and others will be Std
e.g.
Model New Column
CRUZERCROSSFIRE00512 Std
EXTREME512MBCF High
EXTREMECF00256 High
ULTRAIIMAGICGATEMP01024 High
ULTRAIIMAGICGATEMP00512 High
X00512 Std
I am not sure how to write a query to populate the data in the new column.
Kindly advise.
Thank you
Hi 'Tiffany',
It's considered a bad idea to take over another person's thread. You should start you own. Folks may not look it your problem since they will consider it a continuation of the previous thread and you may not get help.
You could try something like:
SELECT
Model,
NewColumn = CASE
WHEN Model LIKE 'EXTREME%' THEN 'High'
WHEN Model LIKE 'ULTRA%' THEN 'High'
ELSE 'Std'
END
FROM MyTable
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message news:4DF539CC-CD49-49EE-9E6B-B7084EF0B602@.microsoft.com...
> Hi,
> i have a model column and has all sort of names.
> E.g.
> Model names
> CRUZERCROSSFIRE00512
> EXTREME512MBCF
> EXTREMECF00256
> EXTREMEIIISECUREDIGITAL
> ULTRAIIMAGICGATEMP00512
> ULTRAIIMAGICGATEMP01024
> X00512
> I have a new column and this new column is to differentiate the model
> belongs to High or Std. Any model name that starts with Extreme or Ultra will
> be High and others will be Std
> e.g.
> Model New Column
> CRUZERCROSSFIRE00512 Std
> EXTREME512MBCF High
> EXTREMECF00256 High
> ULTRAIIMAGICGATEMP01024 High
> ULTRAIIMAGICGATEMP00512 High
> X00512 Std
> I am not sure how to write a query to populate the data in the new column.
> Kindly advise.
> Thank you
>
>
|||Hugo,
I wasn't trying to give Tiffany grief, just concerned that her posting would
be 'lost' and not garner any attention.
And yes, I 'forgot' that OE can combine different threads that just happen
to have the same subject.
About your request that I alter my sending settings, I have no reason not to
do what you ask, but I just can't find any settings similar to 'Settings for
Unformatted text". Also can't find any information on "Line Breaks", or
"unformatted text" in OE help. Any other ideas?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:opgnk2dp2h7t1sgovd8skqfnd3iefd14q6@.4ax.com...
> On Sun, 29 Oct 2006 19:09:36 -0800, Arnie Rowland wrote:
>
> Hi Arnie,
> Just as an FYI, Tiffany didn't take over any thread. She posted a new
> thread, with a subject that happens to be quite popular. So your news
> reading software (like mine) uses a title matching algorithm to show you
> a threaded structure that never existed.
> This technique is great to keep thread structure intact if some posts
> don't contain accurate "references" headers, but it bites you in the leg
> with unrelated postings that use a common subject.
> Oh, and while we're on the subject of news readers, could you please set
> up your news client to include line breaks, as still is standard on
> usenet? I see that you're using Outlook Express; you'll find the option
> to change this throug "Extra" / "Options" / "Send" / (Layout for sending
> news) "Settings for unformatted text"
> --
> Hugo Kornelis, SQL Server MVP

query

hi,
Have a problem and hope someone can help.
I have a column which consists of numbers such as
123
235
456
888
etc
How can i write a simple query to change them to
00123
00235
00456
00888
etc
Kindly advise.
Thank you very much
you can concatenate the data using a + sign.
Select '00' + cast(ColumnNameHere as varchar(255)) from tablename
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:47F589A4-FD90-42C0-8833-6D3D93E28C0A@.microsoft.com...
> hi,
> Have a problem and hope someone can help.
> I have a column which consists of numbers such as
> 123
> 235
> 456
> 888
> etc
> How can i write a simple query to change them to
> 00123
> 00235
> 00456
> 00888
> etc
> Kindly advise.
> Thank you very much
>
|||Thank you very much. What if in the column, there is also alphabets like
abc
rde
fgd
123
256
and I just want 00+ to apply to numbers only. What should I do? Kindly advise.
Thank you
"Warren Brunk" wrote:

> you can concatenate the data using a + sign.
> Select '00' + cast(ColumnNameHere as varchar(255)) from tablename
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
> news:47F589A4-FD90-42C0-8833-6D3D93E28C0A@.microsoft.com...
>
>
|||On Wed, 25 Oct 2006 22:30:01 -0700, Tiffany wrote:

>Thank you very much. What if in the column, there is also alphabets like
>abc
>rde
>fgd
>123
>256
>and I just want 00+ to apply to numbers only. What should I do? Kindly advise.
Hi Tiffany,
Use a CASE expression:
SELECT CASE
WHEN ColumnNameHere NOT LIKE '%[^0-9]%'
THEN '00' ELSE '' END + ColumnNameHere
FROM TableNameHere;
Hugo Kornelis, SQL Server MVP
|||Hugo,
Instead of combining NOT LIKE and [^0-9], wouldn't that be easier to understand as:
SELECT CASE
WHEN ColumnNameHere LIKE '%[0-9]%' THEN '00'
ELSE ''
END + ColumnNameHere
FROM TableNameHere;
(I've always hated double negatives...)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message news:fq52k2hgo2p6nfpg4jq67bvkml3r3hnhhn@.4ax.com...
> On Wed, 25 Oct 2006 22:30:01 -0700, Tiffany wrote:
>
> Hi Tiffany,
> Use a CASE expression:
> SELECT CASE
> WHEN ColumnNameHere NOT LIKE '%[^0-9]%'
> THEN '00' ELSE '' END + ColumnNameHere
> FROM TableNameHere;
> --
> Hugo Kornelis, SQL Server MVP
|||On Thu, 26 Oct 2006 13:41:15 -0700, Arnie Rowland wrote:

>Hugo,
>Instead of combining NOT LIKE and [^0-9], wouldn't that be easier to understand as:
>SELECT CASE
> WHEN ColumnNameHere LIKE '%[0-9]%' THEN '00'
> ELSE ''
> END + ColumnNameHere
>FROM TableNameHere;
>(I've always hated double negatives...)
Hi Arnie,
Easier to understand, but incorrect as well. Try it with '1s3' or 'af7'
as input data.
Double negatives are a bane for language, but a boon for SQL. Not only
for relational division, but also for constructing LIKE clauses. The
very limited regexp powers of LIKE don't allow for a test of "all
characters have to be numeric", but they do allow a test for "no
character may not be numeric".
Of course, if the length of the string is guaranteed to be always three
characters, you could also use LIKE '[0-9][0-9][0-9]'.
Hugo Kornelis, SQL Server MVP
|||I see your point Hugo.
Your illustration of using the double negative will cover all
possibilities -pure or mixed alphanumerics, whereas my method only covers
all alpha or all numeric -but not the mixed possibilities.
For the OP's situation as presented (all alpha or all numeric), either will
work, but your method is definitely more robust.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:beb2k21s9ptltdqsou2dojs4ejbmpdcbt4@.4ax.com...
> On Thu, 26 Oct 2006 13:41:15 -0700, Arnie Rowland wrote:
>
> Hi Arnie,
> Easier to understand, but incorrect as well. Try it with '1s3' or 'af7'
> as input data.
> Double negatives are a bane for language, but a boon for SQL. Not only
> for relational division, but also for constructing LIKE clauses. The
> very limited regexp powers of LIKE don't allow for a test of "all
> characters have to be numeric", but they do allow a test for "no
> character may not be numeric".
> Of course, if the length of the string is guaranteed to be always three
> characters, you could also use LIKE '[0-9][0-9][0-9]'.
> --
> Hugo Kornelis, SQL Server MVP
|||the isnumeric function is probably the most efficient way of accomplishing
this task
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23ZcV%23YZ%23GHA.4704@.TK2MSFTNGP04.phx.gbl.. .
>I see your point Hugo.
> Your illustration of using the double negative will cover all
> possibilities -pure or mixed alphanumerics, whereas my method only covers
> all alpha or all numeric -but not the mixed possibilities.
> For the OP's situation as presented (all alpha or all numeric), either
> will work, but your method is definitely more robust.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:beb2k21s9ptltdqsou2dojs4ejbmpdcbt4@.4ax.com...
>
|||On Fri, 27 Oct 2006 09:44:03 -0400, Michael Abair wrote:

>the isnumeric function is probably the most efficient way of accomplishing
>this task
Hi Michael,
Except that isnumeric returns 1 if the value passed to it can be
converted to at least one numeric datatype - not necessary int.
Try
SELECT ISNUMERIC('$'), ISNUMERIC('1E3'), ISNUMERIC('1.d2')
Hugo Kornelis, SQL Server MVP
sql

query

Hi,

select * from test a where test.col1 = a;

The above query will not work and it will throw the error 'The column prefix 'test' does not match with a table name or alias name used in the query'. i.e if alias defined for a table, then the columns can be qualified only using using the alias. Is it true ? Is it true in all the places like "Group By', 'Order' , 'SELECT list' etc. Is that true in all databases, is it ANSI standard ?

Please advice,

Thanks,
MiraJhi

from BOL
"If an alias is assigned to a table, all explicit references to the table in the Transact-SQL statement must use the alias, not the table name."

query

Hello,
I wondered if anyone can help me? i'm trying to query a table, although
want to preform two searches on the same table/column then display the
results.. these are my two select statements. how can I join them
together so they display side by side.
SELECT COUNT(staff.sex) as "Number of Female members"
FROM staff
WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
SELECT COUNT(staff.sex) as "Number of Male members"
FROM staff
WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
Thanks for you help
The following example should return the desired results:
CREATE TABLE dbo.Staff
(
sex VARCHAR(10),
deptName VARCHAR(20)
)
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'Other department'
INSERT dbo.Staff SELECT 'Male', 'Other department'
SELECT COUNT(SEX) AS [Number of Male members],
(SELECT COUNT(SEX)
FROM dbo.staff
WHERE sex = 'Female'
AND deptName = 'CIS department') AS [Number of Female members]
FROM dbo.staff
WHERE sex = 'Male'
AND deptName = 'CIS department'
HTH
- Peter Ward
WARDY IT Solutions
"davidjohnlong@.googlemail.com" wrote:

> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
|||If you would rather return your data in rows, you could try:
SELECT COUNT(sex) [Number of members],
CASE sex
WHEN 'Mmale' THEN 'Male'
WHEN 'Female' THEN 'Female'
ELSE 'Unknown'
END [Sex]
FROM staff
WHERE deptName = 'CIS department'
GROUP BY [sex]
|||davidjohnlong@.googlemail.com a crit :
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
SELECT SUM(CASE staff.sex
WHEN 'Female' THEN 1
WHEN 'Male' THEN 0
END) AS "Number of Female members",
SUM(CASE staff.sex
WHEN 'Female' THEN 0
WHEN 'Male' THEN 1
END) AS "Number of Male members"
FROM staff
WHERE staff.deptName = 'CIS department'
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

query

Hello,
I wondered if anyone can help me? i'm trying to query a table, although
want to preform two searches on the same table/column then display the
results.. these are my two select statements. how can I join them
together so they display side by side.
SELECT COUNT(staff.sex) as "Number of Female members"
FROM staff
WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
SELECT COUNT(staff.sex) as "Number of Male members"
FROM staff
WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
Thanks for you helpThe following example should return the desired results:
CREATE TABLE dbo.Staff
(
sex VARCHAR(10),
deptName VARCHAR(20)
)
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'Other department'
INSERT dbo.Staff SELECT 'Male', 'Other department'
SELECT COUNT(SEX) AS [Number of Male members],
(SELECT COUNT(SEX)
FROM dbo.staff
WHERE sex = 'Female'
AND deptName = 'CIS department') AS [Number of Female members]
FROM dbo.staff
WHERE sex = 'Male'
AND deptName = 'CIS department'
HTH
- Peter Ward
WARDY IT Solutions
"davidjohnlong@.googlemail.com" wrote:
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>|||If you would rather return your data in rows, you could try:
SELECT COUNT(sex) [Number of members],
CASE sex
WHEN 'Mmale' THEN 'Male'
WHEN 'Female' THEN 'Female'
ELSE 'Unknown'
END [Sex]
FROM staff
WHERE deptName = 'CIS department'
GROUP BY [sex]|||davidjohnlong@.googlemail.com a écrit :
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
SELECT SUM(CASE staff.sex
WHEN 'Female' THEN 1
WHEN 'Male' THEN 0
END) AS "Number of Female members",
SUM(CASE staff.sex
WHEN 'Female' THEN 0
WHEN 'Male' THEN 1
END) AS "Number of Male members"
FROM staff
WHERE staff.deptName = 'CIS department'
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************sql

query

Hello,
I wondered if anyone can help me? i'm trying to query a table, although
want to preform two searches on the same table/column then display the
results.. these are my two select statements. how can I join them
together so they display side by side.
SELECT COUNT(staff.sex) as "Number of Female members"
FROM staff
WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
SELECT COUNT(staff.sex) as "Number of Male members"
FROM staff
WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
Thanks for you helpThe following example should return the desired results:
CREATE TABLE dbo.Staff
(
sex VARCHAR(10),
deptName VARCHAR(20)
)
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Male', 'CIS department'
INSERT dbo.Staff SELECT 'Female', 'Other department'
INSERT dbo.Staff SELECT 'Male', 'Other department'
SELECT COUNT(SEX) AS [Number of Male members],
(SELECT COUNT(SEX)
FROM dbo.staff
WHERE sex = 'Female'
AND deptName = 'CIS department') AS [Number of Female members]
FROM dbo.staff
WHERE sex = 'Male'
AND deptName = 'CIS department'
HTH
- Peter Ward
WARDY IT Solutions
"davidjohnlong@.googlemail.com" wrote:

> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>|||If you would rather return your data in rows, you could try:
SELECT COUNT(sex) [Number of members],
CASE sex
WHEN 'Mmale' THEN 'Male'
WHEN 'Female' THEN 'Female'
ELSE 'Unknown'
END [Sex]
FROM staff
WHERE deptName = 'CIS department'
GROUP BY [sex]|||davidjohnlong@.googlemail.com a crit :
> Hello,
> I wondered if anyone can help me? i'm trying to query a table, although
> want to preform two searches on the same table/column then display the
> results.. these are my two select statements. how can I join them
> together so they display side by side.
> SELECT COUNT(staff.sex) as "Number of Female members"
> FROM staff
> WHERE staff.sex = 'Female' and staff.deptName = 'CIS department'
> SELECT COUNT(staff.sex) as "Number of Male members"
> FROM staff
> WHERE staff.sex = 'Mmale' and staff.deptName = 'CIS department'
> Thanks for you help
>
SELECT SUM(CASE staff.sex
WHEN 'Female' THEN 1
WHEN 'Male' THEN 0
END) AS "Number of Female members",
SUM(CASE staff.sex
WHEN 'Female' THEN 0
WHEN 'Male' THEN 1
END) AS "Number of Male members"
FROM staff
WHERE staff.deptName = 'CIS department'
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Querring only non NULL row/column

Hi,
I want to query few rows from a table. I don't want to get a row, where
certian column has a NULL value.
How can I do that?select * from table
where field is not null
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:0DAA7CB9-1BF4-4091-A7B0-FCA90581060E@.microsoft.com...
> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT * FROM Table
WHERE column IS NOT NULL
"mavrick101" wrote:

> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT ...
FROM YourTable
WHERE col_x IS NOT NULL
David Portas
SQL Server MVP
--

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...
>
>
>

Quarterly Chart

Hi, i have a table with column for quarter and year (just like the sample Report - Company Sales).
now, i'm trying to create a chart which shows the quarter as the series group /x-axis... how can i show the label of the quarter and the year. currently, it can only use either year or quarter.
so, what i want the x-axis label to be like this:
e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
2003 2004If you want the quarter to show up on the x-axis, then it has to be a
category group, not a series group. Just use two category groupings (one for
quarter, one for year) and you should get the desired labels.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> Hi, i have a table with column for quarter and year (just like the sample
Report - Company Sales).
> now, i'm trying to create a chart which shows the quarter as the series
group /x-axis... how can i show the label of the quarter and the year.
currently, it can only use either year or quarter.
> so, what i want the x-axis label to be like this:
> e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> 2003 2004|||ah yes, i meant the category. however, the label can only show either quarter or year. not both.
i need to differentiate between the first q1 and the second q1. I can only put on field for the 'label'. if i leave it blank, it will only show it by the quarter.
"Robert Bruckner [MSFT]" wrote:
> If you want the quarter to show up on the x-axis, then it has to be a
> category group, not a series group. Just use two category groupings (one for
> quarter, one for year) and you should get the desired labels.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> > Hi, i have a table with column for quarter and year (just like the sample
> Report - Company Sales).
> > now, i'm trying to create a chart which shows the quarter as the series
> group /x-axis... how can i show the label of the quarter and the year.
> currently, it can only use either year or quarter.
> >
> > so, what i want the x-axis label to be like this:
> > e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> > 2003 2004
>
>|||Please try and investigate the attached example which gives you two levels
of groupings on the category axis (inner level: month, outer level: year).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Type>Circle</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Visible>true</Visible>
</DataLabel>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales / Cost</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="YearGroup">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="MonthGroup">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.625in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||got it... i didn't pay attention to the category groups, instead i put it in the group on inside the category group.
"Robert Bruckner [MSFT]" wrote:
> Please try and investigate the attached example which gives you two levels
> of groupings on the category axis (inner level: month, outer level: year).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="TotalSalesByYear">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style />
> <Legend>
> <Visible>true</Visible>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <Color>Brown</Color>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)</Value>
> </DataValue>
> </DataValues>
> <DataLabel />
> <Marker>
> <Type>Circle</Type>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=(Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)+8000)*1.15</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> <Visible>true</Visible>
> </DataLabel>
> <Marker>
> <Type>Diamond</Type>
> <Size>10pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> <PlotType>Line</PlotType>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>100</PointWidth>
> <Type>Line</Type>
> <Title>
> <Caption>Sales / Cost</Caption>
> <Style>
> <FontSize>14pt</FontSize>
> <FontWeight>700</FontWeight>
> </Style>
> </Title>
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="YearGroup">
> <GroupExpressions>
> <GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label />
> </DynamicCategories>
> </CategoryGrouping>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="MonthGroup">
> <GroupExpressions>
> <GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!OrderDate.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Height>6.125in</Height>
> <SeriesGroupings>
> <SeriesGrouping>
> <StaticSeries>
> <StaticMember>
> <Label>Cost</Label>
> </StaticMember>
> <StaticMember>
> <Label>Sales</Label>
> </StaticMember>
> </StaticSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundGradientEndColor>White</BackgroundGradientEndColor>
> <BackgroundGradientType>TopBottom</BackgroundGradientType>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <ValueAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <MinorTickMarks>Outside</MinorTickMarks>
> <Min>0</Min>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>6.5in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>7.625in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Quantity">
> <DataField>Quantity</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="OrderDate">
> <DataField>OrderDate</DataField>
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
> Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
> Details].OrderID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>

qry wont use index

I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:

select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)

In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.

EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.

What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).

-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.

Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.

Let me know what you find out, I'm curious now!

-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.

QRY question: If field1 is null then field2

Is there a way to create a column that will show the
value of field1 unless field1 is null in which case it
will show the value of field2? I do it all the time in
Access but can't seem to figure out if SQL Server can do
it as well. Any help or suggestions are geratly
appreciatd!
Here is how I do it in MS Access:
SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
FROM [Table];
Dan,
In SQL Server, you can write
CASE WHEN Field1 IS NULL THEN Field2 ELSE Field1 END AS [Output]
Since this particular need comes up often, there is a shorthand form:
COALESCE(Field1, Field2) AS [Output]
Steve Kass
Drew University
Dan wrote:

>Is there a way to create a column that will show the
>value of field1 unless field1 is null in which case it
>will show the value of field2? I do it all the time in
>Access but can't seem to figure out if SQL Server can do
>it as well. Any help or suggestions are geratly
>appreciatd!
>Here is how I do it in MS Access:
>SELECT IIf([Field1] Is Null,[Field2],[Field1]) AS [Output]
>FROM [Table];
>

QA returning negative #s as positive

Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Thanks in advance.I haven't had a problem with this. What does your query look like? Are the correct results being retuned to your ASP.NET application?

Terri|||Terri,

The query is a straight forward select, nothing tricky at all. Basically, if I look at the Amount values through the analyzer, it displays them as positive. However, one interesting to note is that it recognizes the number as being negative because if i add another column to the return so that it's "Amount * 1", it will return the correct (negative) value.

It basically seems to be a displaying issue. I google'd this issue, and came across a few posts from other forums where people were having the same issue, but none of the threads had an explanation.

Also, this is a problem only with this table. There are other tables in the warehouse that have decimal(18,2) columns with negative values, and they get displayed correctly. At the same time, though, there's nothing special about the table in question. The amount column is sourced from a different table with a column of number(21,6) type, which is really a glorified decimal, so that doesn't seem to be the problem.

I'm pretty much puzzled as to why this column would be displayed incorrectly like this. I welcome any suggestions/ideas.

Thanks|||Have you been able to resolve this issue? I have been out of town this past week and will jump back into this if you are still stuck.

Terri|||Hey Terri,

Actually, it's been sitting on the back burner, and I've been busy with other things. I would, ultimately, like to figure out what is happening, so if you have any ideas, I am all ears.

I did a search on Google groups, this forum, and a few others, and was unable to find an answer. I did find a few threads on various sources concerning this problem, but there was no resolution from anyone.

Thanks for the help.

Q270636

I have used Delphi 6 as front end and sql server 2000 as backend.
In delphi i have used dbgrid to for data entry.In dbgrid, in a single column entry is allowed.The requirement is to show the total of the values entered in this column.For this purpose I have written after post and before edit events of dataset.
But when two users try to make entry in this grid simaltaneously from different machines 'Row can not be located for updating. Since it was last read' error occurs.it's because the methodology you selected is not designed for multi-user environment.

Q: time convert

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.Jim,
I would try an Instr() function to find the position of the ":" and then
split the hours from the minutes (6) (30) with a mid() function; then sort on
the two values. But I usually find the hard way -- I'm sure there is an
easier way.
:-)
HTH
Jim
"JIM.H." wrote:
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by time.
> How should I do this?
> Thanks,
> Jim.
>|||If you want to do the conversion in the SQL query, you could try and
use the Convert(datetime, field) function, not sure if it will accept
just the time. If not prefix string, eg
Convert(datetime, '1901/01/01 ' + field)
Then you can do comparisions and extract specific parts (hours, minutes
etc).
Chris
Jim_OLAP wrote:
> Jim,
> I would try an Instr() function to find the position of the ":" and
> then split the hours from the minutes (6) (30) with a mid() function;
> then sort on the two values. But I usually find the hard way -- I'm
> sure there is an easier way.
> :-)
> HTH
> Jim
>
> "JIM.H." wrote:
> > Hello
> > I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to
> > convert these string to time with the same format (HH:MM) so that I
> > can sort by time. How should I do this?
> > Thanks,
> > Jim.
> >
> >

Q: time conversion

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.You may find these articles helpful:
http://www.sommarskog.se/arrays-in-sql.html
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
--
Keith
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||Some examples:
select * from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by cast(timecol as datetime)
order by case when len(timecol) < 5 then '0' + timecol else timecol end
select cast(timecol as datetime) as newcol from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by newcol
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||or instead of
order by case when len(timecol) < 5 then '0' + timecol else timecol end
order by right('0' + timecol,5)
"Scott Morris" wrote:
> Some examples:
> select * from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by cast(timecol as datetime)
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> select cast(timecol as datetime) as newcol from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by newcol
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > Hello
> > I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> > these string to time with the same format (HH:MM) so that I can sort by
> time.
> > How should I do this?
> > Thanks,
> > Jim.
> >
>
>|||Good one. The left() function came to mind at first but I couldn't think of
a way to use it. I was looking at the wrong end!
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:29C1C2D5-CB91-4102-85BC-07A7F18146E5@.microsoft.com...
> or instead of
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> order by right('0' + timecol,5)
>
> "Scott Morris" wrote:
> > Some examples:
> >
> > select * from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by cast(timecol as datetime)
> > order by case when len(timecol) < 5 then '0' + timecol else timecol end
> >
> > select cast(timecol as datetime) as newcol from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by newcol
> >
> >
> > "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> > news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > > Hello
> > > I have string 6:10, 11:30, 23:10, ., in one of column. I need to
convert
> > > these string to time with the same format (HH:MM) so that I can sort
by
> > time.
> > > How should I do this?
> > > Thanks,
> > > Jim.
> > >
> >
> >
> >

Q: sum of defined field

Hello,
I created a column and create a field: myField that does some calculation. I
was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
not sum details. How can I sum it?
Thanks,
Jim.Not sure what you mean by "does not sum details" but you will have to
duplicate at the group level any calculations you perform at the detail
level. For example, if at the detail level you have
"=ReportItems!myField.Vlaue*2" then at the group level, you would need
"=Sum(ReportItems!myField.Vlaue)*2"
"JIM.H." wrote:
> Hello,
> I created a column and create a field: myField that does some calculation. I
> was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> not sum details. How can I sum it?
> Thanks,
> Jim.
>|||I have this in one of the columns at the second group level. I named the
field DepTotal
=ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
not have problem at this level, I get my number.
I added this in the first group level in the same column.
=Sum(ReportItems!DepTotal.Value)
It does not compile and it says aggregate function can be used only on
reports items contained in page headers and footers. This column has value in
only second group level, there is no detail data, might it be the problem?
"CGW" wrote:
> Not sure what you mean by "does not sum details" but you will have to
> duplicate at the group level any calculations you perform at the detail
> level. For example, if at the detail level you have
> "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> "=Sum(ReportItems!myField.Vlaue)*2"
> "JIM.H." wrote:
> > Hello,
> > I created a column and create a field: myField that does some calculation. I
> > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > not sum details. How can I sum it?
> > Thanks,
> > Jim.
> >|||When you say you named the field Dep Total, do you mean you named the textbox
that?
I believe you'll get the figure you're wanting for the second group level if
you use
=Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
ReportItems!Dep3.Value), or, if the groups are different from what I
understand them to be...
=Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
Sum(ReportItems!Dep3.Value),
"JIM.H." wrote:
> I have this in one of the columns at the second group level. I named the
> field DepTotal
> =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> not have problem at this level, I get my number.
> I added this in the first group level in the same column.
> =Sum(ReportItems!DepTotal.Value)
> It does not compile and it says aggregate function can be used only on
> reports items contained in page headers and footers. This column has value in
> only second group level, there is no detail data, might it be the problem?
>
> "CGW" wrote:
> > Not sure what you mean by "does not sum details" but you will have to
> > duplicate at the group level any calculations you perform at the detail
> > level. For example, if at the detail level you have
> > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > "=Sum(ReportItems!myField.Vlaue)*2"
> >
> > "JIM.H." wrote:
> >
> > > Hello,
> > > I created a column and create a field: myField that does some calculation. I
> > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > not sum details. How can I sum it?
> > > Thanks,
> > > Jim.
> > >|||yes, when I go to properties of textbox I see DepTotal and thsi is group
level 2. No in group level 1, I need to sum all group level 2 DepTotal
values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
level 1 too. I still get the same message for Dep1,2,3.
"CGW" wrote:
> When you say you named the field Dep Total, do you mean you named the textbox
> that?
> I believe you'll get the figure you're wanting for the second group level if
> you use
> =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> ReportItems!Dep3.Value), or, if the groups are different from what I
> understand them to be...
> =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> Sum(ReportItems!Dep3.Value),
>
> "JIM.H." wrote:
> > I have this in one of the columns at the second group level. I named the
> > field DepTotal
> > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > not have problem at this level, I get my number.
> >
> > I added this in the first group level in the same column.
> > =Sum(ReportItems!DepTotal.Value)
> >
> > It does not compile and it says aggregate function can be used only on
> > reports items contained in page headers and footers. This column has value in
> > only second group level, there is no detail data, might it be the problem?
> >
> >
> >
> > "CGW" wrote:
> >
> > > Not sure what you mean by "does not sum details" but you will have to
> > > duplicate at the group level any calculations you perform at the detail
> > > level. For example, if at the detail level you have
> > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > "=Sum(ReportItems!myField.Vlaue)*2"
> > >
> > > "JIM.H." wrote:
> > >
> > > > Hello,
> > > > I created a column and create a field: myField that does some calculation. I
> > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > not sum details. How can I sum it?
> > > > Thanks,
> > > > Jim.
> > > >|||The same sum(whatever) in each group should yield sums appropriate for that
group. If you get the same total in each group, then the groups are
identical. You might check your group definitions in the properities of the
table to make sure you're grouping distinctly.
"JIM.H." wrote:
> yes, when I go to properties of textbox I see DepTotal and thsi is group
> level 2. No in group level 1, I need to sum all group level 2 DepTotal
> values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
> ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
> level 1 too. I still get the same message for Dep1,2,3.
>
> "CGW" wrote:
> > When you say you named the field Dep Total, do you mean you named the textbox
> > that?
> >
> > I believe you'll get the figure you're wanting for the second group level if
> > you use
> >
> > =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> > ReportItems!Dep3.Value), or, if the groups are different from what I
> > understand them to be...
> > =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> > Sum(ReportItems!Dep3.Value),
> >
> >
> > "JIM.H." wrote:
> >
> > > I have this in one of the columns at the second group level. I named the
> > > field DepTotal
> > > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > > not have problem at this level, I get my number.
> > >
> > > I added this in the first group level in the same column.
> > > =Sum(ReportItems!DepTotal.Value)
> > >
> > > It does not compile and it says aggregate function can be used only on
> > > reports items contained in page headers and footers. This column has value in
> > > only second group level, there is no detail data, might it be the problem?
> > >
> > >
> > >
> > > "CGW" wrote:
> > >
> > > > Not sure what you mean by "does not sum details" but you will have to
> > > > duplicate at the group level any calculations you perform at the detail
> > > > level. For example, if at the detail level you have
> > > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > > "=Sum(ReportItems!myField.Vlaue)*2"
> > > >
> > > > "JIM.H." wrote:
> > > >
> > > > > Hello,
> > > > > I created a column and create a field: myField that does some calculation. I
> > > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > > not sum details. How can I sum it?
> > > > > Thanks,
> > > > > Jim.
> > > > >

Q: subtotal of rows in matrix

Hello,
I have a matrix report, how can I sum rows under a column. It seems add
subtotal works for summing of columns not rows.
Thanks,Nver mind. Found it.
"JIM.H." wrote:
> Hello,
> I have a matrix report, how can I sum rows under a column. It seems add
> subtotal works for summing of columns not rows.
> Thanks,
>|||Hey
I've been having the same problem, only with both rows and columns.
The subtotal for the row only grabs the first column's value and th
column subtotal only grabs the first row's value. Could you pleas
explain how you got the subtotals to work? If you can't at least pos
the solution you figured out
Thanks