log backup overwrite: function

Showing posts with label function. Show all posts

Thursday, March 29, 2012

Does SQLSERVER support fuzzy text searching(like the function of agrep).

Does SQLSERVER support fuzzy text searching(like the function of agrep).
For example, given the input keyword [homogenos] and similarity
parameter [2 characters], the function will find out valid result from
datasource by either replacing,inserting or deleting upto two different
characters from word [homogenos].
Both homogenooos(homogeno[+oo]s) and homogeos(homoge[-n]os are valid
result.
thx.No, not directly. You need to build a function that does Levenstein Edit
distance. You will find an implementation of this in the Fuzzy functions
which ship with SSIS in SQL 2005.
You can also use the expansion options in the thesaurus capabilities in
FullText search so a search on homogenos could be expanded to search on
homogenos homogenoos, homogeneous or homogeos, but you have to know in
advance what all the expansions might be and hard code them into your
thesaurus file.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zlf" <zlfcn@.hotmail.com> wrote in message
news:uGyRG55YGHA.4944@.TK2MSFTNGP02.phx.gbl...
> Does SQLSERVER support fuzzy text searching(like the function of agrep).
> For example, given the input keyword [homogenos] and similarity
> parameter [2 characters], the function will find out valid result from
> datasource by either replacing,inserting or deleting upto two different
> characters from word [homogenos].
> Both homogenooos(homogeno[+oo]s) and homogeos(homoge[-n]os are valid
> result.
> thx.
>

Does SQLSERVER support fuzzy text searching(like the function of a

functions are
SOUNDEX
DIFFERENCE
For freetext, try contains clause,
check CONTAINS in BOL
I hope this was what u were asking forThank you for your reply.
However, as I know, CONTAINS does not support the kind of fuzzy searching
described in my post.
And it is very likely that it does support it and I do not know how to use
it.
Can u show my a sample? Thank you again.
-- Original Message --
From: "Omnibuzz" <Omnibuzz@.discussions.microsoft.com>
Newsgroups: microsoft.public.sqlserver.programming
Sent: Wednesday, April 19, 2006 7:00 PM
Subject: RE: Does SQLSERVER support fuzzy text searching(like the function
of a
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> д?:43C1F9E4-4CA3-478D-9064-82AA8D
9DA35D@.microsoft.com...
> functions are
> SOUNDEX
> DIFFERENCE
> For freetext, try contains clause,
> check CONTAINS in BOL
> I hope this was what u were asking for|||Thank you for your reply.
However, as I know, CONTAINS does not support the kind of fuzzy searching
described in my post.
And it is very likely that it does support it and I do not know how to use
it.
Can u show my a sample? Thank you again.
-- Original Message --
From: "Omnibuzz" <Omnibuzz@.discussions.microsoft.com>
Newsgroups: microsoft.public.sqlserver.programming
Sent: Wednesday, April 19, 2006 7:00 PM
Subject: RE: Does SQLSERVER support fuzzy text searching(like the function
of a
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> д?:43C1F9E4-4CA3-478D-9064-82AA8D
9DA35D@.microsoft.com...
> functions are
> SOUNDEX
> DIFFERENCE
> For freetext, try contains clause,
> check CONTAINS in BOL
> I hope this was what u were asking for

Does sql server has BIF like sleep()?

Dear all:
Does sql server has BIF like vb sleep()?
I want to use this kind of function to balance the sql server loading,
or do you have better way to implement this task?
thanks for your kindly assistance
regards,
Stanley HuangIt has WAITFOR.. but honestly, if you think you can write code that handles load better than the engine, you are a brave man....|||Originally posted by byrmol
It has WAITFOR.. but honestly, if you think you can write code that handles load better than the engine, you are a brave man....
dear byrmol:
Thanks for your reply.
As the matter of the fact, because I have a data transfer from text file to db, and the file is very big.
Then it spend me a lot of time to insert, and at the moment, the client still query the db.
That's why I want to split the process, or the user will complain about the query performance.
Would you please advise me if sql server can set the priority level of specific account, if I can down grade the priority of data transfer program, then I will not use the waitfor function to control the loading.

regard for your reply.

and thanks for your kindly assistance again... ^^

Stanley Huang

Tuesday, March 27, 2012

Does SQL have a function that return "null" for records which dont exist in a FK realation

Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.

Thank you very much.

when doing select with 'join's over two tables it would return null.

For example, you have customers and customerdetails table

if you do

select *

from customers

left join customerdetails on customers.customerId = customerdetails.customerId

if there is no customer details for a customer the fields corresponding to the customer details table would be null.

Check the different types of joins 'left join', 'inner join', 'outer join', 'cross join'...i think that's all :)

Cheers,

Yani

|||

Thank you very much indeedYani Dzhurov.

Wednesday, March 21, 2012

Does Reporting service for SQL server work with SQL trial version

Does Reporting service for SQL server work with SQL trial version?
Need to know in advance if it provide a function before buying SQL serverit does work with enterprise edition eval
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"sabot" <sab0t777@.yahoo.com> wrote in message
news:ffe9912.0312181131.1e6f1f09@.posting.google.com...
> Does Reporting service for SQL server work with SQL trial version?
> Need to know in advance if it provide a function before buying SQL server|||Tried installing Report Service beta2. The setup gave this error
message
"This edition of the Report Server Database is not supported on the
edition of SQL Server 2000 you have chosen. Please choose another SQL
Server that matches the requirements."
What am I doing wrong?
running SQL Server 2000 Eval - 8.00.760 (SP3) on Win2000 AS
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sunday, March 11, 2012

does Level() do anything?

enclosed is the complete rdl for a report that has a table, contains groups
and calls the level function.
Returns the current level of depth in a recursive hierarchy. it looks like
the recursive part does not work?
what am I missing here?
<?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>
<Table Name="table1">
<Height>1.25in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>group name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="unique_name">
<Style>
<PaddingLeft>30pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>unique_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!unique_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif( Fields!unique_name.Value is Nothing,
"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>6.83334in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="group_name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!group_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="sub_group_name">
<Style>
<PaddingLeft>10pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>sub_group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!sub_group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox18</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!group_id.Value</Parent>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="family_name">
<Style>
<PaddingLeft>20pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>family_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!family_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox23</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif(Fields!family_name.Value=Nothing,"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox24">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox24</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group3">
<GroupExpressions>
<GroupExpression>=Fields!family_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!sub_group_id.Value</Parent>
</Grouping>
<Visibility>
<Hidden>= iif(Fields!family_name.Value=Nothing,True,False)</Hidden>
</Visibility>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="datasource">
<rd:DataSourceID>6f542575-d325-4bbe-8889-0ce5d6439aa6</rd:DataSourceID>
<DataSourceReference>datasource</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.95833in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="group_name">
<DataField>group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="group_id">
<DataField>group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="sub_group_name">
<DataField>sub_group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="sub_group_id">
<DataField>sub_group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="family_name">
<DataField>family_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="family_id">
<DataField>family_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_id">
<DataField>unique_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_name">
<DataField>unique_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>datasource</DataSourceName>
<CommandText>select 'group 1' as group_name, 1 as group_id, 'sub
group 1' as sub_group_name, 1 as sub_group_id, null as family_name, null as
family_id, null as unique_id, null as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
1 as unique_id, '1' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
2 as unique_id, '2' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
3 as unique_id, '3' as unique_name
union
select 'group 2' as group_name, 2 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
4 as unique_id, '4' as unique_name</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>eec2b812-624b-4368-8fcc-a696fe0d2f30</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>Level function is for recursive hierarchies, which are tables that link back
to themselves. If you create a table such as...
CategoryID CategoryParent
1 0
2 1
3 1
4 0
5 4
6 4
7 4
8 2
9 2
10 2
11 9
12 10
13 12
and set your query to be
Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
Then you could use the level function to tell you how far each category node
is nested.
"letuce dance" wrote:
> enclosed is the complete rdl for a report that has a table, contains groups
> and calls the level function.
> Returns the current level of depth in a recursive hierarchy. it looks like
> the recursive part does not work?
> what am I missing here?
> <?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>
> <Table Name="table1">
> <Height>1.25in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>group name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="unique_name">
> <Style>
> <PaddingLeft>30pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>unique_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!unique_name.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>= iif( Fields!unique_name.Value is Nothing,
> "Null","Not Null")</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <Width>6.83334in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="group_name">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!group_id.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="sub_group_name">
> <Style>
> <PaddingLeft>10pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>sub_group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!sub_group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox17</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox18</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> </GroupExpressions>
> <Parent>=Fields!group_id.Value</Parent>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="family_name">
> <Style>
> <PaddingLeft>20pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>family_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!family_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox23">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>|||ya I think I provided that example? an example that shows level() does not
work?
"Jace" wrote:
> Level function is for recursive hierarchies, which are tables that link back
> to themselves. If you create a table such as...
> CategoryID CategoryParent
> 1 0
> 2 1
> 3 1
> 4 0
> 5 4
> 6 4
> 7 4
> 8 2
> 9 2
> 10 2
> 11 9
> 12 10
> 13 12
> and set your query to be
> Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> Then you could use the level function to tell you how far each category node
> is nested.
>
> "letuce dance" wrote:
> > enclosed is the complete rdl for a report that has a table, contains groups
> > and calls the level function.
> >
> > Returns the current level of depth in a recursive hierarchy. it looks like
> > the recursive part does not work?
> >
> > what am I missing here?
> >
> > <?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>
> > <Table Name="table1">
> > <Height>1.25in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>14</ZIndex>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>group name</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="unique_name">
> > <Style>
> > <PaddingLeft>30pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>unique_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!unique_name.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>= iif( Fields!unique_name.Value is Nothing,
> > "Null","Not Null")</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox6">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>textbox6</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>DataSet1</DataSetName>
> > <Width>6.83334in</Width>
> > <TableGroups>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="group_name">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>10</ZIndex>
> > <rd:DefaultName>textbox11</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>9</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="sub_group_name">
> > <Style>
> > <PaddingLeft>10pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!sub_group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox17">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>7</ZIndex>
> > <rd:DefaultName>textbox17</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox18">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox18</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group2">
> > <GroupExpressions>
> > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > </GroupExpressions>
> > <Parent>=Fields!group_id.Value</Parent>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>|||What query in your example is joined back to itself? I didnt see one. I
believe you have to have this for the level function to work.
"letuce dance" wrote:
> ya I think I provided that example? an example that shows level() does not
> work?
> "Jace" wrote:
> > Level function is for recursive hierarchies, which are tables that link back
> > to themselves. If you create a table such as...
> >
> > CategoryID CategoryParent
> > 1 0
> > 2 1
> > 3 1
> > 4 0
> > 5 4
> > 6 4
> > 7 4
> > 8 2
> > 9 2
> > 10 2
> > 11 9
> > 12 10
> > 13 12
> >
> > and set your query to be
> > Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> > outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> >
> > Then you could use the level function to tell you how far each category node
> > is nested.
> >
> >
> > "letuce dance" wrote:
> >
> > > enclosed is the complete rdl for a report that has a table, contains groups
> > > and calls the level function.
> > >
> > > Returns the current level of depth in a recursive hierarchy. it looks like
> > > the recursive part does not work?
> > >
> > > what am I missing here?
> > >
> > > <?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>
> > > <Table Name="table1">
> > > <Height>1.25in</Height>
> > > <Style />
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox1">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>14</ZIndex>
> > > <rd:DefaultName>textbox1</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>group name</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox2">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>13</ZIndex>
> > > <rd:DefaultName>textbox2</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox3">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>12</ZIndex>
> > > <rd:DefaultName>textbox3</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Details>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="unique_name">
> > > <Style>
> > > <PaddingLeft>30pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>2</ZIndex>
> > > <rd:DefaultName>unique_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=Fields!unique_name.Value</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox5">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>1</ZIndex>
> > > <rd:DefaultName>textbox5</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>= iif( Fields!unique_name.Value is Nothing,
> > > "Null","Not Null")</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox6">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <rd:DefaultName>textbox6</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Details>
> > > <DataSetName>DataSet1</DataSetName>
> > > <Width>6.83334in</Width>
> > > <TableGroups>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="group_name">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>11</ZIndex>
> > > <rd:DefaultName>group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox11">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>10</ZIndex>
> > > <rd:DefaultName>textbox11</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox12">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>9</ZIndex>
> > > <rd:DefaultName>textbox12</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group1">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > > </GroupExpressions>
> > > </Grouping>
> > > </TableGroup>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="sub_group_name">
> > > <Style>
> > > <PaddingLeft>10pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>8</ZIndex>
> > > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!sub_group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox17">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>7</ZIndex>
> > > <rd:DefaultName>textbox17</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox18">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>6</ZIndex>
> > > <rd:DefaultName>textbox18</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group2">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > > </GroupExpressions>

Friday, March 9, 2012

Does function get evaluated twice if used as criteria?

Hello
This newsgroup seems to have deleted my previous messages?
So thanks to Louis here for previous help. I am now getting the resultsets i
want. Another question:
For the select query below, does the function dbo.ACEOr get evaluted once or
twice? I.e is the reference to db.ACEOr evaluated again in the "WHERE"
criteria or does the sql server engine just treat it as a variable and
recognise that its already been evaluated in the SELECT DISTINCT part of the
query.
SELECT DISTINCT tblSalesTerritory.ACEId, dbo.ACEOR(tblSalesTerritory.ACEId,
@.Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
@.Authority) >0
There is no noticable performance hit at the moment but i expect i should
know this for future reference.
Thanks.
IanIan,
Something is missing in the statement.

> SELECT DISTINCT tblSalesTerritory.ACEId, dbo.ACEOR(tblSalesTerritory.ACEId
,
> @.Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
> @.Authority) >0
SELECT DISTINCT
tblSalesTerritory.ACEId,
dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) AS AuthLevel
from
tblSalesTerritory
WHERE
dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) > 0
go
I think that the function will be evaluated one time per each row to filter
the rows based on the where clause and one time per each row that passed the
filter.
You can verify this with the execution plan or you can create a dummy stored
procedure, include the statement in the sp and then debug the sp. Do the
debug with few rows.
Example:
use northwind
go
create function dbo.ufn_f1 (
@.orderid int
)
returns int
as
begin
return (@.orderid)
end
go
set showplan_text on
go
select orderid, customerid, orderdate, dbo.ufn_f1(dbo.orders.orderid) as c1
from dbo.orders
where dbo.ufn_f1(dbo.orders.orderid) between 10250 and 10260
go
set showplan_text off
go
drop function dbo.ufn_f1
go
Result:
StmtText
----
---
|--Compute
Scalar(DEFINE:([Expr1002]=[dbo].[ufn_f1](Convert([Orders].[OrderID]))))
|--Filter(WHERE:([dbo].[ufn_f1](Convert([Orders].[OrderID]))>=10250
AND [dbo].[ufn_f1](Convert([Orders].[OrderID]))<=10260))
|--Clustered Index
Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]))
AMB
"Ian Evitable" wrote:

> Hello
> This newsgroup seems to have deleted my previous messages?
> So thanks to Louis here for previous help. I am now getting the resultsets
i
> want. Another question:
> For the select query below, does the function dbo.ACEOr get evaluted once
or
> twice? I.e is the reference to db.ACEOr evaluated again in the "WHERE"
> criteria or does the sql server engine just treat it as a variable and
> recognise that its already been evaluated in the SELECT DISTINCT part of t
he
> query.
> SELECT DISTINCT tblSalesTerritory.ACEId, dbo.ACEOR(tblSalesTerritory.ACEId
,
> @.Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
> @.Authority) >0
> There is no noticable performance hit at the moment but i expect i should
> know this for future reference.
> Thanks.
> Ian
>
>|||Kalen will probably know if the optimizer is always smart enough to
factor out a common sub-expression.
In standard SQL/PSM, you can declare a procedure to be DETERMINISTIC or
not, so the optimizer can take appropriate action. A non-deterministic
function has to be re-evaluated for each occurence and they can be a
problem to optimize.|||On Sat, 10 Sep 2005 22:56:30 +1200, Ian Evitable wrote:

>Hello
>This newsgroup seems to have deleted my previous messages?
Hi Ian,
You mean the two cursor-related questions that you posted some five
hours before this message? They are in the group, together with some
replies.
(snip)
>For the select query below, does the function dbo.ACEOr get evaluted once o
r
>twice?
Twice.
To prevent this, use a derived table:
SELECT DISTINCT ACEId, AuthLevel
FROM (SELECT ACEId,
dbo.ACEOR(ACEId, @.Authority) AS AuthLevel
FROM tblSalesTerritory) AS der
WHERE AuthLevel > 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello,
Yes your right i must have screwed up the cut and paste. I meant to just rip
out the selected fields because they weren't really pertinent to the
question but it looks like i ripped out the FROM source as well.
Ian
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:0265144C-7714-4675-9B16-CA4102B8CA38@.microsoft.com...
> Ian,
> Something is missing in the statement.
>
dbo.ACEOR(tblSalesTerritory.ACEId,
> SELECT DISTINCT
> tblSalesTerritory.ACEId,
> dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) AS AuthLevel
> from
> tblSalesTerritory
> WHERE
> dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) > 0
> go
> I think that the function will be evaluated one time per each row to
filter
> the rows based on the where clause and one time per each row that passed
the
> filter.
> You can verify this with the execution plan or you can create a dummy
stored
> procedure, include the statement in the sp and then debug the sp. Do the
> debug with few rows.
> Example:
> use northwind
> go
> create function dbo.ufn_f1 (
> @.orderid int
> )
> returns int
> as
> begin
> return (@.orderid)
> end
> go
> set showplan_text on
> go
> select orderid, customerid, orderdate, dbo.ufn_f1(dbo.orders.orderid) as
c1
> from dbo.orders
> where dbo.ufn_f1(dbo.orders.orderid) between 10250 and 10260
> go
> set showplan_text off
> go
> drop function dbo.ufn_f1
> go
> Result:
>
> StmtText
> ----
----
> |--Compute
> Scalar(DEFINE:([Expr1002]=[dbo].[ufn_f1](Convert([Orders].[OrderID]))))
> |--Filter(WHERE:([dbo].[ufn_f1](Convert([Orders].[OrderID]))>=10250
> AND [dbo].[ufn_f1](Convert([Orders].[OrderID]))<=10260))
> |--Clustered Index
> Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]))
>
> AMB
> "Ian Evitable" wrote:
>
resultsets i
once or
the
dbo.ACEOR(tblSalesTerritory.ACEId,
should|||
> You mean the two cursor-related questions that you posted some five
> hours before this message? They are in the group, together with some
> replies.
>
Yes. I can see them in Google newsgroups but not via my Out look express
anymore? Weird.

>To prevent this, use a derived table:

>SELECT DISTINCT ACEId, AuthLevel
>FROM (SELECT ACEId,
> dbo.ACEOR(ACEId, @.Authority) AS AuthLevel
> FROM tblSalesTerritory) AS der
>WHERE AuthLevel > 0
Ahah. Ok great. That works nicely.
T-SQL's actually quite powerful and allows a reasonably high level of DAL
encapsulation/blackboxing if used correctly. Im genuinely chuffed to be able
to keep this "logic" at the DAL rather than pulling into the middle tier and
using 3-4GL langauges to do the job.
Thanks to all for the tips.
Ian|||On Sun, 11 Sep 2005 18:48:17 +1200, Ian Evitable wrote:

>
>Yes. I can see them in Google newsgroups but not via my Out look express
>anymore? Weird.
Hi Ian,
Off topic for this group, but...
Maybe you accidentally deleted your messages from the Outlook archive?
Or maybe Outlook deleted them as a result of some setting - check Extra
/ Options / Maintenance (I'm not sure if these are the exact names of
the options, as I'm translating from the Dutch version of OE) and check
how long you have chosen to keep the newsgroup messages in OE.
Or better yet: upgrade to a better news reader! <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Or maybe Outlook deleted them as a result of some setting
If you mean the dumb question / dumb response setting you're probably right.
Outlooks clearly trying to save me from myself.
:)

Does DATEADD change the datatype?

I have an odd issue (mainly revolving around proper index usage.)
For some reason when I use a DATEADD function within a join the between or
>= , <= operators the optimizer ignores the index and performs a full table
scan.
Example
This will NOT use the INDEX
--code start
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
DATEDIFF(MONTH, -1, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0,
GETDATE()))
--code end
Basically the query will return dates today and a 1 month ago. which works
fine but in looking at the query analyzer I find the optimizer is not using
the index where as if i had used a hard coded date or DATEADD(DAY, 0,
DATEDIFF(DAY, 0, GETDATE())) without supplying DATEADD range.
This WILL use the INDEX
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
DATEDIFF(DAY, 0, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()
))
OR
Select * from Time_Dimension where Date_Number BETWEEN '2/1/2005' AND
'2/28/2005'
OR EVEN.. ( AND THIS ONE IS WEIRD)
Select * from Time_Dimension where Date_Number DATEADD(DAY, 1, DATEDIFF(DAY,
0, GETDATE()))
Any Thoughts
ThanksCheck out what selectivity the optimizer estimates and also if you see an CO
NVERT on the column
side.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3036BF48-0920-4A8C-857D-10337A8C593D@.microsoft.com...
>I have an odd issue (mainly revolving around proper index usage.)
> For some reason when I use a DATEADD function within a join the between or
> scan.
> Example
> This will NOT use the INDEX
> --code start
> Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
> DATEDIFF(MONTH, -1, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0,
> GETDATE()))
> --code end
> Basically the query will return dates today and a 1 month ago. which works
> fine but in looking at the query analyzer I find the optimizer is not usin
g
> the index where as if i had used a hard coded date or DATEADD(DAY, 0,
> DATEDIFF(DAY, 0, GETDATE())) without supplying DATEADD range.
> This WILL use the INDEX
> Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0,
> DATEDIFF(DAY, 0, GETDATE())) AND DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE
()))
> OR
> Select * from Time_Dimension where Date_Number BETWEEN '2/1/2005' AND
> '2/28/2005'
> OR EVEN.. ( AND THIS ONE IS WEIRD)
> Select * from Time_Dimension where Date_Number DATEADD(DAY, 1, DATEDIFF(DA
Y,
> 0, GETDATE()))
> Any Thoughts
> Thanks
>

