query question

I saw a query in which there was a expression
SELECT
case when credit_flag&1 = 1 then 1 else 0 end as credit_default_flag1,
case when credit_flag&2 = 2 then 1 else 0 end as credit_default_flag2,
case when credit_flag&4 = 4 then 1 else 0 end as credit_default_flag3
FROM ...
What happens when we do column & 1, column & 2
THanks
Sanjay& is the bit-wise AND operator in T-SQL. So credit_flag &
1 will return 1 on the first bit only when the first bit
from the right (of credit_flag) is set to 1. And
credit_flag & 4 will return 1 on the third bit (thus
decimal 4) only when the third bit from the right (of
credit_flag) is set to 1.
Linchi
>--Original Message--
>I saw a query in which there was a expression
>SELECT
>case when credit_flag&1 = 1 then 1 else 0 end as
credit_default_flag1,
>case when credit_flag&2 = 2 then 1 else 0 end as
credit_default_flag2,
>case when credit_flag&4 = 4 then 1 else 0 end as
credit_default_flag3
>FROM ...
>
>What happens when we do column & 1, column & 2
>THanks
>Sanjay
>.
>|||HI Linch
Do you have some white paper or document which would explain more about these bit-wise operation
I think you explained what the result would be but i am not clear how bit-wise operations work at first plac
Thanks|||The bit-wise AND operator (also OR and XOR) is supported
in most, if not all, programming languages. You can pick
up any programming tutorial book and find information on
the bit-wise operations. You can also find the info in the
SQL Server Books Online.
Linchi
>--Original Message--
>HI Linchi
>Do you have some white paper or document which would
explain more about these bit-wise operations
>I think you explained what the result would be but i am
not clear how bit-wise operations work at first place
>Thanks
>.
>

query question

I saw a query in which there was a expression
SELECT
case when credit_flag&1 = 1 then 1 else 0 end as credit_default_flag1,
case when credit_flag&2 = 2 then 1 else 0 end as credit_default_flag2,
case when credit_flag&4 = 4 then 1 else 0 end as credit_default_flag3
FROM ...
What happens when we do column & 1, column & 2
THanks
Sanjay& is the bit-wise AND operator in T-SQL. So credit_flag &
1 will return 1 on the first bit only when the first bit
from the right (of credit_flag) is set to 1. And
credit_flag & 4 will return 1 on the third bit (thus
decimal 4) only when the third bit from the right (of
credit_flag) is set to 1.
Linchi

quote:

---

>--Original Message--
>I saw a query in which there was a expression
>SELECT
>case when credit_flag&1 = 1 then 1 else 0 end as

---

credit_default_flag1,

quote:

---

ed">
>case when credit_flag&2 = 2 then 1 else 0 end as

---

credit_default_flag2,

quote:

---

ed">
>case when credit_flag&4 = 4 then 1 else 0 end as

---

credit_default_flag3

quote:

---

d">
>FROM ...
>
>What happens when we do column & 1, column & 2
>THanks
>Sanjay
>.
>

---

|||HI Linchi
Do you have some white paper or document which would explain more about thes
e bit-wise operations
I think you explained what the result would be but i am not clear how bit-wi
se operations work at first place
Thanks|||The bit-wise AND operator (also OR and XOR) is supported
in most, if not all, programming languages. You can pick
up any programming tutorial book and find information on
the bit-wise operations. You can also find the info in the
SQL Server Books Online.
Linchi

quote:

---

>--Original Message--
>HI Linchi
>Do you have some white paper or document which would

---

explain more about these bit-wise operations

quote:

---

>I think you explained what the result would be but i am

---

not clear how bit-wise operations work at first place

quote:

---

>Thanks
>.
>

---

sql

Query Query

