Can we implement Pivot Tables using MSRS?

Can we implement Pivot Tables using Reporting Services 2005? I need to
allow user to select the pivot column at runtime. I mean dynamically
changing the criteria for pivot tables after report is rendered.
Thanks,
YogeshHi,
You can implement pivoting using matrix.
Amarnath.
"Yogi" wrote:
> Can we implement Pivot Tables using Reporting Services 2005? I need to
> allow user to select the pivot column at runtime. I mean dynamically
> changing the criteria for pivot tables after report is rendered.
> Thanks,
> Yogesh
>

Can we do a double Cursor?

Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:

> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:

>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:

> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>

can we combine these 3 statements into one single query

SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp

SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.idSELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
UNION ALL
SELECT 2 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
UNION ALL
SELECT 3 as id, (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id|||The above query doesn't work for me and infact i want to get the percentage in a single query..
Thanks|||I'm "winging" this one wildly, but could you use:SELECT
CAST(Sum(CASE WHEN name IS NOT NULL
AND name <> '' THEN 1 END) AS FLOAT) / Count(*)
FROM dbo.empThis divides the number of names with value by the total to get the percentage of usable names.

-PatP|||How about

SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX|||SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM (
SELECT COUNT(name) as count1
INTO #temp1
FROM emp
JOIN
SELECT COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
........................

I could not execute the above query, can we write the query like that?
please correct me if i am wrong?|||Don't use Name <> NULL

No value will ever match that:

MyField > NULL will always be false
MyField < NULL will always be false
MyField <> NULL will always be false
MyField = NULL will always be false
and so on for all applicable operators...

try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.|||try using:

NULLIF(Name, TRIM(Name)) IS NOT NULL

to catch fields containing only spaces.

My bad. Use this instead:

NULLIF('', TRIM(Name)) IS NOT NULL|||Just curious, but didn't my suggestion work? What results did it produce?

-PatP|||Replace:
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
By:
WHERE LTRIM(ISNULL(name,'')) <> '' ) AS XXX

yabu.

Can Users choose Rows & columns for a matrix?

Hi,
Is it possible to create a report which allows users to select the fields
for the matrix in a report. To explain in detail, can we allow the users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total sales(
data cell) by month (X axis) and by Company( Y axis). Can we have some option
so that if the users select Year as the X axis and SalesPerson as the Y axis
then they can view the same report but by SalesPerson and Year instead od
Month and Company?
Any help is highly appreciated.
Thanks
--
pmudYes, this is possible by using dynamic field references for the grouping
expression and the matrix group header textbox expression. E.g.
=Fields(Parameters!RowGroup.Value).Value
Note: the actual field name in the expression above will be determined by
the parameter's value at runtime. You can use this for the row and the
column grouping of the matrix.
A RS 2005 sample report (which also includes InteractiveSort on the dynamic
matrix row groups) is attached. Note: you cannot load this sample in the RS
2000 report designer.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> Hi,
> Is it possible to create a report which allows users to select the fields
> for the matrix in a report. To explain in detail, can we allow the users
> to
> select the X and Y axis for a matrix in a report?
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Company( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and SalesPerson as the Y
> axis
> then they can view the same report but by SalesPerson and Year instead od
> Month and Company?
> Any help is highly appreciated.
> Thanks
> --
> pmud
=====================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="northwind">
<DataSourceReference>northwind</DataSourceReference>
<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="RowGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ProductName</Value>
</Values>
</DefaultValue>
<Prompt>RowGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ProductName</Value>
<Label>By Product Name</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
<ParameterValue>
<Value>CategoryID</Value>
<Label>By Category ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ColumnGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ReorderLevel</Value>
</Values>
</DefaultValue>
<Prompt>ColumnGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ReorderLevel</Value>
<Label>By Reorder Level</Label>
</ParameterValue>
<ParameterValue>
<Value>UnitsInStock</Value>
<Label>By Stock</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Left>0.125in</Left>
<Top>0.375in</Top>
<ZIndex>2</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.125in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
</Textbox>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>2.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!RowGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ColumnGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.875in</Top>
<Width>3.125in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
</UserSort>
<Value>Sort rows</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products</CommandText>
<DataSourceName>northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>3.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||Hi Robert,
I dont have RS 2005. So the way you told ( by having
=Fields(Parameters!RowGroup.Value)) will work for RS 2000 too?
Thanks
--
pmud
"Robert Bruckner [MSFT]" wrote:
> Yes, this is possible by using dynamic field references for the grouping
> expression and the matrix group header textbox expression. E.g.
> =Fields(Parameters!RowGroup.Value).Value
> Note: the actual field name in the expression above will be determined by
> the parameter's value at runtime. You can use this for the row and the
> column grouping of the matrix.
> A RS 2005 sample report (which also includes InteractiveSort on the dynamic
> matrix row groups) is attached. Note: you cannot load this sample in the RS
> 2000 report designer.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> > Hi,
> >
> > Is it possible to create a report which allows users to select the fields
> > for the matrix in a report. To explain in detail, can we allow the users
> > to
> > select the X and Y axis for a matrix in a report?
> >
> > For ex., there is a report containing a matrix which shows the total
> > sales(
> > data cell) by month (X axis) and by Company( Y axis). Can we have some
> > option
> > so that if the users select Year as the X axis and SalesPerson as the Y
> > axis
> > then they can view the same report but by SalesPerson and Year instead od
> > Month and Company?
> >
> > Any help is highly appreciated.
> >
> > Thanks
> > --
> > pmud
>
> =====================================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="northwind">
> <DataSourceReference>northwind</DataSourceReference>
> <rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <ReportParameters>
> <ReportParameter Name="RowGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ProductName</Value>
> </Values>
> </DefaultValue>
> <Prompt>RowGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ProductName</Value>
> <Label>By Product Name</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>CategoryID</Value>
> <Label>By Category ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="ColumnGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ReorderLevel</Value>
> </Values>
> </DefaultValue>
> <Prompt>ColumnGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ReorderLevel</Value>
> <Label>By Reorder Level</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>UnitsInStock</Value>
> <Label>By Stock</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ReportItems>
> <Textbox Name="textbox3">
> <Left>0.125in</Left>
> <Top>0.375in</Top>
> <ZIndex>2</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
> </Textbox>
> <Textbox Name="textbox1">
> <Left>0.125in</Left>
> <Top>0.125in</Top>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
> </Textbox>
> <Matrix Name="matrix1">
> <MatrixColumns>
> <MatrixColumn>
> <Width>1in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <Width>2.125in</Width>
> <DynamicRows>
> <ReportItems>
> <Textbox Name="CategoryID">
> <rd:DefaultName>CategoryID</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!RowGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Grouping Name="matrix1_RowGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicRows>
> </RowGrouping>
> </RowGroupings>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <ReportItems>
> <Textbox Name="ReorderLevel">
> <rd:DefaultName>ReorderLevel</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="matrix1_ColumnGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.875in</Top>
> <Width>3.125in</Width>
> <Corner>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <UserSort>
> <SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
> <SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
> </UserSort>
> <Value>Sort rows</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <MatrixRows>
> <MatrixRow>
> <Height>0.25in</Height>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <rd:DefaultName>ProductID</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!ProductID.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> </MatrixRow>
> </MatrixRows>
> </Matrix>
> </ReportItems>
> <Height>2in</Height>
> </Body>
> <rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>select * from products</CommandText>
> <DataSourceName>northwind</DataSourceName>
> </Query>
> <Fields>
> <Field Name="ProductID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>ProductID</DataField>
> </Field>
> <Field Name="ProductName">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>ProductName</DataField>
> </Field>
> <Field Name="SupplierID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>SupplierID</DataField>
> </Field>
> <Field Name="CategoryID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>CategoryID</DataField>
> </Field>
> <Field Name="QuantityPerUnit">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>QuantityPerUnit</DataField>
> </Field>
> <Field Name="UnitPrice">
> <rd:TypeName>System.Decimal</rd:TypeName>
> <DataField>UnitPrice</DataField>
> </Field>
> <Field Name="UnitsInStock">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsInStock</DataField>
> </Field>
> <Field Name="UnitsOnOrder">
> <rd:TypeName>System.Int16</rd:TypeName>|||Robert -
I have been trying something very similar to this except that it takes
it one step further. My matrix has multiple RowGroups and multiple
ColumnGroups. I'd like the user to be able to turn some off, but I
cannot keep the RowGroup fields from always appearing in the matrix.
I use expressions to turn the RowGrouping on/off. When the RowGroup is
not to appear, the expression evaluates to "".
I also use expressions to set the RowGroup visibility to false.
Is there any way to do this? Maybe I am doing something wrong?
Thanks.

Can user view objects they only have select permission on?

I have a user that belongs to a role. This role only has select permissions
on 10 views. When I log in as this user via Management Studio I cannot see
the views however I can execute queries against them.
On another server that I did not setup, that I'm supposed to be mimicking
the same security, this same user can see the views.
Any ideas what the difference is?
Thanks!Here's the commands that I'm executing in order:
CREATE ROLE [Customers_ROLE] Authorization dbo
CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] Deny
View
Definition to [Customers_Role]
exec sp_adduser 'phenson', 'phenson', [Customers_Role]
GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
When I log in as phenson I do not see PT_View but I can query on it. I need
to be able to see it.
"SpankyATL" wrote:

> I have a user that belongs to a role. This role only has select permissio
ns
> on 10 views. When I log in as this user via Management Studio I cannot se
e
> the views however I can execute queries against them.
> On another server that I did not setup, that I'm supposed to be mimicking
> the same security, this same user can see the views.
> Any ideas what the difference is?
> Thanks!|||I thought I'd answer my own question for those of you who come across this
some day. I need to remove the "Deny View Definition" portion and that took
care of it. The user was able to see that view and execute it but could not
see the script.
"SpankyATL" wrote:
[vbcol=seagreen]
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I ne
ed
> to be able to see it.
> "SpankyATL" wrote:
>|||SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I
> need to be able to see it.
Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
member of?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was working from a script that was given to me. The person who developed
it mistakenly thought deny view would only deny the user from viewing the
source code.
"Erland Sommarskog" wrote:

> SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
> member of?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Can use some help with a select query

Hi,

I have this tables (view attachment) wich i want to query with a selct statement I'll tried al sort of things but it didn't happend!

I have a machine which has one cabinet in this cabinet there could be 2 screens , I want the details of both screens of a certain machine in a single line
Hope somesone understand what i want!

Cheers WimmoAs a matter of fact, no I don't understand.
It sounds like you are describing a crosstab query, but your schema allows only one screen per cabinet.
It would be best if you posted the query you have tried, and let us know how the results differed from what you wanted.|||Untested:
select *
from tblMachine m
,tblCabinet c
,tblScreens s1
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number|||Untested:
select *
from tblMachine m
,tblCabinet c
,tblScreens s1
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Numberi would be extremely leery of mixing "comma list" syntax with JOIN syntax

in mysql 5, for instance, JOINs take precedence (similar to the way ANDs take precedence over ORs) and so the following will produce an error --tblScreens s1 left join tblScreens s2
on c.Screen2=s2.Part_Numbercan you see why?|||Yes the left join should be to tblCabinet i.e.
select ...
from tblMachine m
, tblScreens s1
, tblCabinet c
left join tblScreens s2 on c.Screen2=s2.Part_Number
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number

And as you say this is just as bad as mixing ANDs and ORs without brackets
Thanks for highlighting it
So here it is without mixing syntax
select ...
from tblMachine m
,tblCabinet c
,tblScreens s1
,tblScreens s2
where m.Cabinet=c.id
and c.Screen1=s1.Part_Number
and c.Screen2*=s2.Part_Number

select ...
from tblMachine m
join tblCabinet c on m.Cabinet=c.id
join tblScreens s1 on c.Screen1=s1.Part_Number
left join tblScreens s2 on c.Screen2=s2.Part_Number|||Hi All, thanx for your reply's.
I tried the query's but none did actually worked, they generated no errors but it returned zero records where ther should be one.

@.Blindman, I tried this query for getting the info of 1 screen which is already hard to get but there could be 2 or none in a cabinet

SELECT tblCabinet.Screen1, tblCabinet.Screen2, tblScreenTypes.ScreenType, tblBrands.BrandName, tblCommTypes.CommType, tblAdaptor.Adaptor
FROM tblMachine INNER JOIN
tblCabinet ON tblMachine.Cabinet = tblCabinet.ID INNER JOIN
tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number INNER JOIN
tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID INNER JOIN
tblBrands ON tblScreens.Brand = tblBrands.ID INNER JOIN
tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID INNER JOIN
tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
where tblMachine.Part_NUmber = 'Value'

I'll tried several changes in the joins but none returned an error but none returned values.|||If your query unexpectedly returned zero rows, then run this and see how many rows it returns:SELECT count(*)
FROM tblMachine
-- INNER JOIN tblCabinet ON tblMachine.Cabinet = tblCabinet.ID
-- INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number
-- INNER JOIN tblScreenTypes ON tblScreens.ScreenType = tblScreenTypes.ID
-- INNER JOIN tblBrands ON tblScreens.Brand = tblBrands.ID
-- INNER JOIN tblAdaptor ON tblScreens.Adaptor = tblAdaptor.ID
-- INNER JOIN tblCommTypes ON tblScreens.CommType = tblCommTypes.ID
where tblMachine.Part_NUmber = 'Value'
Then, uncomment one line at a time until your query again returns zero rows, and that will tell you where the problem join is.|||I'll did what you suggested and at this line

INNER JOIN tblScreens ON tblCabinet.Screen1 = tblScreens.Part_Number

it returns 0 but i don´t understand why, there are values. Could it have something todo with the relation?

Thanks to your proposed strategy i found the problem, seems that there was a relation to an old table on screen 1 so deleting that table did solve my problem.

Thanks for all your help and probably expensive time.

Wim

can u help with my select ?

Hello All,

I have a simple table with 2 columns.

datetime int
27.05.04 1
29.05.04 2
31.05.04 5

and i need to get a view that looks like this below :

27.05.04 1 1
29.05.04 2 3
31.05.04 5 8

The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(

can anybody help ?

thx in advance
Krzysiek"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq48$25ka$1@.news2.ipartners.pl...
> Hello All,
> I have a simple table with 2 columns.
> datetime int
> 27.05.04 1
> 29.05.04 2
> 31.05.04 5
> and i need to get a view that looks like this below :
> 27.05.04 1 1
> 29.05.04 2 3
> 31.05.04 5 8
> The third column shows a sum of numbers that were display before present
one
> ..
> I was trying build stored proc however I'm not so familiar with SQL so I
> failed.. :-(
> can anybody help ?
> thx in advance
> Krzysiek

It looks like you got an answer in another group - please do not post to
multiple groups independently.

Simon

Can this query be simpilified

Hi,
Can this query be simpilified for performance
If (select count(id)
From dbo.TDetails
where
ID = 554 AND Name = 'SUN'
HAVING
Sum(Quantity_Shipped + Quantity_Credited) <> SUM(QUANTITY)) = 0 Then ......
.
ThanksChris,
I think we are missing a "group by" clause.
AMB
"Chris" wrote:

> Hi,
> Can this query be simpilified for performance
>
> If (select count(id)
> From dbo.TDetails
> where
> ID = 554 AND Name = 'SUN'
> HAVING
> Sum(Quantity_Shipped + Quantity_Credited) <> SUM(QUANTITY)) = 0 Then ....
..
> Thanks|||> I think we are missing a "group by" clause.
My mistake. Can you try:
if (
select
isnull(sum(Quantity_Shipped + Quantity_Credited), 0) -
isnull(sum(QUANTITY), 0)
from
dbo.TDetails
where
[ID] = 554 AND [Name] = 'SUN'
) != 0
...
AMB
"Alejandro Mesa" wrote:
> Chris,
> I think we are missing a "group by" clause.
>
> AMB
> "Chris" wrote:
>|||Hi Chris,
I would not expect any performance gain by rewriting the statement.
Personally, I would prefer the following syntax. An "Else" clause might
become faster this way. The "Then" clause will not be faster.
If NOT EXISTS (
SELECT 1
FROM dbo.TDetails
WHERE ID = 554
AND Name = 'SUN'
HAVING SUM(Quantity_Shipped + Quantity_Credited) <> SUM(Quantity)
)
Begin
.
End
Make sure you have an index on (ID, Name).
Gert-Jan
Chris wrote:
> Hi,
> Can this query be simpilified for performance
> If (select count(id)
> From dbo.TDetails
> where
> ID = 554 AND Name = 'SUN'
> HAVING
> Sum(Quantity_Shipped + Quantity_Credited) <> SUM(QUANTITY)) = 0 Then ....
..
> Thanks

can this query be rewritten?

update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = (select max(MCARA_RISK_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM),
MAX_MCARD_RISK_ADJ_RTE = (select max(MCARD_RISK_ADJ_RTE) from XTAW0200_MEM_DTL A
where A.HIC_NUM = CMS_RISK_SCORES.HIC_NUM)

Can I get the same results with one join instead of two without creating a temporary table?

Thanks much.

:confused:update CMS_RISK_SCORES
set MAX_MCARA_RISK_RTE = MaxValues.MCARA_RISK_RTE,
MAX_MCARD_RISK_ADJ_RTE = MaxValues.MCARD_RISK_ADJ_RTE
from CMS_RISK_SCORES
inner join --MaxValues
(select HIC_NUM,
max(MCARA_RISK_RTE) as MCARA_RISK_RTE,
max(MCARD_RISK_ADJ_RTE) as MCARD_RISK_ADJ_RTE
from XTAW0200_MEM_DTL
group by HIC_NUM) MaxValues
on CMS_RISK_SCORES.HIC_NUM = MaxValues.HIC_NUM|||Thank You.|||While there is a difference in syntax, I don't think there will be any significant difference in execution plan between the two statments. SQL Server is very good at combining redundant queries like this.

-PatPsql

Can SQL server return text in column instead of row?

Hi
Consider the query
"SELECT names FROM Table1"
It would return one column with some rows.
Abc
Xyz
Pqr
Mno
I want SQL server to return is in columns like
Abc Xyz Pqr Mno
Thank YouChakravarti Mukesh wrote:
> Hi
> Consider the query
> "SELECT names FROM Table1"
> It would return one column with some rows.
> Abc
> Xyz
> Pqr
> Mno
> I want SQL server to return is in columns like
> Abc Xyz Pqr Mno
> Thank You
That's just formatting a list, which something controlled by your
client application, not by SQL Server. Take a look at the ADO GetString
method for example.
http://msdn.microsoft.com/library/d...
etstringmethod(recordset)ado.asp
David Portas
SQL Server MVP
--

can sql server allow to read record ramdomly?

anyone have the solution or work around?

my system need to select x% ramdom record from a batch of data from sql server for validation. is this posible?

any sql statement allow to select the record ramdomly? let say total record is around 1000, example record that i need to select is:-

record no 5,48,49,50,147,148,256,257,258,411,412,413,414,415,..... so and so

can i use cursor to move around the record to read it?

if we view on performance of the system. how can i make this at max speed? is that clone this table into local access database will make this more faster?

regards

terence chua

How do you intend to use this data ? If you won't display it in tabular form, you could read the entire DataTable, then use random number generation to access different rows in random order ?

|||

actually this is a system to receive customer feedback.

and now i want to basic on the total transaction to take out the % of transaction to collect customer contact information. then i will create a new contact list to allow the system to send out sms to customer to say thanks and request them to reply. just to make sure the customer did feedback to us but not our own staff key in the feedback.

this sms list will also be a report to show to top management. regarding how many sms we send out everyday.

regard

terence chua

|||

You can use TABLESAMPLE (nnn PERCENT) or TABLESAMPLE (nnn ROWS) in your SELECT statement. But be carefully - TABLESAMPLE extracts not exactly nnn percent or nnn rows, i.e. two executions of, for example, 'select * from Person.Contact tablesample (10 percent)' will have thow different rowcounts. See BOL for details.

WBR, Evergray -- Words mean nothing...|||

YOu could use something like this:

SELECT TOP 10 *

FROM

SomeTable

Order by NEWID()

HTH, jens Suessmeyer.

|||

Yes, this kind of query will return exactly specified number of records, but by cost of performance (in common case). Query with 'tablesample' will read random number of table pages (and, yeah, may even return 0 records or double expected count) while query with 'order by newid()' should scan entire table or index and compute newid() for each record to properly sort them and select top(n). The second type of queries may be better (in performance) only with highly selective (or covered) queries with appropriate indexes for support them (and, again, always more accurate :-)). Or, if table has an 'uniqueidentifier' or 'rowguid' column (indexed) - this is may be case, too.

If the main goal is performance of such a query (as I've understand from the first post), and the query is not very selective and not covered by any index, the choice is tablesampling. Inaccuracy of this clause may be eliminated by doing tablesampling twice (or more), for example:

select top 10 percent * from
(select * from sometable
tablesample (10 percent)
where x=y
union
select * from sometable
tablesample (10 percent)
where x=y
) a

will be more accurate with percentage than one sample. Anyway, you should compare performance of queries of both types to select the better one for your needs.

This data may be used to fill out temporary table (directly or by using table variable - the last will be better choice) to process it on server side (sms via Database Mail?). But, if you doing processing in an external application, sure you can (maybe even should) extract data from server in any store which is local for that application so server will be free from take a care of it.

Good luck!

WBR, Evergray
--
Words mean nothing...|||Hi Everygray,
what your mean is using more then 1 time tablesampling to make sure the record more accurate?
to make sure what i understand is correct in ur sample, "a" is temporary table? is the "tablesample(10 percent)" is the syntax for the query?
below is the code for single tablesampling?
--
select * from sometable
tablesample (10 percent)
where x=y
--
so let say my table name call "Answers" then my query should be like this?
select top 30 percent * from
(select * from Answers
tablesample (10 percent)
where date >= yesterday
union
select * from Answers
tablesample (10 percent)
where date >= yesterday
) a
now my system should able to take out the contact information and pass to external system to process and send out the sms. so the output should be in either .txt or .dat file or something else like .xml.
btw are you having any better idea or better way to done the job with sms the data directly out to my customer phone without export the data out? because now my company planing to corporate with a communication provided company to send out sms. so them may request the customer list in a txt, dat or xml file. tat's y i looking for the solution now before my management want it to be implement.
regards
terence chua
|||

Terence Chua wrote:

Hi Everygray,

what your mean is using more then 1 time tablesampling to make sure the record more accurate?

see below...

Terence Chua wrote:

to make sure what i understand is correct in ur sample, "a" is temporary table?

No, it's called 'derived table' (named subquery)

Terence Chua wrote:

is the "tablesample(10 percent)" is the syntax for the query?

below is the code for single tablesampling?
--
select * from sometable
tablesample (10 percent)
where x=y
--

Yes

Terence Chua wrote:

so let say my table name call "Answers" then my query should be like this?

select top 30 percent * from
(select * from Answers
tablesample (10 percent)
where date >= yesterday
union
select * from Answers
tablesample (10 percent)
where date >= yesterday
) a

Ok, maybe I've been not clear enough about sampling. Tablesample reads random number of PAGES, not ROWS. If you specify "tablesample (10 percent)" in your query, server will randomly select approximately 10 percent of pages on which table data is stored (let's say, 10 of 100 total) and return ALL rows from each selected page. If your table is big enough and rows are evenly distributed on data pages, resulting count of rows will be close to 10 percent. But, if, for example, your table is quite small (for example, 5 pages), 10% of total number of pages is zero, so query will result no rows at all. Or, if some of data pages in your table has small count of rows (e.g. 2-3), and some - big (e.g. 20-30), the resulting count will depend on which pages were selected (e.g. your query may return 3 rows or 300 on the same data). Or, if you specify "tablesample (10 rows)" for table which contains 300 rows at all, which are stored on 15 pages, the result again be 0 rows (10 rows from 300 are 3%, and 3% from 15 pages are 0 pages). So, if the same query executed several times against the same data returns very different results (e.g. 0, 30, 300, 100....) we may think about 'equalizing' these results. Your query above should return number of records closer to 6% of total rows than simple 'select * .. tablesample 6%'.

Terence Chua wrote:

now my system should able to take out the contact information and pass to external system to process and send out the sms. so the output should be in either .txt or .dat file or something else like .xml.

btw are you having any better idea or better way to done the job with sms the data directly out to my customer phone without export the data out? because now my company planing to corporate with a communication provided company to send out sms. so them may request the customer list in a txt, dat or xml file. tat's y i looking for the solution now before my management want it to be implement.

regards
terence chua

If number of messages to send is low, you may use sp_send_dbmail stored proc to send them through your smtp server (little emails to xxxxxxx@.mobile.operator.com or so). But this is not likely your case, because it is resource consuming task (all messages are stored in a queue on the server, mailing program executes here too), at least, you should't use the same SQL Server instance for it.

As for export - there are MANY different ways to export data in any format - using linked servers (in any external database or plaintext file), simply executing query with sqlcmd and storing results (maybe using FOR XML) in plaintext file, create webservice which will return such data in desired format to your partner and so on... Again, what the goal is? Which restrictions are? ;-)

WBR, Evergray
--
Words mean nothing...|||

Hi Evergray,

Thanks for your help, but when i try the sql statement in sql server Enterprise Manager, at the view. i get this error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'PERCENT'.

below is the code i test on hard code the date at 26th Jan 2006 and get the answer between a range.

SELECT TOP 30 PERCENT *
FROM (SELECT *
FROM Answers tablesample(10 PERCENT)
WHERE (iMinutes BETWEEN 570 AND 1020) AND (MONTH(dtAnswer) = 1) AND (YEAR(dtAnswer) = 2006) AND (iTemplateId = 1) AND (fDiscard = 0)
AND (fIncomplete = 0) AND (DAY(dtAnswer) = 26)
UNION
SELECT *
FROM Answers tablesample(10 PERCENT)
WHERE (iMinutes BETWEEN 570 AND 1020) AND (MONTH(dtAnswer) = 1) AND (YEAR(dtAnswer) = 2006) AND (iTemplateId = 1) AND (fDiscard = 0) AND
(fIncomplete = 0) AND (DAY(dtAnswer) = 26))

|||

The first, you should add an alias for your inner query, such as

select top 30 percent *
from (...) somealias

The second... the error message you refer to... Looks like you are using SQL Server 2000 or your database compatibility level is not 9.0, as required for tablesample to work... If this is the case, you should use Jens's suggestion because tablesampling is unavailable for you.

The third. If your Answers table has an index on dtAnswer column, you'd better use two DateTime constants and BETWEEN clause in you query rather than using these Day(), Year() etc., because your query doesn't benefit from such index, always doing fullscan of table.

WBR, Evergray
--
Words mean nothing....

Can SQL Server 2000's Select statement assign multi-variables from Table?

Can SQL Server 2000's Select statement assign multi-variables from Table?
e.g. Select @.CustomerName = CustomerName, @.EffectiveDateFrom =
EffectiveDateFrom, @.EffectiveDateEnd = EffecitveDateEnd from Customer where
customercode = 'XXX'.> Can SQL Server 2000's Select statement assign multi-variables from Table?
Yes. When multiple rows are returned, only values from the last row
returned are assigned.
Hope this helps.
Dan Guzman
SQL Server MVP
"ABC" <abc@.abc.com> wrote in message
news:eWjrMTBEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Can SQL Server 2000's Select statement assign multi-variables from Table?
> e.g. Select @.CustomerName = CustomerName, @.EffectiveDateFrom =
> EffectiveDateFrom, @.EffectiveDateEnd = EffecitveDateEnd from Customer
> where customercode = 'XXX'.
>|||Yes it can. Have you tried it and getting an error or just asking?
Andrew J. Kelly SQL MVP
"ABC" <abc@.abc.com> wrote in message
news:eWjrMTBEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Can SQL Server 2000's Select statement assign multi-variables from Table?
> e.g. Select @.CustomerName = CustomerName, @.EffectiveDateFrom =
> EffectiveDateFrom, @.EffectiveDateEnd = EffecitveDateEnd from Customer
> where customercode = 'XXX'.
>

can someone tell me why this sql statement doesnt work?

can someone tell me why this sql statement doesnt work?

SQL = "SELECT (Count(department_id) as 'totals' FROM nonconformance WHERE department_id = '7'),(Count(department_id) as 'totals2' FROM nonconformance WHERE department_id = '1') FROM nonconformance"

How do I fix it?

Thanks...

SQL = "SELECT Totals = (SELECT Count(*) FROM nonconformance WHERE department_id=7), Totals2 = (SELECT Count(*) FROM nonconformance WHERE department_id=1)"|||thanks, works great

Can someone tell me what this means?

Rc = SQL_Query(
"SELECT TerminalOptions, RRDays, InstallerId, IcePakPhone, Snak,
ScreenSetID, TerminalConfiguration, MaskOptions "
"FROM Terminal "
"WHERE (CustomerID=?) AND (TerminalID=?)",
SQL_C_LONG, &lMerchatIdenity, sizeof(long),
SQL_C_CHAR, Field[F_TERMID], strlen(Field[F_TERMID]),
SQL_INTO,
SQL_C_CHAR, LicOpt, 9,
SQL_C_CHAR, RRDays, 3,
SQL_C_CHAR, InstallerId, 7,
SQL_C_CHAR, IcePhone, 17,
SQL_C_CHAR, Snak,17,
SQL_C_CHAR, ScrId,12,
SQL_C_CHAR, TermCfg, 9,
SQL_C_CHAR, MaskOpt, 9 ,
SQL_TYPE_NULL );
I can undesrtand up to ' "WHERE (CustomerID=?) AND (TerminalID=?)",
'
whatever follows after that gets me lost ...
Can someone tell me what exaclly th erest means?
Thanks"CTG" wrote:
<snip code: repeated below>

> I can undesrtand up to ' "WHERE (CustomerID=?) AND (TerminalID=?)",
> '
> whatever follows after that gets me lost ...
> Can someone tell me what exaclly th erest means?
> Thanks
I'm not familiar with a function named SQL_Query (perhaps it's internal to
your app or part of a library you purchased?), but it appears to me that...

> SQL_C_LONG, &lMerchatIdenity, sizeof(long),
> SQL_C_CHAR, Field[F_TERMID], strlen(Field[F_TERMID]),
> SQL_INTO,
This is providing information about the variables used to fill in parameters
in the query (the question marks in the WHERE clause). Each query parameter
is given as 3 arguments passed to SQL_Query: data type, address of the
information, and length of the data pointed to by the address. SQL_INTO
marks the end of the query parameters and denotes the beginning of...

> SQL_C_CHAR, LicOpt, 9,
> SQL_C_CHAR, RRDays, 3,
> SQL_C_CHAR, InstallerId, 7,
> SQL_C_CHAR, IcePhone, 17,
> SQL_C_CHAR, Snak,17,
> SQL_C_CHAR, ScrId,12,
> SQL_C_CHAR, TermCfg, 9,
> SQL_C_CHAR, MaskOpt, 9 ,
> SQL_TYPE_NULL );
This is telling SQL_Query where to place the results of the query:
TerminalOptions will be stored in LicOpt, RRDays in RRDays, etc. It appears
to use the same pattern as above: type, location, length. SQL_TYPE_NULL is
the marker that tells SQL_Query that the list is finished.
Now, how you handle multiple records, how you insure that you don't corrupt
memory, and what the return code (stored in Rc) means is impossible to
determine from the code here.
Craig|||Thanks Craige;
I did not write this code.
I guess the prev author ASSuMEd that there is only one record and always
found and we know what happens when we ASS U ME.
Thanks for reply
"Craig Kelly" wrote:

> "CTG" wrote:
> <snip code: repeated below>
>
> I'm not familiar with a function named SQL_Query (perhaps it's internal to
> your app or part of a library you purchased?), but it appears to me that..
.
>
> This is providing information about the variables used to fill in paramete
rs
> in the query (the question marks in the WHERE clause). Each query paramet
er
> is given as 3 arguments passed to SQL_Query: data type, address of the
> information, and length of the data pointed to by the address. SQL_INTO
> marks the end of the query parameters and denotes the beginning of...
>
> This is telling SQL_Query where to place the results of the query:
> TerminalOptions will be stored in LicOpt, RRDays in RRDays, etc. It appea
rs
> to use the same pattern as above: type, location, length. SQL_TYPE_NULL i
s
> the marker that tells SQL_Query that the list is finished.
> Now, how you handle multiple records, how you insure that you don't corrup
t
> memory, and what the return code (stored in Rc) means is impossible to
> determine from the code here.
> Craig
>
>

Can someone proofread my remove duplicates script?

DELETE
FROM tblContacts
WHERE tblContacts.ID IN(
SELECT F.ID
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
)
AND tblContacts.ID NOT IN(
SELECT Min(ID)
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
GROUP BY email
)

I readily admit that I've shamelessly copied 'n pasted this from a tutorial and then taken a stab at tweaking it for my own ends. But I really don't understand what it's doing.

