Sql Comparing two tables: following

Showing posts with label following. Show all posts

Thursday, March 29, 2012

Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sql sql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!
Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings togethe
r,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicon
s"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthlink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.sql sql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.

For example:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?

FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

Thanks!Would this work for you?

declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string

It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate Rows

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate Rows

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

Thank you all for your help.

Concatenate list of values under a group

I have the following dataset:

State ZIP Homes Schools

WA 98007 2000 4

WA 98052 3000 5

WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as “[98007, 98052, 98079]” how can I accomplish this.

My report needs to show:

State Homes Schools

[98007, 98052, 98079] 7000 12

Any help will be greatly appreciated

SPJ11, Why dont you do this in SQL itselves? You can also do this in SSRS. At the group level use,

Sum(Fields!Homes.value), Sum(Fields!Schools.value) -- these will retun the counts correctly.

I am not sure as how to concatnate the zip codes. I thought this should work

Join(Fields!Zip.Value) -- but this does not seem to work, I will see if I can come with something else...

|||

Never mind. I could make it work. Thanks to everyone who spent time on this thread

sql sql

Tuesday, March 27, 2012

Concatenatation with NULL

I am changing the setting od my database to put concatenate null yields null to off.....the following are the statements i run....

exec sp_dboption 'Solumina','concat null yields null','false'

SELECT 'abc' + NULL

I expect 'abc' to be returned after this....But not so..I get NULL

Can any one tell me this setting has to be changed at the connection level. if so, why has this been provided as a db option.

the following works....

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT 'abc' + NULL

abc

ODBC and SQL Query Analyzer will turn this ON by default so you need to explicitly turn the behavior OFF if you are using either of these connection mechanisms

Run this: select databaseproperty(''Solumina', 'IsNullConcat')

is the result 0? then it's set to OFF, however you have to change it from QA to make it wok in a query window

go to Tools-->options-->Connection Properties and uncheck set concat_null_yields_null

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Concat instead of SUM when grouping results

Hello,

I have a very simple problem which I will illustrate with an example:

I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K

I now want to group them and the result has to be:
A 1,2,3 C
B 8,9 K

So the results in the second row have to be concatenated. I guess
there is no function to do this... What is the simplest solution?

Kind regards,

Bart WarnezHi Bart,

I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:

CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))

INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'

SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3

DROP TABLE test

which seems to work :)

Good luck!
J|||On 23 nov, 12:52, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Bart,
>
I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:
>
CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))
>
INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'
>
SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3
>
DROP TABLE test
>
which seems to work :)
>
Good luck!
J

Hey, thank you very much, it works :). The only problem is that it
lasts more than 10 s to execute it and that with only 5 records :(.

Kind Regards,

Bart|||I have also tried out the solution below (with the same test-table),
with a function. But again the response time is very slow...

create function dbo.fn_groupIt(@.test1 varchar(5),@.test3 varchar(5))
returns varchar(5000)
as
begin
declare @.out varchar(5000)
select@.out = coalesce(@.out + ',' + convert(varchar,test2),
convert(varchar,test2))
fromtest
wheretest1 = @.test1 and
test3 = @.test3

return @.out
end

selecttest1, dbo.fn_groupIt(test1,test3) test2,test3
from(
selecttest1,test3
fromtest
group by test1,test3
) a|||Hi Bart,

What spec server are you using? I can run either script in under a
second :-/

J|||On 23 nov, 15:46, jhofm...@.googlemail.com wrote:

Quote:

Originally Posted by

Hi Bart,
>
What spec server are you using? I can run either script in under a
second :-/
>
J

Ok, I asked for another testserver because the first one was
apparently overloaded (read: dead). I didn't notice that at first
because a simple table-select took no time at all and those other
scripts took 10-20 seconds. On the new server, it takes no time...
Yes, you are right and I am happy :). Thank you very much!

Bart|||>I guess there is no function to do this... What is the simplest solution? <<

Do it in the front end instead violating 1NF in the Database side.|||On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

