3 largest value

I can use MAX to find the largest value but Is there a way or function to find 3 largest value from a column?Originally posted by QaAP
I can use MAX to find the largest value but Is there a way or function to find 3 largest value from a column?
There are various ways. One is:
SELECT col FROM tab
WHERE 3 >
(SELECT COUNT(DISTINCT col) FROM tab tab2
WHERE tab2.col > tab.col);|||Originally posted by andrewst
There are various ways. One is:
SELECT col FROM tab
WHERE 3 >
(SELECT COUNT(DISTINCT col) FROM tab tab2
WHERE tab2.col > tab.col);

I need to get 3 largest values from 1 table and 1 column. eg.

Table1:
Name Age
abc 3
DAJ 8
YYY 19
TTT 25

3 largest column

Name AGE
----
DAJ 8
YYY 19
TTT 25|||OK, so just change the columns in my previous answer:

SELECT name, age
FROM table1
WHERE 3 >
( SELECT COUNT(DISTINCT age)
FROM table1 t1
WHERE t1.age > table1.age
);|||dear friend,

The printing 3rd largest number not working in MySQL5.
************************************************** ******************************
select * from fun;
+---+
| money |
+---+
| 123 |
| 111 |
| 12 |
| 134 |
| 777 |
| 888 |
| 666 |
| 555 |
| 99999 |
| 256 |
| 777 |
+---+
11 rows in set (0.00 sec)

************************************************** **********************************************

select f1.money from fun as f1 where 3>(select count(distinct f1.money) from fun as f1, fun as f2 where f1.money>f2.money order by f1.money desc);

Result is : Empty set (0.01 sec)

Please reply what is the solutin|||I want to print the 3rd largest number of a column say money from table fun as given.

printing 3rd largest number not working
Pls help

The printing 3rd largest number not working in MySQL5.
************************************************** ******************************
select * from fun;
+---+
| money |
+---+
| 123 |
| 111 |
| 12 |
| 134 |
| 777 |
| 888 |
| 666 |
| 555 |
| 99999 |
| 256 |
| 777 |
+---+
11 rows in set (0.00 sec)

************************************************** **********************************************

select f1.money from fun as f1 where 3>(select count(distinct f1.money) from fun as f1, fun as f2 where f1.money>f2.money order by f1.money desc);

Result is : Empty set (0.01 sec)

Please reply what is the solution|||This smells like homework to me, but I'll give you a small hand by relocating your question to the MySQL forum. There is a MySQL-specific extension to SQL specifically to help with tasks like this.

-PatP|||Same as I posted before really:

select f1.money
from fun as f1
where 3 >
( select count(distinct f2.money)
from fun as f2
where f1.money>f2.money
);|||please don't post the same question into more than one forum

threads merged|||hi,

It is giving the result as:
mysql> select f1.money
-> from fun as f1
-> where 3 >
-> ( select count(distinct f2.money)
-> from fun as f2
-> where f1.money>f2.money
-> );
+---+
| money |
+---+
| 123 |
| 111 |
| 12 |
+---+
3 rows in set (0.00 sec)

(i.e.) It is taking first three rows of the column money. But I want to print 3 greatest number. i.e.
99999
888
777|||somehow, the inequality was incorrectly reversed

this works (i tested it on your sample data) --select distinct money
from fun as f1
where 3 >
( select count(distinct money)
from fun
where f1.money < money
)
order by money desc|||Other solution, without subquery but with (self) join:
SELECT A.age, A.whatever
FROM table1 AS A
INNER JOIN table1 AS B
ON A.age <= B.age
GROUP BY A.age, A.whatever -- make sure this is at least a PK of table1
HAVING count(*) <= 3
If you just want to see the third largest, replace "<= 3" by "= 3".

Depending on the size of your table (and presence of indices etc.) the subquery solution or the join solution might be the most performant one.|||Yet another solution, but unfortunately a platform-specific one (just like the mysql one), now for DB2:
SELECT *
FROM table1
ORDER BY age DESC
FETCH FIRST 3 ROWS ONLYB.T.W., this is the most performant solution of the three when using DB2.|||excuse me? which mysql-specific solution would you be referring to? :)

pat did not move the thread, we are still in the generic SQL forum, and all the solutions offered up until your last have been good, standard sql

granted, there is a cleaner mysql solution, but fondofopensource re-opened a three-year-old thread in the SQL forum as well as starting a duplicate post in the mysql forum, so when i merged the threads, i left the merged thread here

let's wait to see if he/she is interested in the mysql solution too...|||Sorry, then.
You are right.
Actually, I didn't pay much attention to the mysql stuff being said ;)|||no harm, no foul ;)

the "standard sql" forum is of special interest to me, and i will routinely move threads out of it into more appropriate forums when necessary

this was a difficult case, because the question was actually posted into the mysql forum as well as here