Really, all I want to know is that it will remove records with duplicate email fields. But I could also do with confirming - looking at the "SELECT Min(ID)" bit - does that mean that if it finds a duplicate, it'll delete the latest-added one? And if so, that changing it to remove the earliest-added one is simply a case of changing MIN to MAX?

Thanks :)A good tip for keeping your sanity is to always run scripts like this on a testing version of your database and then confirm that it has worked before even contemplating running it on prod. As such - the below is based on my best reading of the script.

Yes it will work. Yes it will delete the most recently inserted record(s) (assuming that the ID field is a monotonically increasing value such as an identity and a higher number always indicates a more recently inserted record). And yes - you can change MIN to MAX to retain the most recently inserted record.

Have a read through the script a few times though - even if you don't consider it necesary it would be nice to know what it is doing and why.

HTH|||Yeah, a test run would be advisable. Good point. I've tried reading my way through it and I just get bogged down in "so we get one list that's... and those exist in... and that doesn't exist... and..." and the will to live rapidly leaves me. I think I get it now, though. I guess I just needed someone to tell me it did do what I thought before I tried to figure out exactly how.

Thanks for the help.|||While it lookes to me like the code you posted should work, I'd suggest a simpler approach. It is a lot easier to read (at least for me anyway), and probably easier to understand.

DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))-PatP|||Ooh, that's easier :D Nice one :beer:|||Oh yeah... One thing I ought to mention before you go trundling off, this code snippet will trip over any rows that have an ID column that is NULL. This may make you need to add an ID IS NOT NULL to the outer clause if there is any chance of encountering a legitimate NULL value in the ID column. This is unlikely, but some schemas will permit it, and the results can be catastrophic!

-PatP|||A handy trick I have used in such cases is rewrite the statement as a SELECT, rather than an update or delete. See what records you are about to modify/maim, and if you have no objections, then you can run the actual data modification.|||DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))
Actually - reading this more carefully, I'm a bit confused. It looks like it'll only do one at a time? Is that right? If so, that's not a bad thing - in fact, it'd be good to know that I've managed to guess what a statement will do before I run it :rolleyes: :D

edit:

Well... having read MCrowley's excellent advice: clearly it doesn't. It gives me a big list of duplicates. But I don't understand how? It selects MAX - which is only going to return one record, right? And then it selects (or deletes) WHERE ID <> - not "is not in [a range]", but "does not equal [a value]".

I'm confused again :(|||The key is in the corrolation ;)

DELETE FROM tblContacts
WHERE ID <> (SELECT Max(z.ID)
FROM tblContacts AS z
WHERE z.email = tblContacts.email))
If you remove the bit in bold then yes - you would get one ID returned. However the bit in bold corrolates the inner and outer query so there is one MAX(ID) returned per email address.