I guess there is no function to do this... What is the simplest solution? <<

>
Do it in the front end instead violating 1NF in the Database side.

Hi,

I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.

Kind regards,

Bart|||"Bart op de grote markt" <warnezb@.googlemail.comwrote in message
news:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...

Quote:

Originally Posted by

On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I guess there is no function to do this... What is the simplest
>solution? <<

>>
>Do it in the front end instead violating 1NF in the Database side.

>
Hi,
>
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

You're "wrong".

You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.

If you design your database properly, your queries follow from that.

Quote:

Originally Posted by

>
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
>

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

Quote:

Originally Posted by

>
Kind regards,
>
Bart

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||If you design your database properly, your queries follow from that.

This is nice in theory, but in practice I have seen many occasions
where reporting requirements simply don't align with the database
(which you often have no control over and may have been designed for
an input system for example). Short of designing a new database and
ETL'ing your data across (which there certainly is a market for but in
a lot of cases would be overkill to meet a single requirement),
sometimes you have to write "non-standard" queries.

Quote:

Originally Posted by

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?

J|||On 26 nov, 14:09, "Greg D. Moore $Strider$"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

"Bart op de grote markt" <warn...@.googlemail.comwrote in messagenews:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...
>

Quote:

Originally Posted by

On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

I guess there is no function to do this... What is the simplest
solution? <<

Quote:

Originally Posted by

Quote:

Originally Posted by

Do it in the front end instead violating 1NF in the Database side.

Quote:

Originally Posted by

Hi,

Quote:

Originally Posted by

I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.

>
You're "wrong".
>
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
>
If you design your database properly, your queries follow from that.

I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.