so my decision to merge the threads and leave them here was based not on the poster's database but on the sql solution offered earlier in this thread (which the poster was trying to implement)

2nd Req: Series style function oddity in SP1

I'll try posting this again, since it garnered no reply the first time.
Greetings.
I found an odd issue when working with a series style in a line chart
in SP1. I was using my own function to determine the color of the
series. So I entered =Code.PickColor(Fields!ResultSort.Value) for the
formula. My code is this:
Public Function PickColor(iResultSort As Integer) As String
Dim sColor As String
Select Case iResultSort
Case 0: sColor = "#000000" ' Black, Full
Case 3: sColor = "#800000" ' Brown, Avg
End Select
PickColor = sColor
End Function
The ResultSort field is defined in SQL as an integer. What happened
on the chart is that the data line was drawn in the color expected,
but from the end of the line to the x axis, a black line was drawn.
If I change the code to use a String instead:
Public Function PickColor(sResultSort As String) As String
Dim sColor As String
Select Case sResultSort
Case "0": sColor = "#000000" ' Black, Full
Case "3": sColor = "#800000" ' Brown, Avg
End Select
PickColor = sColor
End Function
then it draws as expected.
I would sort of like to know if there is something that I am missing
here that causes it to behave like this or if there is an issue with
the functionality.
I have the rdl and sample data as xml attached here, and can email
screenshots or whatever if desired.
Thanks.
Dale.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!DPC.Value</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderColor>
<Default>=Code.PickColor( Fields!ResultSort.Value
)</Default>
</BorderColor>
</Style>
<Marker>
<Type>Auto</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Line</Type>
<Title />
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!DaysAfter.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!DaysAfter.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!ResultSort.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!ResultSort.Value</Label>
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>5.125in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="List_History ReadOnly">
<rd:DataSourceID>f2bd5a97-e7bc-444d-a3f9-b6f33e8fb5a7</rd:DataSourceID>
<DataSourceReference>List_History ReadOnly</DataSourceReference>
</DataSource>
</DataSources>
<Code>Public Function PickColor(iResultSort As Integer) As String
Dim sColor As String
Select Case iResultSort
Case 0: sColor = "#000000" ' Black, Full
Case 3: sColor = "#800000" ' Brown, Avg
End Select
PickColor = sColor
End Function
Public Function PickLineWidth(sResultSort As String) As String
Dim sWidth As String
Select Case sResultSort
Case 0: sWidth = "1.5pt" ' Thicker
Case Else: sWidth = "1pt" ' Normal
End Select
End Function
</Code>
<Width>6.625in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="DaysAfter">
<DataField>DaysAfter</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ResultSort">
<DataField>ResultSort</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="DPC">
<DataField>DPC</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>List_History ReadOnly</DataSourceName>
<CommandText>SELECT * FROM dbo.Dale</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>1f236bff-9e92-4d02-a9b0-b41e2d7f6950</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
<SampleData>
<DataRow DaysAfter="34" ResultSort="0" DPC="4.07"/><DataRow
DaysAfter="65" ResultSort="0" DPC="4.07"/><DataRow DaysAfter="95"
ResultSort="0" DPC="4.07"/><DataRow DaysAfter="126" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="156" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="187" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="218" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="248" ResultSort="0"
DPC="4.07"/><DataRow DaysAfter="279" ResultSort="0"
DPC="4.07"/><DataRow ResultSort="0" DPC="4.07"/><DataRow
DaysAfter="34" ResultSort="1" DPC="3.06"/><DataRow DaysAfter="65"
ResultSort="1" DPC="3.06"/><DataRow DaysAfter="95" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="126" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="156" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="187" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="218" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="248" ResultSort="1"
DPC="3.06"/><DataRow DaysAfter="279" ResultSort="1"
DPC="3.06"/><DataRow ResultSort="1" DPC="3.06"/><DataRow
DaysAfter="34" ResultSort="3" DPC="8.59"/><DataRow DaysAfter="65"
ResultSort="3" DPC="11.32"/><DataRow DaysAfter="95" ResultSort="3"
DPC="14.78"/><DataRow DaysAfter="126" ResultSort="3"
DPC="15.42"/><DataRow DaysAfter="156" ResultSort="3"
DPC="16.04"/><DataRow DaysAfter="187" ResultSort="3"
DPC="16.33"/></SampleData>Please check this posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=f33c29d0-dbdf-4509-b67c-e517c1959e64&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dale M." <Dale M.@.discussions.microsoft.com> wrote in message
news:350063B1-CBC5-47A0-88F4-933A9F1E864F@.microsoft.com...
> I'll try posting this again, since it garnered no reply the first time.
> Greetings.
> I found an odd issue when working with a series style in a line chart
> in SP1. I was using my own function to determine the color of the
> series. So I entered =Code.PickColor(Fields!ResultSort.Value) for the
> formula. My code is this:
> Public Function PickColor(iResultSort As Integer) As String
> Dim sColor As String
> Select Case iResultSort
> Case 0: sColor = "#000000" ' Black, Full
> Case 3: sColor = "#800000" ' Brown, Avg
> End Select
> PickColor = sColor
> End Function
> The ResultSort field is defined in SQL as an integer. What happened
> on the chart is that the data line was drawn in the color expected,
> but from the end of the line to the x axis, a black line was drawn.
> If I change the code to use a String instead:
> Public Function PickColor(sResultSort As String) As String
> Dim sColor As String
> Select Case sResultSort
> Case "0": sColor = "#000000" ' Black, Full
> Case "3": sColor = "#800000" ' Brown, Avg
> End Select
> PickColor = sColor
> End Function
> then it draws as expected.
> I would sort of like to know if there is something that I am missing
> here that causes it to behave like this or if there is an issue with
> the functionality.
> I have the rdl and sample data as xml attached here, and can email
> screenshots or whatever if desired.
> Thanks.
> Dale.
> <?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="chart1">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> </Style>
> <Legend>
> <Visible>true</Visible>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Fields!DPC.Value</Value>
> </DataValue>
> </DataValues>
> <DataLabel />
> <Style>
> <BorderColor>
> <Default>=Code.PickColor( Fields!ResultSort.Value
> )</Default>
> </BorderColor>
> </Style>
> <Marker>
> <Type>Auto</Type>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Min>0</Min>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>DataSet1</DataSetName>
> <PointWidth>0</PointWidth>
> <Type>Line</Type>
> <Title />
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="chart1_CategoryGroup1">
> <GroupExpressions>
>
<GroupExpression>=Fields!DaysAfter.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!DaysAfter.Value</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <SeriesGroupings>
> <SeriesGrouping>
> <DynamicSeries>
> <Grouping Name="chart1_SeriesGroup1">
> <GroupExpressions>
>
<GroupExpression>=Fields!ResultSort.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label>=Fields!ResultSort.Value</Label>
> </DynamicSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundColor>White</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <ValueAxis>
> <Axis>
> <Title />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Min>0</Min>
> <Margin>true</Margin>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>5.125in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="List_History ReadOnly">
>
<rd:DataSourceID>f2bd5a97-e7bc-444d-a3f9-b6f33e8fb5a7</rd:DataSourceID>
> <DataSourceReference>List_History ReadOnly</DataSourceReference>
> </DataSource>
> </DataSources>
> <Code>Public Function PickColor(iResultSort As Integer) As String
> Dim sColor As String
> Select Case iResultSort
> Case 0: sColor = "#000000" ' Black, Full
> Case 3: sColor = "#800000" ' Brown, Avg
> End Select
> PickColor = sColor
> End Function
> Public Function PickLineWidth(sResultSort As String) As String
> Dim sWidth As String
> Select Case sResultSort
> Case 0: sWidth = "1.5pt" ' Thicker
> Case Else: sWidth = "1pt" ' Normal
> End Select
> End Function
> </Code>
> <Width>6.625in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="DaysAfter">
> <DataField>DaysAfter</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="ResultSort">
> <DataField>ResultSort</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="DPC">
> <DataField>DPC</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>List_History ReadOnly</DataSourceName>
> <CommandText>SELECT * FROM dbo.Dale</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <Description />
> <rd:ReportID>1f236bff-9e92-4d02-a9b0-b41e2d7f6950</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
> <SampleData>
> <DataRow DaysAfter="34" ResultSort="0" DPC="4.07"/><DataRow
> DaysAfter="65" ResultSort="0" DPC="4.07"/><DataRow DaysAfter="95"
> ResultSort="0" DPC="4.07"/><DataRow DaysAfter="126" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="156" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="187" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="218" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="248" ResultSort="0"
> DPC="4.07"/><DataRow DaysAfter="279" ResultSort="0"
> DPC="4.07"/><DataRow ResultSort="0" DPC="4.07"/><DataRow
> DaysAfter="34" ResultSort="1" DPC="3.06"/><DataRow DaysAfter="65"
> ResultSort="1" DPC="3.06"/><DataRow DaysAfter="95" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="126" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="156" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="187" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="218" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="248" ResultSort="1"
> DPC="3.06"/><DataRow DaysAfter="279" ResultSort="1"
> DPC="3.06"/><DataRow ResultSort="1" DPC="3.06"/><DataRow
> DaysAfter="34" ResultSort="3" DPC="8.59"/><DataRow DaysAfter="65"
> ResultSort="3" DPC="11.32"/><DataRow DaysAfter="95" ResultSort="3"
> DPC="14.78"/><DataRow DaysAfter="126" ResultSort="3"
> DPC="15.42"/><DataRow DaysAfter="156" ResultSort="3"
> DPC="16.04"/><DataRow DaysAfter="187" ResultSort="3"
> DPC="16.33"/></SampleData>
>