Originally I thought this was going to be a DISTINCT issue, but I don't
think that is the case.
I have 3 tables..
Apps
ID AppName
1 ApplicationOne
Tests
ID TestCase
1 Run Overnight
TestAttributes
ID AppID TestID Type OS
ProjectID
1 1 1 Stress Windows XP
1
2 1 1 Quick Check Windows XP 1
3 1 1 Larger Check Windows XP 1
If I do..
SELECT DISTINCT Tests.TestCase, Apps.AppName
FROM Tests
LEFT OUTER JOIN Tests ON TestAttributes.TestID = Tests.ID
LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
WHERE (TestAttributes.ProjectID = '1')
ORDER BY TestCase
I will get
Run Overnight ApplicationOne
This is what i need to display in a grid, however I will need to know all 3
IDs for the 3 seperate types.
Is it possible in a query to get something like
Run Overnight ApplicationOne
1
2
3
Where the 1,2,3 would be the 3 IDs from the TestAttributes table.
With more data it would be seomthing like
Run Overnight ApplicationOne
1
2
3
Run Daytime ApplicaitonTwo
4
5
Run Whenever ApplicationThree
6
7
8
9
Basically I want to display one distinct test case, but still get all the
IDs for the no distinct ones. I could obviously do this client side, but
the performance may not be the best.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
There is no such thig as a "magical universal" id number in an RDBMS.
Did you mean something like this?
CREATE TABLE Applications
(app_id INTEGER NOT NULL PRIMARY KEY,
app_name CHAR(20) NOT NULL);
In a relational model, an entity is the sum of its attributes. So why
did you split the attributes into another table? That violates rules
about mixing data and metadata
Why did you use multiple names for the same data element
(TestCases.AppID = Apps.ID)? See how vague that makes the sentence " I
will need to know all 3 IDs for the 3 separate types." Which id do you
mean? How can you build a data dictioanry with this mess?
CREATE TABLE Tests
(test_nbr INTEGER NOT NULL PRIMARY KEY,
app_id INTEGER NOT NULL
REFERENCES Applications(app_id),
test_type CHAR(10) NOT NULL,
os CHAR(10) NOT NULL,
project_id INTEGER NOT NULL);
Ther is no table named TestCases, so your sample query makees no sense.
Can we try again?|||In a stored procedure, you can browse your tests case with a CURSOR and for
each one store several lines in a temporary table :
- the test case & app.name
- one line for each test attribute id
Build the temp table by an IDENTITY column, and you'll can do a
select ... order by Id_field.
JN.
"Lucas Graf" <lgraf2000@.comcast.net> a crit dans le message de news:
OFSIfSCIFHA.3428@.TK2MSFTNGP10.phx.gbl...
> Originally I thought this was going to be a DISTINCT issue, but I don't
> think that is the case.
> I have 3 tables..
> Apps
> ID AppName
> 1 ApplicationOne
> Tests
> ID TestCase
> 1 Run Overnight
> TestAttributes
> ID AppID TestID Type OS ProjectID
> 1 1 1 Stress Windows XP 1
> 2 1 1 Quick Check Windows XP 1
> 3 1 1 Larger Check Windows XP
> 1
> If I do..
> SELECT DISTINCT Tests.TestCase, Apps.AppName
> FROM Tests
> LEFT OUTER JOIN Tests ON TestAttributes.TestID = Tests.ID
> LEFT OUTER JOIN Apps ON TestCases.AppID = Apps.ID
> WHERE (TestAttributes.ProjectID = '1')
> ORDER BY TestCase
> I will get
> Run Overnight ApplicationOne
> This is what i need to display in a grid, however I will need to know all
> 3 IDs for the 3 seperate types.
> Is it possible in a query to get something like
> Run Overnight ApplicationOne
> 1
> 2
> 3
> Where the 1,2,3 would be the 3 IDs from the TestAttributes table.
> With more data it would be seomthing like
> Run Overnight ApplicationOne
> 1
> 2
> 3
> Run Daytime ApplicaitonTwo
> 4
> 5
> Run Whenever ApplicationThree
> 6
> 7
> 8
> 9
> Basically I want to display one distinct test case, but still get all the
> IDs for the no distinct ones. I could obviously do this client side, but
> the performance may not be the best.
>|||More clear depiction of what I am asking.
CREATE TABLE Apps(
app_id smallint
IDENTITY(1,1)
PRIMARY KEY,
app_name varchar(50) NOT NULL
)
CREATE TABLE Tests(
test_id smallint
IDENTITY(1,1)
PRIMARY KEY,
testcase varchar(50) NOT NULL
)
CREATE TABLE TestAttributes(
attribute_id smallint
IDENTITY(1,1)
PRIMARY KEY,
attr_appid smallint NOT NULL,
attr_testid smallint NOT NULL,
attr_type varchar(50) NOT NULL,
attr_OS varchar(50) NOT NULL,
attr_projid smallint NOT NULL
)
go
INSERT Apps values('Application One')
INSERT Tests values('Run Overnight')
INSERT TestAttributes values('1','1',' Stress','Windows XP','1')
INSERT TestAttributes values('1','1','Quick Check','Windows XP','1')
INSERT TestAttributes values('1','1','Longer Check','Windows XP','1')
Using this query
SELECT DISTINCT Tests.testcase, Apps.app_name
FROM TestAttributes
LEFT OUTER JOIN Tests ON TestAttributes.attr_testid = Tests.test_id
LEFT OUTER JOIN Apps ON TestAttributes.attr_appid = Apps.app_id
WHERE (TestAttributes.attr_projid = '1')
ORDER BY TestCase
Gives me
testcase app_name
Run Overnight Application One
But i will also need to know all 3 of the attribute_id's that correspond to
that test case
Using this query
SELECT DISTINCT Tests.testcase, Apps.app_name,attribute_id
FROM TestAttributes
LEFT OUTER JOIN Tests ON TestAttributes.attr_testid = Tests.test_id
LEFT OUTER JOIN Apps ON TestAttributes.attr_appid = Apps.app_id
WHERE (TestAttributes.attr_projid = '1')
ORDER BY TestCase
I get
testcase app_name attribute_id
Run Overnight Application One 1
Run Overnight Application One 2
Run Overnight Application One 3
I don't want to show alll 3, however I need to know all 3 attribute_id's
So ideallly would like a query to give me something like
testcase app_name
Run Overnight Application One
attribute_id
1
attribute_id
2
attribute_id
3

query problem

I have a table (Product) and a Column (ReleaseDate)

and trying to run this query

select Product =
CASE ReleaseDate

WHEN datediff(d,ReleaseDate,getutcdate()) < 30 THEN 'New'
WHEN datediff(d,ReleaseDate,getutcdate()) < 90 THEN 'Recent'
ELSE 'Normal' END

and get this error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '<'.

any ideas

cheers!!!

Change to:

select Product=

CASEWHENdatediff(d,ReleaseDate,getutcdate())< 30THEN'New'

WHENdatediff(d,ReleaseDate,getutcdate())< 90THEN'Recent'

ELSE'Normal'END

FROM product

|||Thanks for that.. i got it sorted

Query only Dimension Data

We Have a case where we have to create a report only with the dimension data from SSAS.
Is there any way in doing it in Excel 2007.

I see a pivot table option which says " Display item labels when no fields are in the value area"
but this is disabled. Can anyone point me to a way of doin this or enabling this.

Thankyou
VidyaAre you creating the report through excel or programmatically? If programmatically $Dimension exposes the dimension as a cube and you can use it as you would any cube...