And ok, I did not design the database... it is a database from a new
application my company bought. (In fact it's about two databases from
two different applications that have to be linked in a report, but I
won't go too far to explain that :-) )

Quote:

Originally Posted by

Quote:

Originally Posted by

>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
>

As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.

Kind regards and thx for all your comments

Bart|||You're "wrong".

Actually Greg - You're "wrong".

SQL Server is a data engine and not just a relational data storage method.

There are lots and lots of extensions and features in SQL Server to help us
gain more performance, more simplicity instead of having to code stuff in
the middle tier all the time.

For instance, if I was writing a data export why on earth would I want to
use a second programming langauge that adds complexity when I can easily use
the functions and features in T-SQL.

There is a move more to putting business logic in the data engine rather
than just using the data engine as a put and get object - see research by
Jim Gray.

Quote:

Originally Posted by

Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?

It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

Sunday, March 25, 2012

Computed Fields Reuse

Hi,
I have the following query
SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
Access allows the calling of computed fields by using the column alias.
Thx in advanceOn Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
>Hi,
>I have the following query
>SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
>Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
>Access allows the calling of computed fields by using the column alias.
>Thx in advance
Hi JL,
This is not allowed in standard SQL. The common workarounds are:
* Simply repeat the expression, as in your example above
* Use a derived table. This is especially useful if the expression is long
and complicated. In your case, it would be overkill, but to demonstrate
the technique:
SELECT OrderQ, OrderQ * Tax AS SalesAmount
FROM (SELECT Prod * Qty AS OrderQ, Tax
FROM Orders) AS X
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks.
I dont understand why it's not allowed in standard SQL. Is this something
Yukon would improve?
"Hugo Kornelis" wrote:
> On Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
> >Hi,
> >I have the following query
> >
> >SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
> >
> >Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
> >Access allows the calling of computed fields by using the column alias.
> >
> >Thx in advance
> Hi JL,
> This is not allowed in standard SQL. The common workarounds are:
> * Simply repeat the expression, as in your example above
> * Use a derived table. This is especially useful if the expression is long
> and complicated. In your case, it would be overkill, but to demonstrate
> the technique:
> SELECT OrderQ, OrderQ * Tax AS SalesAmount
> FROM (SELECT Prod * Qty AS OrderQ, Tax
> FROM Orders) AS X
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||This is the way the SQL language is designed, in the ANSI SQL standard. So, access doesn't follow
the ANSI standard. Logically, a SQL statement is processed in the following order:
FROM
WHERE
GROUP BY
HAVING
SELECT <column list>
ORDER BY
TOP (Not in ANSI SQL standard)
Also, the SELECT list is not processed sequentially from left to right, all happens at the same time
(logically). If that were not the case, it would be difficult to do things like:
UPDATE tbl
SET colA = colB, colB = colA
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:A2B2AD3B-A0B0-4A1A-9297-68AE652E8E17@.microsoft.com...
> Thanks.
> I dont understand why it's not allowed in standard SQL. Is this something
> Yukon would improve?
> "Hugo Kornelis" wrote:
>> On Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
>> >Hi,
>> >I have the following query
>> >
>> >SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
>> >
>> >Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
>> >Access allows the calling of computed fields by using the column alias.
>> >
>> >Thx in advance
>> Hi JL,
>> This is not allowed in standard SQL. The common workarounds are:
>> * Simply repeat the expression, as in your example above
>> * Use a derived table. This is especially useful if the expression is long
>> and complicated. In your case, it would be overkill, but to demonstrate
>> the technique:
>> SELECT OrderQ, OrderQ * Tax AS SalesAmount
>> FROM (SELECT Prod * Qty AS OrderQ, Tax
>> FROM Orders) AS X
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
>Thanks.
>I dont understand why it's not allowed in standard SQL. Is this something
>Yukon would improve?
Hi JL,
The full explanation is posted on a regular basis by Joe Celko. Click the
following link to find several copies of his explanation on Google Groups:
http://groups-beta.google.com/groups?as_q=%22here%20is%20how%20a%20select%20works%22&safe=images&as_uauthors=Celko&lr=&hl=en
The important part of his explanation is that the complete SELECT clause
is evaluated at once. Column aliases don't exist until AFTER the SELECT
clause is evaluated. (And since evaluating the SELECT clause is the last
step, they don't exist during evaluation of the WHERE clause either - that
is another common question in this group).
I don't know aboout Yukon, but I'd be surprised if this feature would be
added. Just think about the possible misinterpretations. What output would
you expect from this query:
SELECT (A - B) AS A, (B - A) AS B
FROM SomeTable
I agree that the above query is silly, but it is valid syntax and there's
no way to decide if each of the A's and B's refer to the column or to the
alias. And the same problem suddenly looks a lot less confusing if you
look at some other examples, that are all quite likely to be used in many
places in production code:
SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
ROUND (SomeValue, 2) AS SomeValue,
CONVERT (char(10), SomeDate, 101) AS SomeDate,
SUM (SalesTotal) AS SalesTotal,
...
FROM ...
Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
parts of the SELECT clause, which one would you expect to be used? Can you
see the confusion this would cause?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:
> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
> >Thanks.
> >
> >I dont understand why it's not allowed in standard SQL. Is this something
> >Yukon would improve?
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
> http://groups-beta.google.com/groups?as_q=%22here%20is%20how%20a%20select%20works%22&safe=images&as_uauthors=Celko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Another thing to keep in mind is that SQL Server DOES provide you with the
concept of a DERIVED TABLE, as in the example that was provided to you. MS
Access CAN NOT; you always have to actually save this as another query
definition first.
So, yes, there are some drawbacks; however, there are advantages too.
Sincerely,
Anthony Thomas
"JL" <JL@.discussions.microsoft.com> wrote in message
news:8EF0B4A0-B69E-4BD0-ABF6-EC6C64C259A7@.microsoft.com...
Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:
> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
> >Thanks.
> >
> >I dont understand why it's not allowed in standard SQL. Is this
something
> >Yukon would improve?
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
>
http://groups-beta.google.com/groups?as_q=%22here%20is%20how%20a%20select%20works%22&safe=images&as_uauthors=Celko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Computed Fields Reuse

Hi,
I have the following query
SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
Access allows the calling of computed fields by using the column alias.
Thx in advance
On Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:

>Hi,
>I have the following query
>SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
>Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
>Access allows the calling of computed fields by using the column alias.
>Thx in advance
Hi JL,
This is not allowed in standard SQL. The common workarounds are:
* Simply repeat the expression, as in your example above
* Use a derived table. This is especially useful if the expression is long
and complicated. In your case, it would be overkill, but to demonstrate
the technique:
SELECT OrderQ, OrderQ * Tax AS SalesAmount
FROM (SELECT Prod * Qty AS OrderQ, Tax
FROM Orders) AS X
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks.
I dont understand why it's not allowed in standard SQL. Is this something
Yukon would improve?
"Hugo Kornelis" wrote:

> On Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
>
> Hi JL,
> This is not allowed in standard SQL. The common workarounds are:
> * Simply repeat the expression, as in your example above
> * Use a derived table. This is especially useful if the expression is long
> and complicated. In your case, it would be overkill, but to demonstrate
> the technique:
> SELECT OrderQ, OrderQ * Tax AS SalesAmount
> FROM (SELECT Prod * Qty AS OrderQ, Tax
> FROM Orders) AS X
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||This is the way the SQL language is designed, in the ANSI SQL standard. So, access doesn't follow
the ANSI standard. Logically, a SQL statement is processed in the following order:
FROM
WHERE
GROUP BY
HAVING
SELECT <column list>
ORDER BY
TOP (Not in ANSI SQL standard)
Also, the SELECT list is not processed sequentially from left to right, all happens at the same time
(logically). If that were not the case, it would be difficult to do things like:
UPDATE tbl
SET colA = colB, colB = colA
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:A2B2AD3B-A0B0-4A1A-9297-68AE652E8E17@.microsoft.com...[vbcol=seagreen]
> Thanks.
> I dont understand why it's not allowed in standard SQL. Is this something
> Yukon would improve?
> "Hugo Kornelis" wrote:
|||On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:

>Thanks.
>I dont understand why it's not allowed in standard SQL. Is this something
>Yukon would improve?
Hi JL,
The full explanation is posted on a regular basis by Joe Celko. Click the
following link to find several copies of his explanation on Google Groups:
http://groups-beta.google.com/groups...elko&lr=&hl=en
The important part of his explanation is that the complete SELECT clause
is evaluated at once. Column aliases don't exist until AFTER the SELECT
clause is evaluated. (And since evaluating the SELECT clause is the last
step, they don't exist during evaluation of the WHERE clause either - that
is another common question in this group).
I don't know aboout Yukon, but I'd be surprised if this feature would be
added. Just think about the possible misinterpretations. What output would
you expect from this query:
SELECT (A - B) AS A, (B - A) AS B
FROM SomeTable
I agree that the above query is silly, but it is valid syntax and there's
no way to decide if each of the A's and B's refer to the column or to the
alias. And the same problem suddenly looks a lot less confusing if you
look at some other examples, that are all quite likely to be used in many
places in production code:
SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
ROUND (SomeValue, 2) AS SomeValue,
CONVERT (char(10), SomeDate, 101) AS SomeDate,
SUM (SalesTotal) AS SalesTotal,
...
FROM ...
Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
parts of the SELECT clause, which one would you expect to be used? Can you
see the confusion this would cause?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:

> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
>
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
> http://groups-beta.google.com/groups...elko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Another thing to keep in mind is that SQL Server DOES provide you with the
concept of a DERIVED TABLE, as in the example that was provided to you. MS
Access CAN NOT; you always have to actually save this as another query
definition first.
So, yes, there are some drawbacks; however, there are advantages too.
Sincerely,
Anthony Thomas

"JL" <JL@.discussions.microsoft.com> wrote in message
news:8EF0B4A0-B69E-4BD0-ABF6-EC6C64C259A7@.microsoft.com...
Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:
[vbcol=seagreen]
> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
something
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
>
http://groups-beta.google.com/groups...elko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
sql sql

Computed Fields Reuse

Hi,
I have the following query
SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
Access allows the calling of computed fields by using the column alias.
Thx in advanceOn Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:

>Hi,
>I have the following query
>SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
>Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
>Access allows the calling of computed fields by using the column alias.
>Thx in advance
Hi JL,
This is not allowed in standard SQL. The common workarounds are:
* Simply repeat the expression, as in your example above
* Use a derived table. This is especially useful if the expression is long
and complicated. In your case, it would be overkill, but to demonstrate
the technique:
SELECT OrderQ, OrderQ * Tax AS SalesAmount
FROM (SELECT Prod * Qty AS OrderQ, Tax
FROM Orders) AS X
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks.
I dont understand why it's not allowed in standard SQL. Is this something
Yukon would improve?
"Hugo Kornelis" wrote:

> On Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
>
> Hi JL,
> This is not allowed in standard SQL. The common workarounds are:
> * Simply repeat the expression, as in your example above
> * Use a derived table. This is especially useful if the expression is long
> and complicated. In your case, it would be overkill, but to demonstrate
> the technique:
> SELECT OrderQ, OrderQ * Tax AS SalesAmount
> FROM (SELECT Prod * Qty AS OrderQ, Tax
> FROM Orders) AS X
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||This is the way the SQL language is designed, in the ANSI SQL standard. So,
access doesn't follow
the ANSI standard. Logically, a SQL statement is processed in the following
order:
FROM
WHERE
GROUP BY
HAVING
SELECT <column list>
ORDER BY
TOP (Not in ANSI SQL standard)
Also, the SELECT list is not processed sequentially from left to right, all
happens at the same time
(logically). If that were not the case, it would be difficult to do things l
ike:
UPDATE tbl
SET colA = colB, colB = colA
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:A2B2AD3B-A0B0-4A1A-9297-68AE652E8E17@.microsoft.com...[vbcol=seagreen]
> Thanks.
> I dont understand why it's not allowed in standard SQL. Is this something
> Yukon would improve?
> "Hugo Kornelis" wrote:
>|||On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:

>Thanks.
>I dont understand why it's not allowed in standard SQL. Is this something
>Yukon would improve?
Hi JL,
The full explanation is posted on a regular basis by Joe Celko. Click the
following link to find several copies of his explanation on Google Groups:
http://groups-beta.google.com/group...Celko&lr=&hl=en
The important part of his explanation is that the complete SELECT clause
is evaluated at once. Column aliases don't exist until AFTER the SELECT
clause is evaluated. (And since evaluating the SELECT clause is the last
step, they don't exist during evaluation of the WHERE clause either - that
is another common question in this group).
I don't know aboout Yukon, but I'd be surprised if this feature would be
added. Just think about the possible misinterpretations. What output would
you expect from this query:
SELECT (A - B) AS A, (B - A) AS B
FROM SomeTable
I agree that the above query is silly, but it is valid syntax and there's
no way to decide if each of the A's and B's refer to the column or to the
alias. And the same problem suddenly looks a lot less confusing if you
look at some other examples, that are all quite likely to be used in many
places in production code:
SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
ROUND (SomeValue, 2) AS SomeValue,
CONVERT (char(10), SomeDate, 101) AS SomeDate,
SUM (SalesTotal) AS SalesTotal,
..
FROM ...
Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
parts of the SELECT clause, which one would you expect to be used? Can you
see the confusion this would cause?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:

> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
>
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
> http://groups-beta.google.com/group...Celko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Another thing to keep in mind is that SQL Server DOES provide you with the
concept of a DERIVED TABLE, as in the example that was provided to you. MS
Access CAN NOT; you always have to actually save this as another query
definition first.
So, yes, there are some drawbacks; however, there are advantages too.
Sincerely,
Anthony Thomas
"JL" <JL@.discussions.microsoft.com> wrote in message
news:8EF0B4A0-B69E-4BD0-ABF6-EC6C64C259A7@.microsoft.com...
Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:

> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
>
something[vbcol=seagreen]
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
>
http://groups-beta.google.com/group...Celko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Computed columns and constant

Hi, I have a small problem with my database. I've got following situation: I have a computed column, which value is base on currency rate: rent * rate. Users have to have possibility to change currency rate easily (maybe another table or constant). Is there any way to create formula, which would compute value properly, via constant or something like this? Or the easiest workaround would be load data into dataset (I'm building asp.net application - database will be very small - couple of hundreds of records) and make calculations programmatically?