It can be rewritten as a select query as a join of two tables that might make it more obvious:


SELECT tblContacts.*
FROM tblContacts INNER JOIN
(SELECT Max(z.ID) AS TheMaxID,
email
FROM tblContacts
GROUP BY email) AS z ON
z.email = tblContacts.email
WHERE tblContacts.ID <> TheMaxID

HTH|||The more I think about this, the harder it gets :D I swear there's a SQL gene.

Anyway - thanks for the help and patience, everyone. I think I need to just go and play with these statements and get my head round them.|||Just practice - SQL is very easy to learn but rather tricky to master.

Run the inner query on its own:
SELECT Max(z.ID) AS TheMaxID,
email
FROM tblContacts
GROUP BY emailThat might illuminate...|||When you have a subquery (one query that is logically "nested inside" of another query), the subquery gets re-evaluated for each row returned by the outer query. If the DELETE query materializes a million rows from the tblContacts table, then the SELECT query would be evaluated a million times, once for each of the rows materialized by the DELETE query.

For each row in tblContacts, that DELETE checks to see if that row has the Max(ID) value for a given email address. If the row does not have the Max(ID) for that email, then the row is deleted.

While this will sometimes confuse people, it does not confuse SQL! ;) The only point that can confuse SQL is NULL values. A NULL email is simply ignored, considered junque and deleted (the explanation of that gets a bit tricky, just take it on faith for now). A NULL ID is also deleted, for a different (but similar) reason.