Wednesday, March 7, 2012

Does bit data type function as boolean?

Hi,

I have some applications working fine with Sql Server 7 and Jet 4.
I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:

These query worked fine in Sql Server 7:

"Select <fields> from <table> where <boolean/bit field>"

Now I have to do this way:

"Select <fields> from <table> where <bit field> = 1"

Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.

Does anyone know why this is happening? Does Sql Server work different than older versions?

Thanks!

Fabianohttp://dbforums.com/arch/43/2002/4/343538

Originally posted by fabiano
Hi,

I have some applications working fine with Sql Server 7 and Jet 4.
I'm doing some tests with Sql Server 2000 to port these applications to it. I'm having some troubles to query tables using a bit field. For example:

These query worked fine in Sql Server 7:

"Select <fields> from <table> where <boolean/bit field>"

Now I have to do this way:

"Select <fields> from <table> where <bit field> = 1"

Why does this happen? Sql Server 2000 doesn't recognize a boolean value anymore? It's easier and more comprehensive (to me) to use the first way.

Does anyone know why this is happening? Does Sql Server work different than older versions?

Thanks!

Fabiano|||nice thread, sbaru

especially joe celko's advice not to use BIT

did you notice that no one actually answered the question "...so the correct, non-proprietary datatype for representing a boolean value is...?"