Przemek

hi,

computed columns can only be based on constants, variables, functions and columns of the same table, and the last one exclude your

Users have to have possibility to change currency rate easily (maybe another table or constant)

the client side calculation can be an option, as the same can be true at SELECT time as well.. you can do lot of sophisticated things in your SELECT statement, defining a "virtual" columns as well based on calculations..

ie:

SET NOCOUNT ON;

USE tempdb;

CREATE TABLE dbo.t1 (

Id int NOT NULL PRIMARY KEY,

Rate varchar(10) NOT NULL,

Val decimal (8,4) NOT NULL

);

CREATE TABLE dbo.t2 (

Id int NOT NULL,

RateId int NOT NULL

CONSTRAINT fk_t2$has$rates_on_t1 FOREIGN KEY

REFERENCES dbo.t1 (Id),

Rent decimal(8,4) NOT NULL

);

INSERT INTO dbo.t1 VALUES ( 1 , 'Dollar', 1 );

INSERT INTO dbo.t1 VALUES ( 2 , 'Euro', 1.25 );

INSERT INTO dbo.t2 VALUES ( 1 , 1 , 5 );

INSERT INTO dbo.t2 VALUES ( 2 , 2 , 5 );

SELECT t2.Id, t1.Rate, t2.Rent, t1.Val * t2.Rent AS [calculated value in base rate units]