Once you understand the way this works for non-NULL values, we'll worry about the NULL values. They are almost assuredly garbage anyway, so don't burn much time on them yet.

-PatP

can someone please tell me what this dql code does.

DECODE
(measureid,
'TOTD', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
'CDL', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT NVL (SUM (0.5), 0)
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
AND ele.empl_trt_id IN ('PH', 'WEND')),
'DNR', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT COUNT (empl_id) / 2
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_origin_id = 'T'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
measurevalue
) measurevalueOracle's DECODE is similar in functionality to the ANSI-standard CASE
expression used in SQL Server. Different values are returned depending on
if the measureid value is 'TODT', 'CDL' or 'DNR'.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:7067dc28f849e@.uwe...
> DECODE
> (measureid,
> 'TOTD', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
> 'CDL', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
> 'DNR', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT COUNT (empl_id) / 2
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_origin_id = 'T'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
> measurevalue
> ) measurevalue
>|||dwynenelson880 wrote:

> 'CDL', ...
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
>
my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
correct?
because i think that the result will aways be 0.5
if someone can explain this to me it would be very helpfull.
thank you|||> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
return NULL. I think the purpose of NVL here is to return 0 instead of
NULL. In SQL Server, you can use the ANSI standard COALESCE function for
this purpose.
For questions are specific to Oracle, you are better off posting to an
Oracle forum. However, we might be able to help you convert Oracle code to
SQL Server here.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:70683db28c33e@.uwe...
> dwynenelson880 wrote:
>
> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
> if someone can explain this to me it would be very helpfull.
> thank you
>|||>If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
>return NULL. I think the purpose of NVL here is to return 0 instead of
>NULL. In SQL Server, you can use the ANSI standard COALESCE function for
>this purpose.
>
thank you very much Dan
i finaly understand it now. men that was really helpfull. surry for posting
in the sql forum. next time i will know better.