fabiano, your question "Sql Server 2000 doesn't recognize a boolean value anymore?" is not quite accurate -- i don't think it ever did

if you can change the datatype, you might consider a numeric field like TINYINT or INTEGER instead of BIT

see also Booleans in SQL Server (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid556536_tax285649,00.html)

rudy|||Thanks Rudy and Sbaru,

I've been advised to use tinyint (or some integer variant) to do it. I have some doubts about use tinyint instead of bit to represent a boolean type as follows:

1. Didn't bit type created to represent boolean values? Why does it exist? It what kind of situation am I going to use it?

2. Is bit an optimized type to work with?

3. I know it doesn't make sense to summarize or group by a bit field but it's very common to use this type of field to count records in a table or query by it.

Ex:
Employees that have dependents:

"Select Cod_emp from Employees Where Dep_emp"

Count the number of these employees:

"Select Count(Cod_emp) from Employees Where Dep_emp"

These queries above are common in some situations.

Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read.

I have many queries this way. If I change the bit types to tinyint will they work fine or I will have to change the structure of all my queries?

Thanks for your time!

Fabiano|||1) no, BIT was created to store bits

2) depends what you mean by "optimized"

3) "Note that I don't need to explicit "Dep_emp = 1" Or "Dep_emp = 0", it's just "Dep_emp". The query is clearer to read."