FROM dbo.t2 t2

RIGHT JOIN dbo.t1 t1 ON t1.Id = t2.RateId;

DROP TABLE dbo.t2, dbo.t1;

--<

Id Rate Rent calculated value in base rate units

-- - --

1 Dollar 5.0000 5.00000000

2 Euro 5.0000 6.25000000

regards

computed columns and casting

Why does the following fail the parser ?
CREATE PROC [dbo].[GetQuestionAnswers]
@.QuestionID int
AS
DECLARE @.TotalCount int
SELECT @.TotalCount = Sum(AnswerCount)
FROM Answer
WHERE QuestionID = @.QuestionID
IF (@.TotalCount = 0)
BEGIN
SET @.TotalCount = 1
END
SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
AnswerFraction
FROM Answer
WHERE QuestionID = @.QuestionID
ORDER BY Rank
GODouble is not a Transact-SQL system data type. Use float or real for
approximate values or decimal for exact numeric values.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
> Why does the following fail the parser ?
> CREATE PROC [dbo].[GetQuestionAnswers]
> @.QuestionID int
> AS
> DECLARE @.TotalCount int
> SELECT @.TotalCount = Sum(AnswerCount)
> FROM Answer
> WHERE QuestionID = @.QuestionID
> IF (@.TotalCount = 0)
> BEGIN
> SET @.TotalCount = 1
> END
> SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
> AnswerFraction
> FROM Answer
> WHERE QuestionID = @.QuestionID
> ORDER BY Rank
> GO
>|||Hi Dan, and thanks for the response.
I should have phrased my question differently (because I quickly found out
that Double is not SQLDataType).
How to write SQL that creates a float or real or decimal psuedocolumn equal
to the quotient of two integer values (columns of integer type, or
otherwise) -- I do not want truncation to the nearest integer.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
> Double is not a Transact-SQL system data type. Use float or real for
> approximate values or decimal for exact numeric values.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
>|||Use
CAST(AnswerCount / @.TotalCount AS Decimal(12,2))
Madhivanan|||John,
It will not work to cast after dividing. You need to cast or
force implicit conversion before dividing.
Won't work:
cast(AnswerCount / @.TotalCount as double precision)
Either of these will work:
cast(AnswerCount as double precision) / @.TotalCount
(1e0*AnswerCount)/@.TotalCount
Steve Kass
Drew University
John A Grandy wrote:

>Hi Dan, and thanks for the response.
>I should have phrased my question differently (because I quickly found out
>that Double is not SQLDataType).
>How to write SQL that creates a float or real or decimal psuedocolumn equal
>to the quotient of two integer values (columns of integer type, or
>otherwise) -- I do not want truncation to the nearest integer.
>Thanks.
>"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
>
>
>|||CREATE PROCEDURE GetQuestionAnswers ( @.my_question_id INTEGER)
AS
SELECT A1.answer_id, A1.answer_txt,
(A1.answer_count
/ (SELECT CASE WHEN SUM(A2.answer_count) = 0
THEN CAST (1.00 AS REAL)
ELSE SUM(A2.answer_count) END)
FROM Answers AS A2
WHERE A2.question_id = A1.question_id))
AS answer_fraction
FROM Answers AS A1
WHERE A1.question_id = @.my_question_id;
Untested; the CASE expression will resolve to the highest data type in
the THEN or ELSE clauses. This saves a local variable and lets the
optimizer do its thing.

Computed columns

Hi,

Consider the following example

create table sample

(col1 int,

col2 int ,

col3 AS col1 + col2) PERSISTED NOT NULL)

basically col3 is a computed column. Now when ever a row in col1 or col2 is updated the computed column will reflect the new value. how does this happen in the background. does this use row level triggers or what other mechanism is used to maintain col3 - computed column