24h/7d Time Periods TO 10h/5d Time Periods

I have to do alot of date calculations. For some calculations, I can use the datediff or dateadd function to get a Time Period between 2 dates.

Now for some dates I need to calculate the time between 2 dates BUT:

a week = 5 days starting from Monday to Friday a day starts at 8AM and ends at 6PM (so a day is 10 hours)

You can probably calculate this manually but what about summer to winter hour and the month Februari when it has 29 days etc.

So I was thinking ... is it possible to calculate the Time Period for weeks with 24h a day / 7 days AND then transform that Time Period to a time period for weeks with 10h a day / 5 days ?

If anyone has an idea to solve this, either with functions or an other way, please let me know! Thanks

For our internal help desk application I created the following user-defined function to calculate the hours between 8am and 5pm Monday - Friday. There are obvious holes, but it fell within the management guidelines for their reporting:

CREATE FUNCTION [dbo].fnGetWorkHours
(@.start_dt datetime=null, @.end_dt datetime=null, @.co_user_id int=null)
RETURNS int

AS
-- Returns the number of weekday hours between the supplied dates
BEGIN
declare @.d1 datetime, @.d2 datetime, @.d1a datetime, @.d2a datetime, @.hours int, @.offset int

set @.offset = 0
if @.co_user_id is not null
begin
select @.offset = l.timezone_offset
from [HelpDesk].[dbo].[Company_User] c INNER JOIN [HelpDesk].[dbo].[Location_Def] l
on c.[loc_id] = l.[loc_id]
where c.co_user_id = @.co_user_id
end