whether it is clearer to read is a matter of opinion

"Dep_emp" by itself means nothing to me

"Dep_emp = 1" means nothing to me either

if the purpose of the Dep_emp column is to indicate whether the employee belongs to the department, then i suggest that it is not at all clear, and in fact is poor design

but yes, you are going to have to change the structure of all your queries

rudy|||It doesn't work that way..

USE Northwind
GO

CREATE TABLE myTable99 (col1 bit)
GO

INSERT INTO myTable99(Col1) SELECT 1 UNION ALL SELECT 0
GO

--Doesn't work
SELECT * FROM myTable99 WHERE Col1 TRUE
--Works
SELECT * FROM myTable99 WHERE Col1 = 1
GO

DROP TABLE myTable99
GO

And what is dep_emp...it must be predefined somewhere..

Also it sounds like all of your SQL code is in the app...now would be a good time to turn them in to stored procedures|||I understood. I'm going to change some queries in my applications.

I've already have most of my queries in stored procedures and views, and these queries use boolean fields in criteria search the way I've described.

Returning to those doubts I've mentioned:

1) Yes. Bit fields were created to store bits. But bit value seems to use in boolean cases. When do I need to store pure bits? If the type was byte instead of bit it would make sense, but bit? Bit value accepts only 0 or 1, True or False, Yes or No or some variance of it. That's why I've thought in use it to store this type of value. But it's ok! I've understood that it won't work this way.