the value does not exist by default

when you select a record and you included the calculated column

the server reevaluates everything which

is one of the great disadvantage of the computed column.

computation is being done all over again when you select from this column

|||I have marked it PERSISTED ...meaning that the column is saved in the database. I think what you are talking about is the computed column without using the PERSISTED key word.|||

sorry i wasn't aware of that new feature

any way

Their values are updated when any columns that are part of their calculation change

|||I would say magic :) Seriously, it would be done at a physical implementation level below what we have access to, much like values in an index get maintained. I think if you thought of it sort of like a row-level trigger it wouldn't be "wrong," but it is not through any mechanism that we have direct access to for sure.|||the reason why i posted the question is : If i use a lot of computed columns in my database, will it cause any kind of performance problems. so far what ever i have read about computed columns, no where it is mentioned that using computed columns may cause performance problems. Please let me know if you have any information.|||I think the question is more about how you need the data. There will be a performance hit, whether you persist them, or not. The difference will be based on whether you modify data more, or read it more. If it is a frequently used column (or might be) try persisting it. If it doesn't slow you down too much then that would be the best idea. Unless your formula is tremendously complex, I doubt you will even notice.|||

one good thing in answering in this forum is that you

got to learn new things. anyway here's what BOL has to say

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The SQL Server 2005 Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.

In terms of performance therefore persisted columns, performs better than non-persisted. Except of course when we hard talking of harddisk consumption. persisted column may even outrun a column-trigger solution and the first is easier to maintain over the later.

|||Thanks for all your replies.

Thursday, March 22, 2012

Compute sum of count(*) with group by