set @.d1 = dateadd(hour, @.offset, @.start_dt)
set @.d2 = dateadd(hour, @.offset, @.end_dt)

if convert(char(10),@.d1,101) = convert(char(10),@.d2,101)
BEGIN
SELECT @.hours = DATEDIFF(hour, @.d1, @.d2)
END
ELSE
BEGIN
SET @.d1 = CASE WHEN @.d1 > convert(char(10),@.d1,101) + ' 17:00' THEN convert(char(10),dateadd(day,1,@.d1),101) + ' 08:00'
ELSE @.d1 END
SET @.d2 = CASE WHEN @.d2 < convert(char(10),@.d2,101) + ' 08:00' THEN convert(char(10),@.d2,101) + ' 08:00'
ELSE @.d2 END
SET @.d1 = CASE WHEN DATEPART(weekday,@.d1+@.@.DATEFIRST)=1 THEN convert(char(10),dateadd(day,1,@.d1),101) + ' 08:00'
WHEN DATEPART(weekday,@.d1+@.@.DATEFIRST)=7 THEN convert(char(10),dateadd(day,2,@.d1),101) + ' 08:00'
ELSE @.d1 END
SET @.d2 = CASE WHEN DATEPART(weekday,@.d2+@.@.DATEFIRST)=1 THEN convert(char(10),dateadd(day,-2,@.d2),101) + ' 17:00'
WHEN DATEPART(weekday,@.d2+@.@.DATEFIRST)=7 THEN convert(char(10),dateadd(day,-1,@.d2),101) + ' 17:00'
ELSE @.d2 END
set @.d1a = dateadd(day,1,@.d1)
set @.d2a = dateadd(day,-1,@.d2)
SELECT @.hours =
CASE WHEN DATEDIFF(day, @.d1, @.d2) > 1 THEN
(DATEDIFF(day, @.d1a, @.d2a) + 1 - (2 * DATEDIFF(week, @.d1, @.d2))) * 8
ELSE 0 END +
CASE WHEN convert(char(10),@.d1,101) = convert(char(10),@.d2,101)
THEN DATEDIFF(hour, @.d1, @.d2)
WHEN convert(char(10),@.d1,101) < convert(char(10),@.d2,101)
THEN DATEDIFF(hour, @.d1, convert(char(10),@.d1,101) + ' 17:00')
ELSE 0 END +
CASE WHEN DATEDIFF(day, @.d1, @.d2) > 0 THEN
DATEDIFF(hour, convert(char(10),@.d2,101) + ' 08:00', @.d2)
ELSE 0 END
END
RETURN(@.hours)
END
go

|||Best is to build a calendar table with the possible dates, columns that track working days, holidays, time adjustments etc. You can then easily write queries to answer your questions. It is possible to write scalar UDFs but the logic for those will often be complicated and hard to use if you want to support different types of calculations easily.

2005: creating aggregate function from .NET assembly

Hello,
I am learning SQL Server 2005. I have (correctly) written in .NET
assembly DemoSQLServer with aggregate function AvgNoMinMax in class
Demo and I have added assembly to database DemoSQLServer. Now I need
to create aggregate in SQL Server. I tried this way:

CREATE AGGREGATE AvgNoMinMax(@.v float) RETURNS float EXTERNAL NAME
[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]