2) When I've mentioned "optimized" I wanted to know if Sql Server treat this type of field in a different way. Do you know if it proceeds?

3) The field "Dep_emp" was not the better example. Think in a gender field to store "male" or "female" where "true" could be "female" and so on. In this case a query like "Select Name_cus from Customers Where Gen_cus" would return just the females ones in a case of a promotion for the female customers for example. In this case, untill now, I didn't have to explicit "Where Gen_cus = 1". I've been used just "Where Gen_cus" that it seems to be clearer once you think in "boolean way".

My doubt is why did MS change the way of treatment of bit fields in Sql Server 2000? Any other DBMS or older versions of Sql Server recognizes the ANSI-SQL "Where <boolean field>" without the need of explicit the true or false value. Once it's a boolean field it doesn't make sense to explicit the value, right? It's true or false, nothing more. I thought this type of field was created to treat these situations in a "optimized" way, even just to clear the queries.

This way of treatment of boolean values helps us even more than this. In a table that stores the quantity of dependents of a employee (like children, parents, ...) you will be able to query for employees that have dependents just using the boolean way, for example:

"Select Cod_emp from Employess Where Dependents_emp"

Or

"Select Cod_emp from Employees Where Not Dependents_emp"

Note that I don't need to explicit "Where Dependents_emp > 0" or "Where Dependents_emp = 0". I don't need, in this case, to know how many dependents each employee has, just to know who they are. There're many reasons for this kind of query. This way of query worked not just in bit fields but in all numeric fields. Any value different than 0 was treated as true.