can someone please tell me what this dql code does.

DECODE
(measureid,
'TOTD', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
'CDL', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT NVL (SUM (0.5), 0)
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
AND ele.empl_trt_id IN ('PH', 'WEND')),
'DNR', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT COUNT (empl_id) / 2
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_origin_id = 'T'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
measurevalue
) measurevalue
Oracle's DECODE is similar in functionality to the ANSI-standard CASE
expression used in SQL Server. Different values are returned depending on
if the measureid value is 'TODT', 'CDL' or 'DNR'.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:7067dc28f849e@.uwe...
> DECODE
> (measureid,
> 'TOTD', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
> 'CDL', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
> 'DNR', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT COUNT (empl_id) / 2
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_origin_id = 'T'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
> measurevalue
> ) measurevalue
>
|||dwynenelson880 wrote:

> 'CDL', ...
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
>
my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
correct?
because i think that the result will aways be 0.5
if someone can explain this to me it would be very helpfull.
thank you
|||> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
return NULL. I think the purpose of NVL here is to return 0 instead of
NULL. In SQL Server, you can use the ANSI standard COALESCE function for
this purpose.
For questions are specific to Oracle, you are better off posting to an
Oracle forum. However, we might be able to help you convert Oracle code to
SQL Server here.
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:70683db28c33e@.uwe...
> dwynenelson880 wrote:
>
> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
> if someone can explain this to me it would be very helpfull.
> thank you
>
|||>If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
>return NULL. I think the purpose of NVL here is to return 0 instead of
>NULL. In SQL Server, you can use the ANSI standard COALESCE function for
>this purpose.
>
thank you very much Dan
i finaly understand it now. men that was really helpfull. surry for posting
in the sql forum. next time i will know better.