Unfortunately I have error:

Incorrect syntax near '.'.

I don't know what's wrong. Please help.
Thank you very much!
/RAM/R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

I am learning SQL Server 2005. I have (correctly) written in .NET
assembly DemoSQLServer with aggregate function AvgNoMinMax in class
Demo and I have added assembly to database DemoSQLServer. Now I need
to create aggregate in SQL Server. I tried this way:
>
CREATE AGGREGATE AvgNoMinMax(@.v float) RETURNS float EXTERNAL NAME
[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]
>
Unfortunately I have error:
>
Incorrect syntax near '.'.

The thing to do when you get a syntax error is to look up the topic
for the command in question and study the syntax graph in detail to
find out what's wrong.

Yes, I'm telling you to read the manual. You see, you are learning SQL 2005,
and part of that is to learn the tremendous asset that Books Online actually
is. Besides, by doing mistakes and finding what mistake - that is how you
really learn things. You don't learn things by being spoon-fed in
newsgroups.

(That is not to say that asking in newsgroups is a bad idea. Sometimes
it's rather creativity you need help with, and that is not be found in
Books Online.)

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

2005 inline table function produce incorrect resultset than 2000

OK...this all works in 2000.
I have wondered why return results were different in an inline table
function (run on the same set of data) in 2005 than 2000. The function is the
same...a parametized function with a no join select on a table. The function
returns incorrect results yet if I run the sql statement that is in the
function as a stand alone in the server manager studio...it returns the
correct results. This problem does not exist in sql server 2000. Here is the
inline table function.
CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
@.middleName uddtMiddleName=NULL,
@.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
@.startDOB datetime = NULL, @.endDOB datetime = NULL,
@.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
@.birthState uddtBirthState=NULL)
RETURNS TABLE
AS
RETURN
(
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
= @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
BirthCountry = @.birthCountry)
AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
@.birthState)
)
running this statement:
set ansi_nulls off
SELECT personId FROM
fn_usrsearchpersontest(null,null,'gawrisch',null,n ull,null,null,null,null)
does not return the right set if some of the records have nulls in the
corresponding fields addressed in the table function that has 'OR' as part of
the condition (i.e. social security)
Yet if I run this in studio it return the correct number of results
set ansi_nulls off
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
ISNULL(null, MiddleName) OR MiddleName = null)
AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
ISNULL(null, SocialSecurity) OR SocialSecurity = null)
AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
thoughts? Like I said if this is run in sql server 2000 the results are the
same in either running the function or sql statement as above...I need to get
this resolved for a migration
the parameter in the second sql statement 'funke' should be
'gawrisch'...otherwise it wouldn't be the same result...sorry...bad
proofiing
"bLad3" wrote:

> OK...this all works in 2000.
> I have wondered why return results were different in an inline table
> function (run on the same set of data) in 2005 than 2000. The function is the
> same...a parametized function with a no join select on a table. The function
> returns incorrect results yet if I run the sql statement that is in the
> function as a stand alone in the server manager studio...it returns the
> correct results. This problem does not exist in sql server 2000. Here is the
> inline table function.
> CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
> @.middleName uddtMiddleName=NULL,
> @.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
> @.startDOB datetime = NULL, @.endDOB datetime = NULL,
> @.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
> @.birthState uddtBirthState=NULL)
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
> ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
> AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
> ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
> AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
> = @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
> BirthCountry = @.birthCountry)
> AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
> @.birthState)
> )
> running this statement:
> set ansi_nulls off
> SELECT personId FROM
> fn_usrsearchpersontest(null,null,'gawrisch',null,n ull,null,null,null,null)
> does not return the right set if some of the records have nulls in the
> corresponding fields addressed in the table function that has 'OR' as part of
> the condition (i.e. social security)
> Yet if I run this in studio it return the correct number of results
> set ansi_nulls off
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
> ISNULL(null, MiddleName) OR MiddleName = null)
> AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
> ISNULL(null, SocialSecurity) OR SocialSecurity = null)
> AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
> AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
> AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
> thoughts? Like I said if this is run in sql server 2000 the results are the
> same in either running the function or sql statement as above...I need to get
> this resolved for a migration
|||Hi Blad,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you use Inline
table to query some data under SQL Server 2005, but it returns the
different result with that the sql statement executed in Management Studio.
And also the same SQL statement return the same results in SQL Server 2000.
If I have misunderstood your concern, please feel free to point it out.
Since you defined this function on a specific database and you use
User-defined Data type in your table, I can not re-pro it on my own
environment. So for narrowing down the question, would you like to give me
some more information?
1. Which edition of SQL Server 2005 do you use?
2. What is the difference between the results of those 2 method? Does
the Inline table return all the record with NULL value in those
corresponding column?
3. Do you have any other function to query the table? If so, does this
issue happen on those function?
4. If you create a simple function use Inline table ( with less
criteria ) to query the table, does this issue happen?
If there are more information on the issue, please feel free to let us
know. Have a great day!
Best Regards,
Wei-Dong XU
Microsoft Support
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of any assistance.
|||Here is a quote from Books Online:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure.
Whenever the stored procedure is subsequently executed,
the setting of SET ANSI_NULLS is restored to its originally
used value and takes effect. When invoked inside a stored
procedure, the setting of SET ANSI_NULLS is not changed.
In this aspect, views and functions are also treated like stored
procedures. I guess you have created the function when SET ANSI_NULLS
was ON, so whenever you execute the function, it considers this
setting, regardless of the current state of SET ANSI_NULLS when the
function is invoked.
You can re-create the function with SET ANSI_NULLS OFF and it should
behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
would modify the function to use conditions like this:
[...]
AND (BirthCountry LIKE @.birthCountry
OR BirthCountry = @.birthCountry
OR @.birthCountry IS NULL)
[...]
Razvan