The biggest problem is that I'll need to separate my apps in 2 versions. One for any DBMS and one just for Sql Server 2000. I'm not worried about the performance using a "native" Sql of each DBMS. I'm worried about the portability of my apps to any DBMS, that's why I often use ANSI-SQL, even because the performance boost is not too sensitive.

Thanks again!

Fabiano|||i could be wrong, but i don't think sql server has ever supported boolean

it goes against everything we've come to expect from microsoft, that they would have a standard feature in an early database and then stop supporting it -- that just doesn't make sense

as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean...

"Any value different than 0 was treated as true" ?

that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know, and all i can say is, i hope i never have to support one of your apps, because that is certainly counter-intuitive

at least, to me ;)|||Hi Rudy,

Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.

It seems to me that you got angry about anything I've said in your words:

"...whether it is clearer to read is a matter of opinion
Dep_emp by itself means nothing to me
Dep_emp = 1 means nothing to me either..."

That's not possible that you couldn't see the mean of these codes. It's very clear. You don't need to know the total structure of the project to suppose that it's a query that brings rows where "Dep_emp" is "true". Just it!

--

"...and in fact is poor design."

How can you say something like that without the knowledge enough about the project?

--

"i could be wrong, but i don't think sql server has ever supported boolean..."

Yes. You're wrong. The bit field was treated as boolean untill Sql Server 7. My apps stopped to work only when I upgraded to Sql Sever 2000.

--

"as far as your rather creative way of using an integer such as number of dependents in a WHERE clause as a boolean..."

The more experience more creativity, indeed.

--

"Any value different than 0 was treated as true ?
that may be the way it works in mysql, but it certainly is not standard behaviour, as far as i know..."

I think you don't have a basic knowledge about another RDBMS than Sql Server 2000. This behavior has been standard at least in Jet, Sql Server (untill 7), MySql and Postgre SQL. I'm working with all these ones and I can say that they work fine.

--

"...i hope i never have to support one of your apps, because that is certainly counter-intuitive."

Yes. You're totally right. My apps need more "creativity" and I think you've not got it enough.

--