Hi,
Given the following table and test data:
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (5,1,'2005-01-01 15:15','2005-01-01 15:20')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
the following query lists only the spid's with non-unique spid's and
their respective counts:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
I'm new to SQL and am having difficulty with a couple of things:
1. Modify the above query to compute the grand total for the Count, or
indeed a separate SQL statement to return just the grand total (= 6 in
this example).
2. This is the big challenge :). Taking the grouping returned by the
above query, write a query/stored procedure which looks for records
with identical spId's and the endtime of one spid equal to the
startTime of another. With the above test data, recordIds 3, 5, and 2,
4, 6 match this criteria.
Thanks very much for any help with this.1. Use a derived table construct:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total ) ;
2. Not sure if your requirements are clear since . Something like:
SELECT recordId, spId, ...
( SELECT MIN( t2.startTime )
FROM tbl t2 WHERE t2.spId = t1.spId
AND t2.startTime >= t1.endtime )
FROM tbl t1
ORDER BY t1.spid, startTime ;
If, not please post the sample resultset for the dataset you posted.
Anith|||Anith Sen wrote:
> 1. Use a derived table construct:
> SELECT SUM( total )
> FROM ( SELECT spid, COUNT(*)
> FROM tbl
> GROUP BY spid
> HAVING COUNT(*) > 1 ) D ( spid, total ) ;
>
Thanks. What does the 'D' mean above?

> 2. Not sure if your requirements are clear since . Something like:
> SELECT recordId, spId, ...
> ( SELECT MIN( t2.startTime )
> FROM tbl t2 WHERE t2.spId = t1.spId
> AND t2.startTime >= t1.endtime )
> FROM tbl t1
> ORDER BY t1.spid, startTime ;
> If, not please post the sample resultset for the dataset you posted.
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (5,1,'2005-01-01 14:33','2005-01-01 15:20')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
(Sorry, no wonder it wasn't clear as there was mistake in my original
test data. I've corrected the data above and put records with the same
spId together to make the grouping more obvious.)
So, from the above test data the expected results contain 2 sets of
matching data:
1. recordIds 3 and 5 because they have the same spId (1) and the
endTime of recordId 3 is the same as the startTime of recordId 5.
2. recordIds 2, 4 and 6 because they have the same spId (2) and the
endTime of recordId 2 is the same as the startTime of recordId 4; the
endTime of 4 is the same as the startTime of 6.
I hope that makes sense now. cheers,|||On 11 Nov 2005 09:30:25 -0800, "J Williams"
<johnwilliams_esquire@.hotmail.com> wrote:
>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
If that does the job, great, otherwise you can always store the
results of the first query in a table an do further summations against
it.
J.|||>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
Thanks, but that doesn't give the expected result. The basic SELECT:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
returns:
spid Count
1 3
2 3
The grand total of Count in the above resultset is 6 and the SQL posted
earlier by Anith Sen gives this result:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total )|||>> What does the 'D' mean above?
D in the query stands for an alias for the derived table ( some folks
explicitly use AS keyword before the alias as well. )
Can you post the sample resultset here ( as you'd want to see on the QA
results pane ).
Anith|||Anith Sen wrote:

> Can you post the sample resultset here ( as you'd want to see on the QA
> results pane ).
First recordId, Second recordId, spId, endTime, startTime
3 5 1 2005-01-01 14:33 2005-01-01 14:33
2 4 2 2005-01-01 14:00 2005-01-01 14:00
4 6 2 2005-01-01 15:15 2005-01-01 15:15
The resultset shows pairs of 'matching' records, which is slightly
different (and better) to how I first envisioned it.
Thanks.|||This is one way of getting it:
SELECT MAX( t1.recordid ),
t2.recordid, t1.spid, t1.endtime
FROM test t1
INNER JOIN test t2
ON t1.spId = t2.spId
AND t1.endTime = t2.starttime
GROUP BY t1.spid, t2.recordid, t1.endtime ;
Anith|||That's excellent, thanks.

Thursday, March 29, 2012

Concatenate strings in group column

Concatenate String and Pass to FORMSOF?

Concatenate String and Pass to FORMSOF?

Concatenate String and Pass to FORMSOF?

Concatenate Rows

Concatenate Rows

Concatenate list of values under a group

Tuesday, March 27, 2012

Concatenatation with NULL

Concat instead of SUM when grouping results

Sunday, March 25, 2012

Computed Fields Reuse

Computed Fields Reuse

Computed Fields Reuse

Computed columns and constant

computed columns and casting

Computed columns

Thursday, March 22, 2012

Compute sum of count(*) with group by

Sql Comparing two tables

Blog Archive

About Me