2005 inline table function produce incorrect resultset than 2000

OK...this all works in 2000.
I have wondered why return results were different in an inline table
function (run on the same set of data) in 2005 than 2000. The function is the
same...a parametized function with a no join select on a table. The function
returns incorrect results yet if I run the sql statement that is in the
function as a stand alone in the server manager studio...it returns the
correct results. This problem does not exist in sql server 2000. Here is the
inline table function.
CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
@.middleName uddtMiddleName=NULL,
@.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
@.startDOB datetime = NULL, @.endDOB datetime = NULL,
@.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
@.birthState uddtBirthState=NULL)
RETURNS TABLE
AS
RETURN
(
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
= @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
BirthCountry = @.birthCountry)
AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState = @.birthState)
)
running this statement:
set ansi_nulls off
SELECT personId FROM
fn_usrsearchpersontest(null,null,'gawrisch',null,null,null,null,null,null)
does not return the right set if some of the records have nulls in the
corresponding fields addressed in the table function that has 'OR' as part of
the condition (i.e. social security)
Yet if I run this in studio it return the correct number of results
set ansi_nulls off
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
ISNULL(null, MiddleName) OR MiddleName = null)
AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
ISNULL(null, SocialSecurity) OR SocialSecurity = null)
AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
thoughts? Like I said if this is run in sql server 2000 the results are the
same in either running the function or sql statement as above...I need to get
this resolved for a migrationthe parameter in the second sql statement 'funke' should be
'gawrisch'...otherwise it wouldn't be the same result...sorry...bad
proofiing
"bLad3" wrote:
> OK...this all works in 2000.
> I have wondered why return results were different in an inline table
> function (run on the same set of data) in 2005 than 2000. The function is the
> same...a parametized function with a no join select on a table. The function
> returns incorrect results yet if I run the sql statement that is in the
> function as a stand alone in the server manager studio...it returns the
> correct results. This problem does not exist in sql server 2000. Here is the
> inline table function.
> CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
> @.middleName uddtMiddleName=NULL,
> @.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
> @.startDOB datetime = NULL, @.endDOB datetime = NULL,
> @.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
> @.birthState uddtBirthState=NULL)
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
> ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
> AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
> ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
> AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
> = @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) OR
> BirthCountry = @.birthCountry)
> AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState => @.birthState)
> )
> running this statement:
> set ansi_nulls off
> SELECT personId FROM
> fn_usrsearchpersontest(null,null,'gawrisch',null,null,null,null,null,null)
> does not return the right set if some of the records have nulls in the
> corresponding fields addressed in the table function that has 'OR' as part of
> the condition (i.e. social security)
> Yet if I run this in studio it return the correct number of results
> set ansi_nulls off
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
> ISNULL(null, MiddleName) OR MiddleName = null)
> AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
> ISNULL(null, SocialSecurity) OR SocialSecurity = null)
> AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
> AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
> AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
> thoughts? Like I said if this is run in sql server 2000 the results are the
> same in either running the function or sql statement as above...I need to get
> this resolved for a migration|||Hi Blad,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you use Inline
table to query some data under SQL Server 2005, but it returns the
different result with that the sql statement executed in Management Studio.
And also the same SQL statement return the same results in SQL Server 2000.
If I have misunderstood your concern, please feel free to point it out.
Since you defined this function on a specific database and you use
User-defined Data type in your table, I can not re-pro it on my own
environment. So for narrowing down the question, would you like to give me
some more information?
1. Which edition of SQL Server 2005 do you use?
2. What is the difference between the results of those 2 method? Does
the Inline table return all the record with NULL value in those
corresponding column?
3. Do you have any other function to query the table? If so, does this
issue happen on those function?
4. If you create a simple function use Inline table ( with less
criteria ) to query the table, does this issue happen?
If there are more information on the issue, please feel free to let us
know. Have a great day!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.|||Here is a quote from Books Online:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure.
Whenever the stored procedure is subsequently executed,
the setting of SET ANSI_NULLS is restored to its originally
used value and takes effect. When invoked inside a stored
procedure, the setting of SET ANSI_NULLS is not changed.
In this aspect, views and functions are also treated like stored
procedures. I guess you have created the function when SET ANSI_NULLS
was ON, so whenever you execute the function, it considers this
setting, regardless of the current state of SET ANSI_NULLS when the
function is invoked.
You can re-create the function with SET ANSI_NULLS OFF and it should
behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
would modify the function to use conditions like this:
[...]
AND (BirthCountry LIKE @.birthCountry
OR BirthCountry = @.birthCountry
OR @.birthCountry IS NULL)
[...]
Razvan|||nice catch...I thought it had to be something like that but I tried set
ansi_nulls off with an alter on the procedure...that did not work and that
is why I thought it was some other problem...didn't occur to me to drop and
recreate
also nice call with the @.birthCountry IS NULL was not looking at it from
that perspective...will be much cleaner...ticks me off because that was a dah!
Thnx again
"Razvan Socol" wrote:
> Here is a quote from Books Online:
> For stored procedures, SQL Server uses the SET ANSI_NULLS setting
> value from the initial creation time of the stored procedure.
> Whenever the stored procedure is subsequently executed,
> the setting of SET ANSI_NULLS is restored to its originally
> used value and takes effect. When invoked inside a stored
> procedure, the setting of SET ANSI_NULLS is not changed.
> In this aspect, views and functions are also treated like stored
> procedures. I guess you have created the function when SET ANSI_NULLS
> was ON, so whenever you execute the function, it considers this
> setting, regardless of the current state of SET ANSI_NULLS when the
> function is invoked.
> You can re-create the function with SET ANSI_NULLS OFF and it should
> behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
> would modify the function to use conditions like this:
> [...]
> AND (BirthCountry LIKE @.birthCountry
> OR BirthCountry = @.birthCountry
> OR @.birthCountry IS NULL)
> [...]
> Razvan
>|||Solved because of the ansi_nulls set to on upon creation of func...missed that
Thnx for help though
"Wei-Dong XU [MS]" wrote:
>
> Hi Blad,
> Welcome to use MSDN Managed Newsgroup!
> From your description, my understanding of this issue is: you use Inline
> table to query some data under SQL Server 2005, but it returns the
> different result with that the sql statement executed in Management Studio.
> And also the same SQL statement return the same results in SQL Server 2000.
> If I have misunderstood your concern, please feel free to point it out.
> Since you defined this function on a specific database and you use
> User-defined Data type in your table, I can not re-pro it on my own
> environment. So for narrowing down the question, would you like to give me
> some more information?
> 1. Which edition of SQL Server 2005 do you use?
> 2. What is the difference between the results of those 2 method? Does
> the Inline table return all the record with NULL value in those
> corresponding column?
> 3. Do you have any other function to query the table? If so, does this
> issue happen on those function?
> 4. If you create a simple function use Inline table ( with less
> criteria ) to query the table, does this issue happen?
> If there are more information on the issue, please feel free to let us
> know. Have a great day!
> Best Regards,
> Wei-Dong XU
> Microsoft Support
> ----
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ----
> It is my pleasure to be of any assistance.
>
>|||You are very welcome! Enjoy a nice weekend!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.