Please take a good look at the links below (they're all from Microsoft as you can see):

http://support.microsoft.com/default.aspx?scid=kb;en-us;129803
"0=False, non-zero=True"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vadatBoolean.asp
"When numeric data types are converted to Boolean values, 0 becomes False and all other values become True."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_7uzy.asp
"Converting to bit promotes any nonzero value to 1."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
"Microsoft SQL Server optimizes the storage used for bit columns."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledbsql/9_ole_13_7s6r.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledbsql/9_ole_13_10ha.asp
*Sqloledb mapps bit data type to boolean data type according to the table shown.

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/trans/ch20.asp
"Efficient datatypes

...Narrow columns make narrow tables, and narrow tables allow you to store more rows on a single data page...

...If your table contains a series of flags with Yes/No or False/True type of information, the best datatype for such columns is BIT. This type is especially effective when you have several columns of type BIT in a table..."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbls7/html/vblrfvbspec10_2a.asp
"...A zero numeric value converts to the literal False. All other numeric values convert to the literal True."

--

You can see that I've followed the MS' suggestions. That's why my disappointment with the change of treatment of the bit type.

I'm not saying Sql Server is a bad RDBMS. I use it for a long time and I like its performance and stability. I often suggest it for my customers. Then you don't need to get personally offended. It was not intension. Sorry Rudy.

Fabiano
IT Solution Engineer|||fabiano, i was not angry, and i am not angry now

i've been working with databases since the mid '70s, and i do have plenty of experience with them

what is "creative" to you may not seem so to someone else

you are entitled to your opinion, and i am entitled to mine, which i have arrived at after working with many different databases for many decades

i have plenty of creativity, my friend

good luck to you sir

rudy

p.s. you say sql server 7 "treated" BIT as BOOLEAN, but that's not the same thing as supporting BOOLEAN|||Come on Rudy, admit it! Everything you know you learned by scanning the back cover of "Databases For Dummies" at Barne's And Noble!

It's time to face the music!

:p|||sheeeeeesh

flame wars...

Why are people soooooooo sensitive...

Is it the ability to lash out in anonymity ?

And fabiano...chill

Please forgive me if it seemed that I've been rude or arrogant. Believe, I was not intended to. My english is very poor and I don't know how to expressing my words very well. I've been just "translating" my "portuguese thoughts" in "english words" and I suppose you got me wrong.

That translating part I find goes both ways...with the expression, and the interpretation...

Take everything with a grain of salt...nobody at the sites (well with the exception of a few) are not here, not to help...

Now back to the thing...

Where is your code stored? In an Access front end? VB? Stored procedures?

And Can you post the DDL and or code so we can have a look?

Maybe only a Blindman (get the pun) can interpret something we haven't seen.

I'm very curious as to what you have there (It's the only way we learn too)

Thanks|||How I was asking for help (I know nobody here is obligated to, it's just a forum) and it seemed Rudy didn't like my insistence in some points. I was testing nobody, just debating to see if I could do something not to need changing my whole code.

I have some codes in sp, views, using the execute method of ado in vb and some dlls in mts. The job will be enormous considering all projects and tecnologies involved with.

I think I don't need to post any part of the code. The doubt is simple. Once it doesn't matter if I'm querying from a sp, an execute method or view, Sql Server will be always the "interpreter". If the query was "Select <something> from <some table> Where <some field>", it would be interpreted the same way, independently the method used.

I know "treat by" and "supporting" are different things, but the mean of it is the same. If I can have a condition search like "Where <bField>" and the RDBMS returns the records means that it treated the content of that field as boolean, even the RDBMS doesn't support boolean types (and that's it what have been done by earlier versions of Sql Server).

I don't have all of that experience because I began to work with projects design at the mid' 80s (10 years latter). Even so I think the time is not the only manner to measure the ability of a professional. There're plenty of professionals that don't worry about their evolution.

But it's ok. I've interpreted wrong all of those words I'm sure. Forgive me.

About the anonymity I think there's something wrong. Fabiano is my true name. Brasil, Gois, Goinia is my true physical location. If the problem is a telephone number here it goes: +55 (62) 259-7567 (Commercial. I'll be there from January, 5). I don't speak english very well (as you can see) but if we have patience I guess we can understand each other.

Fabiano
Entitled to my opinion: IT Solution Engineer|||fabiano, your english is fine, that was never the problem, in fact, your english is way better than my portuguese

;)

i understand your frustration, and all i can say is, you have learned a valuable lesson about creativity versus flexibility and maintainability

i practice what i call "defensive sql" and WHERE BITFIELD by itself is not very defensive, as you have found out

good luck with your upgrade

rudy|||Rudy,

You don't need to know portuguese once I came for help in a foreign forum (for me ;)). It's my obligation to know how to express my doubts, I know.