can someone please tell me what this dql code does.

DECODE
(measureid,
'TOTD', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
'CDL', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT NVL (SUM (0.5), 0)
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
AND ele.empl_trt_id IN ('PH', 'WEND')),
'DNR', (SELECT COUNT (cald_date)
FROM calendar_days
WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
- (SELECT COUNT (empl_id) / 2
FROM emp_log_entries ele
WHERE empl_emp_id = employeeid
AND ele.empl_status = 'A'
AND ele.empl_origin_id = 'T'
AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
measurevalue
) measurevalueOracle's DECODE is similar in functionality to the ANSI-standard CASE
expression used in SQL Server. Different values are returned depending on
if the measureid value is 'TODT', 'CDL' or 'DNR'.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:7067dc28f849e@.uwe...
> DECODE
> (measureid,
> 'TOTD', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal),
> 'CDL', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
> 'DNR', (SELECT COUNT (cald_date)
> FROM calendar_days
> WHERE cald_date BETWEEN :fecinicial AND :fecfinal)
> - (SELECT COUNT (empl_id) / 2
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_origin_id = 'T'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal),
> measurevalue
> ) measurevalue
>|||dwynenelson880 wrote:
> 'CDL', ...
> - (SELECT NVL (SUM (0.5), 0)
> FROM emp_log_entries ele
> WHERE empl_emp_id = employeeid
> AND ele.empl_status = 'A'
> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
> AND ele.empl_trt_id IN ('PH', 'WEND')),
>
my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
correct?
because i think that the result will aways be 0.5
if someone can explain this to me it would be very helpfull.
thank you|||> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
return NULL. I think the purpose of NVL here is to return 0 instead of
NULL. In SQL Server, you can use the ANSI standard COALESCE function for
this purpose.
For questions are specific to Oracle, you are better off posting to an
Oracle forum. However, we might be able to help you convert Oracle code to
SQL Server here.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"dwynenelson880" <u33162@.uwe> wrote in message news:70683db28c33e@.uwe...
> dwynenelson880 wrote:
>
>> 'CDL', ...
>> - (SELECT NVL (SUM (0.5), 0)
>> FROM emp_log_entries ele
>> WHERE empl_emp_id = employeeid
>> AND ele.empl_status = 'A'
>> AND ele.empl_date BETWEEN :fecinicial AND :fecfinal
>> AND ele.empl_trt_id IN ('PH', 'WEND')),
> my question is what is - (SELECT NVL (SUM (0.5), 0) doing. is this code
> correct?
> because i think that the result will aways be 0.5
> if someone can explain this to me it would be very helpfull.
> thank you
>|||>If no rows satisfy the WHERE clause, the SUM(0.5) aggregate function will
>return NULL. I think the purpose of NVL here is to return 0 instead of
>NULL. In SQL Server, you can use the ANSI standard COALESCE function for
>this purpose.
>
thank you very much Dan
i finaly understand it now. men that was really helpfull. surry for posting
in the sql forum. next time i will know better.

Can someone please explain this to me...

why do the following return the same datasets?

select * from myTable where myData = ''

select * from myTable where myData = ' '

in the first I'm specifically searching empty strings, in the second a sequence of five spaces. Yet both return any and all white character matches? Is this a "feature" of SQL...

P.S. I'm using T-SQL

This should work. Check it out

select * from myTable where myData = '';

select * from myTable where myData = SPACE(5);

|||

Yes, trailing spaces are not significant (nor stored) in varchar fields.

Can somebody explain what this is doing... select ...status & 4096 ...

Can somebody explain what "status & 4096" is doing in this SQL:
"select name, (status & 4096) as 'SingleUser' from sysdatabases.
In SQL Books, I find reference to "bitwise" operation. In my case, a databa
se has a status value of 4104 (which means "single user" and "trunc. log on
chkpt"). This statement returns a value of 4096 for this particular databas
e. I want to understand wh
y a 4096 (and 0 for ones that are not "single user") is being returned.
Thanks> I want to understand why a 4096 (and 0 for ones that are not "single
user") is being returned.
From the SQL 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_operator_7fax.htm">
The bitwise & operator performs a bitwise logical AND between the two
expressions, taking each corresponding bit for both expressions. The bits in
the result are set to 1 if and only if both bits (for the current bit being
resolved) in the input expressions have a value of 1; otherwise, the bit in
the result is set to 0.
<Excerpt>
Since the binary value of 4096 is 0001000000000000, a single bit is
evaluated and the expression 'status & 4096 will yeild either 0 or 4096.
This allows the single-user status bit to be examined independently of the
other bit values.
Hope this helps.
Dan Guzman
SQL Server MVP
"john lantz" <anonymous@.discussions.microsoft.com> wrote in message
news:060E090D-18F1-4649-980B-FF4FB64DB205@.microsoft.com...
> Can somebody explain what "status & 4096" is doing in this SQL:
> "select name, (status & 4096) as 'SingleUser' from sysdatabases.
> In SQL Books, I find reference to "bitwise" operation. In my case, a
database has a status value of 4104 (which means "single user" and "trunc.
log on chkpt"). This statement returns a value of 4096 for this particular
database. I want to understand why a 4096 (and 0 for ones that are not
"single user") is being returned.
> Thanks