2005 inline table function produce incorrect resultset than 2000

OK...this all works in 2000.
I have wondered why return results were different in an inline table
function (run on the same set of data) in 2005 than 2000. The function is th
e
same...a parametized function with a no join select on a table. The function
returns incorrect results yet if I run the sql statement that is in the
function as a stand alone in the server manager studio...it returns the
correct results. This problem does not exist in sql server 2000. Here is the
inline table function.
CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
@.middleName uddtMiddleName=NULL,
@.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
@.startDOB datetime = NULL, @.endDOB datetime = NULL,
@.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
@.birthState uddtBirthState=NULL)
RETURNS TABLE
AS
RETURN
(
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIKE
ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LIKE
ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurity)
AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCounty
= @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry) O
R
BirthCountry = @.birthCountry)
AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
@.birthState)
)
running this statement:
set ansi_nulls off
SELECT personId FROM
fn_usrsearchpersontest(null,null,'gawris
ch',null,null,null,null,null,null)
does not return the right set if some of the records have nulls in the
corresponding fields addressed in the table function that has 'OR' as part o
f
the condition (i.e. social security)
Yet if I run this in studio it return the correct number of results
set ansi_nulls off
SELECT Id as PersonId FROM Persons
WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
ISNULL(null, MiddleName) OR MiddleName = null)
AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
ISNULL(null, SocialSecurity) OR SocialSecurity = null)
AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = null)
AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
thoughts? Like I said if this is run in sql server 2000 the results are the
same in either running the function or sql statement as above...I need to ge
t
this resolved for a migrationthe parameter in the second sql statement 'funke' should be
'gawrisch'...otherwise it wouldn't be the same result...sorry...bad
proofiing
"bLad3" wrote:

> OK...this all works in 2000.
> I have wondered why return results were different in an inline table
> function (run on the same set of data) in 2005 than 2000. The function is
the
> same...a parametized function with a no join select on a table. The functi
on
> returns incorrect results yet if I run the sql statement that is in the
> function as a stand alone in the server manager studio...it returns the
> correct results. This problem does not exist in sql server 2000. Here is t
he
> inline table function.
> CREATE FUNCTION fn_UsrSearchPersontest ( @.firstName uddtFirstName=NULL,
> @.middleName uddtMiddleName=NULL,
> @.lastName uddtLastName=NULL, @.socialSecurity uddtSocialSecurity = NULL,
> @.startDOB datetime = NULL, @.endDOB datetime = NULL,
> @.birthCounty uddtBirthCounty=NULL, @.birthCountry uddtBirthCountry=NULL,
> @.birthState uddtBirthState=NULL)
> RETURNS TABLE
> AS
> RETURN
> (
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(@.firstName, FirstName) AND (MiddleName LIK
E
> ISNULL(@.middleName, MiddleName) OR MiddleName = @.middleName)
> AND LastName LIKE ISNULL(@.lastName, LastName) AND (SocialSecurity LI
KE
> ISNULL(@.socialSecurity, SocialSecurity) OR SocialSecurity = @.socialSecurit
y)
> AND (BirthCounty LIKE ISNULL(@.birthCounty, BirthCounty) OR BirthCoun
ty
> = @.birthCounty) AND (BirthCountry LIKE ISNULL(@.birthCountry, BirthCountry)
OR
> BirthCountry = @.birthCountry)
> AND (BirthState LIKE ISNULL(@.birthState, BirthState) OR BirthState =
> @.birthState)
> )
> running this statement:
> set ansi_nulls off
> SELECT personId FROM
> fn_usrsearchpersontest(null,null,'gawris
ch',null,null,null,null,null,null)
> does not return the right set if some of the records have nulls in the
> corresponding fields addressed in the table function that has 'OR' as part
of
> the condition (i.e. social security)
> Yet if I run this in studio it return the correct number of results
> set ansi_nulls off
> SELECT Id as PersonId FROM Persons
> WHERE FirstName LIKE ISNULL(null, FirstName) AND (MiddleName LIKE
> ISNULL(null, MiddleName) OR MiddleName = null)
> AND LastName LIKE ISNULL('funke', LastName) AND (SocialSecurity LIKE
> ISNULL(null, SocialSecurity) OR SocialSecurity = null)
> AND (BirthCounty LIKE ISNULL(null, BirthCounty) OR BirthCounty = nul
l)
> AND (BirthCountry LIKE ISNULL(null, BirthCountry) OR BirthCountry = null)
> AND (BirthState LIKE ISNULL(null, BirthState) OR BirthState = null)
> thoughts? Like I said if this is run in sql server 2000 the results are th
e
> same in either running the function or sql statement as above...I need to
get
> this resolved for a migration|||Hi Blad,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you use Inline
table to query some data under SQL Server 2005, but it returns the
different result with that the sql statement executed in Management Studio.
And also the same SQL statement return the same results in SQL Server 2000.
If I have misunderstood your concern, please feel free to point it out.
Since you defined this function on a specific database and you use
User-defined Data type in your table, I can not re-pro it on my own
environment. So for narrowing down the question, would you like to give me
some more information?
1. Which edition of SQL Server 2005 do you use?
2. What is the difference between the results of those 2 method? Does
the Inline table return all the record with NULL value in those
corresponding column?
3. Do you have any other function to query the table? If so, does this
issue happen on those function?
4. If you create a simple function use Inline table ( with less
criteria ) to query the table, does this issue happen?
If there are more information on the issue, please feel free to let us
know. Have a great day!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.|||Here is a quote from Books Online:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure.
Whenever the stored procedure is subsequently executed,
the setting of SET ANSI_NULLS is restored to its originally
used value and takes effect. When invoked inside a stored
procedure, the setting of SET ANSI_NULLS is not changed.
In this aspect, views and functions are also treated like stored
procedures. I guess you have created the function when SET ANSI_NULLS
was ON, so whenever you execute the function, it considers this
setting, regardless of the current state of SET ANSI_NULLS when the
function is invoked.
You can re-create the function with SET ANSI_NULLS OFF and it should
behave as you expect. However, I would not use SET ANSI_NULLS OFF and I
would modify the function to use conditions like this:
[...]
AND (BirthCountry LIKE @.birthCountry
OR BirthCountry = @.birthCountry
OR @.birthCountry IS NULL)
[...]
Razvan