With the improvement of my english I can see clearly now. It was not your (or Brett's) fault, sorry. It was seemed to me that you've been ironic in your answers, but was not.

I respect your knowledge. Unfortunatelly the earlier versions of Sql Server did work the way I told, but it's not the problem. That sintax I've demonstrated is ANSI-SQL and not a "native" one.

I've been using ANSI-SQL thinking the same way of yours "defensive sql". To have not problems at all (unless the performance was critical, although it would be the last thing I was change).

I think that I'll need to call for mommy... :)

Fabiano|||I like Rudy's concept of "defensive SQL". Its certainly possible to be so creative in a search for the elegant solution that it becomes difficult for even experience SQL programmers to decipher your logic later on. What I refuse to do, though, is to dumb down my code so that it is readable by any VB developer that comes along. Efficiency and functionality are still of higher importance.

At least thats what I read in "SQL For Idiots".

blindman :cool:|||Fabiano,

Where's Goias?

Getting ready for New Years eve?

And is that really an ANSI Compliant SQL?

Anyone got a link to them?

Still dusting off my 6.5 book to take a look see...|||Originally posted by fabiano
That sintax I've demonstrated is ANSI-SQL and not a "native" one.i don't know if your syntax actually was ANSI

i think ANSI syntax requires you to say WHERE BOOLFIELD IS TRUE or WHERE BOOLFIELD IS FALSE

you left off the IS TRUE part ;)

(i could be wrong -- i don't have an intimate knowledge of the ANSI standard, and the Mimer SQL Validator (http://developer.mimer.se/validator/index.htm) appears to be down at the moment)

in any case, i do not see the value in using ANSI sql intended for one data type (BOOLEAN) on a different datatype (BIT)

i.e. WHERE BITFIELD IS TRUE wouldn't have worked with sql server 7 BIT fields anyway, right?

rudy|||Hey...pretty exotic...

http://www.brazil.travelmall.com/travelmall/region/Brasillia+and+Goias

Got any 6 month consulting gigs opening up?

Sounds like you'll need a hand or two...|||Hmmm. This is from that link:

Here are some points that will make your trip more pleasant:
1) Take good care in your personal hygiene and dress to avoid ridicule and to fit in with the locals.

Never been to good with that "dress to avoid ridicule" thing... ;-)|||I liked this one

Generally be wary of what you do to avoid offending or causing trouble.|||Hmm. They must have a big sign out front that says "No Americans allowed".|||HEY!

I resemble that remark!|||http://docs.zf.jcu.cz/sql1992.txt

"8.12 <search condition>
...
Format

<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ] "

* it seems to be optional to specify the truth value.

"...

Leveling Rules
1) The following restrictions apply for Intermediate SQL:
a) A <boolean test> shall not specify a <truth value>."

* here's a recommendation.

"...
24)Subclause 8.12, "<search condition>":
a) A <boolean test> shall not specify a <truth value>."

* that's the recommendation again.

"...
12.4 Calls to a <procedure>
...
TYPE;
type BIT is array (NATURAL range <>) of BOOLEAN;"

* here's saying that each bit is a boolean value.

----

This doc reproduces the entire content of ANSI-SQL-92 Standard.
How my english is not at all good, I've transported a part of the doc above to be interpreted.

What it seems to be clear is that BIT and BOOLEAN are synonymous.
This way Sql Server 7 seems to be ANSI-SQL-92 Compliant and Sql Server 2000 is not. I say that because Sql Server 7 works fine with my code, no problems.

For example. If you have a table like this:

Cus_id as Integer, Cus_Name as Varchar(50), Cus_gen as Bit

*Cus_gen: (0) male, (1) female

With some records like these:

1, Rudy, 0
2, Fabiano, 0
3, MCrowley (Ozzy Osbourne), 0
4, Brett Kaiser, 0
5, Suzy, 1

You can query females this way (Sql Server 7):

Select Cus_name Where Cus_gen

But using Sql Server 2000 you'll need to do this way:

Select Cus_name Where Cus_gen = 1

Once the value of "Cus_gen" is a boolean (or bit) you don't need to specify the boolean value, it doesn't make sense, it's true or false, nothing more.

For example, in VB (or any language) to do a loop through the records of a table you do something like that:

<open your recordset>

While Not adoRS.Eof

<do something>
adoRS.MoveNext

Wend

I do not need to specify:

While Not adoRS.Eof = True
or
While adoRS.Eof = False

Or a condition to verify whether a number is odd or even:

If X Mod 2 then
<even>
Else
<odd>
End If

I don't need to do:

If X Mod 2 <> 0 then

And so on. I've been used thus. This way we save a processor time taking out the need of the comparison "<> 0" or "=True" and, at the same time, you let your app smaller.

I don't remeber where I've read about these tips. I'll look for it later. I remember just something about "how you optimize your code" or something like that. I guess was at MS.com, don't remember.

In many cases the bandwidth of 100 Mbits of a huge LAN is not enough to provide a good performance, than we have to use the creativity to improve it. Worse when you working on an eCommerce. I use the standards to develop but I try to maintain my apps the minimun code as possible.

I'm not trying to explain my point of view. I'm trying to know if the way I work is actually a good way or if there's better ways to do it.

Thanks!

Fabiano|||congratulations on digging up the 1992 standard

again, all i can say is, too bad you have to rewrite all your code

want to know how i design male/female columns?

instead of

... Where Cus_gen

i prefer

... Where Cus_gen = 'F'

but that's just me, eh

rudy|||My e-mail is: fabianomaiafranco@.hotmail.com (it's my messenger passport too).

Thanks!!!

Fabiano
The Pretentious ;)