Case Statement!

Hi all,
I am trying to return a true / false value via case statement.
The boolean value returned is determined whether a column contains a null
value.
Can someone help with the following query as it is causing an error...
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN IsNull THEN 0
WHEN Not IsNull Then 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Cheers,
Adam
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IS NULL THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||Try (untested)
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID IsNull THEN 0
WHEN a.ColumnID Is not Null Then 1
END AS colAlias
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID
Is Null THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in message
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I am trying to return a true / false value via case statement.
> The boolean value returned is determined whether a column contains a null
> value.
> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
> Cheers,
> Adam
>|||Try this.
SELECT
q.ColumnID, q.ColumnText,
CASE WHEN a.ColumnID Is Null THEN 0 ELSE 1 END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID|||"Adam J Knight" <adam.jknight@.optusnet.com.au> wrote in
news:eV8iGrvKGHA.1288@.TK2MSFTNGP09.phx.gbl:

> Can someone help with the following query as it is causing an error...
> SELECT
> q.ColumnID, q.ColumnText,
> CASE a.ColumnID
> WHEN IsNull THEN 0
> WHEN Not IsNull Then 1
> END
> FROM
> Table1 q
> LEFT JOIN
> Table2 a
> ON
> q.ColumnID = a.ColumnID
I suspect that you're misusing the ISNULL function. AFAICT, the syntax
for ISNULL is: ISNULL ( check_expression , replacement_value )
Try something like:
CASE ISNULL(a.ColumnID, 0)
WHEN 0 THEN 0
ELSE 1
END AS aID
or
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END AS aID
HTH,
Geoff Lane
Cornwall, UK|||Hi Adam,
SELECT
q.ColumnID, q.ColumnText,
CASE a.ColumnID
WHEN NULL THEN 0
ELSE 1
END
FROM
Table1 q
LEFT JOIN
Table2 a
ON
q.ColumnID = a.ColumnID
HTH, Jens Suessmeyer.sql

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they are
case sensitive?
Thanks in advance.
John Steen
It appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a crit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a crit :
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:

> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
Hugo Kornelis, SQL Server MVP
|||Thanks, Hugo. And you're right, I do need to learn to write more than simple
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:

> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
>
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they ar
e
case sensitive?
Thanks in advance.
John SteenIt appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a crit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a crit :
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle m
y
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:

> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
are
> case sensitive?
> Thanks in advance.
> John Steen
>|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle
my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo. And you're right, I do need to learn to write more than simpl
e
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:

> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
>
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they are
case sensitive?
Thanks in advance.
John SteenIt appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a écrit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a écrit :
>> It appears your database default collation is case-insensitive. You can
>> change the collation for a specific column to a case-sensitive one with
>> ALTER TABLE:
>> CREATE TABLE MyTable
>> (
>> MyPassword varchar(10)
>> )
>> INSERT INTO MyTable VALUES('password')
>> --row found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'PASSWORD'
>> ALTER TABLE MyTable
>> ALTER COLUMN MyPassword varchar(10)
>> COLLATE SQL_Latin1_General_CP1_CS_AS
>> --row not found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'PASSWORD'
>> --row found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'password'
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they are
> case sensitive?
> Thanks in advance.
> John Steen
>|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:
>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
--
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo. And you're right, I do need to learn to write more than simple
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:
> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
> >Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
> >way through it that way if necessary, but is there a way to do this in
> >Enterprise Manager? Can I do this through the "Design Table" atributes?
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

CASE Returning NULL

Hello,

I have a query that contains six derived columns;

FirstPerYr; The current year
FirstPerMo; The current month
FirstPeriodRevenue; CASE the current year, use the CurrentYearSalesTable (CY). CASE the current month, match the correct column (JanRev, FebRev, etc) of the CurrentYearSalesTable to get the correct data.
FirstPeriodYrAnnum; Last year
FirstPeriodMoAnnum; The current month, last year
FirstPeriodAnnumRev; Same as FirstPeriodRevenue, but from the year and month before.

In the following query, I get the correct FirstPeriodRev value, but FirstPeriodAnnumRev comes up NULL. I know I have data for January 2006. The query is as follows;

--**************************

DECLARE @.FirstPerYr Int
DECLARE @.FirstPerMo Int
DECLARE @.FirstPerYrAnnum Int
DECLARE @.FirstPerMoAnnum Int

SET @.FirstPerYr = YEAR(GETDATE())
SET @.FirstPerMo = MONTH(GETDATE())
SET @.FirstPerYrAnnum = YEAR(DateAdd(Year,-1,DateAdd(Month,0,GetDate())))
SET @.FirstPerMoAnnum = MONTH(DateAdd(Year,-1,DateAdd(Month,0,GetDate())))

SELECT
RTRIM(DA.AcctCode) AS DAAcctCode,
RTRIM(DA.CompanyName) AS DACompanyName,
@.FirstPerYr AS FirstPerYr,
@.FirstPerMo AS FirstPerMo,
"FirstPeriodRev" =
CASE
WHEN @.FirstPerYr = DATEPART(YEAR,GETDATE()) THEN
CASE
WHEN @.FirstPerMo = 1 THEN SUM(CY.JanRev)
WHEN @.FirstPerMo = 2 THEN SUM(CY.FebRev)
WHEN @.FirstPerMo = 3 THEN SUM(CY.MarRev)
WHEN @.FirstPerMo = 4 THEN SUM(CY.AprRev)
WHEN @.FirstPerMo = 5 THEN SUM(CY.MayRev)
WHEN @.FirstPerMo = 6 THEN SUM(CY.JunRev)
WHEN @.FirstPerMo = 7 THEN SUM(CY.JulRev)
WHEN @.FirstPerMo = 8 THEN SUM(CY.AugRev)
WHEN @.FirstPerMo = 9 THEN SUM(CY.SeptRev)
WHEN @.FirstPerMo = 10 THEN SUM(CY.OctRev)
WHEN @.FirstPerMo = 11 THEN SUM(CY.NovRev)
WHEN @.FirstPerMo = 12 THEN SUM(CY.DecRev)
END
END,
@.FirstPerYrAnnum AS FirstPerYrAnnum,
@.FirstPerMoAnnum AS FirstPerMoAnnum,
"FirstPeriodAnnumRev" =
CASE
WHEN @.FirstPerYrAnnum = DATEADD(YEAR,-1,GETDATE()) THEN
CASE
WHEN @.FirstPerMoAnnum = 1 THEN SUM(PY.JanRev)
WHEN @.FirstPerMoAnnum = 2 THEN SUM(PY.FebRev)
WHEN @.FirstPerMoAnnum = 3 THEN SUM(PY.MarRev)
WHEN @.FirstPerMoAnnum = 4 THEN SUM(PY.AprRev)
WHEN @.FirstPerMoAnnum = 5 THEN SUM(PY.MayRev)
WHEN @.FirstPerMoAnnum = 6 THEN SUM(PY.JunRev)
WHEN @.FirstPerMoAnnum = 7 THEN SUM(PY.JulRev)
WHEN @.FirstPerMoAnnum = 8 THEN SUM(PY.AugRev)
WHEN @.FirstPerMoAnnum = 9 THEN SUM(PY.SeptRev)
WHEN @.FirstPerMoAnnum = 10 THEN SUM(PY.OctRev)
WHEN @.FirstPerMoAnnum = 11 THEN SUM(PY.NovRev)
WHEN @.FirstPerMoAnnum = 12 THEN SUM(PY.DecRev)
END
END
FROM

SalesCommissions.dbo.DailyAccountsDownload DA
INNER JOIN (SELECT
AcctCode,
Territory,
SUM(JanRev) AS JanRev,
SUM(FebRev) AS FebRev,
SUM(MarRev) AS MarRev,
SUM(AprRev) AS AprRev,
SUM(MayRev) AS MayRev,
SUM(JunRev) AS JunRev,
SUM(JulRev) AS JulRev,
SUM(AugRev) AS AugRev,
SUM(SeptRev) AS SeptRev,
SUM(OctRev) AS OctRev,
SUM(NovRev) AS NovRev,
SUM(DecRev) AS DecRev
FROM
SalesReporting.dbo.PriorYearSales
GROUP BY
AcctCode, Territory)PY
ON RTRIM(DA.AcctCode) = RTRIM(PY.AcctCode)
INNER JOIN (SELECT
AcctCode,
SUM(JanRev) AS JanRev,
SUM(FebRev) AS FebRev,
SUM(MarRev) AS MarRev,
SUM(AprRev) AS AprRev,
SUM(MayRev) AS MayRev,
SUM(JunRev) AS JunRev,
SUM(JulRev) AS JulRev,
SUM(AugRev) AS AugRev,
SUM(SeptRev) AS SeptRev,
SUM(OctRev) AS OctRev,
SUM(NovRev) AS NovRev,
SUM(DecRev) AS DecRev
FROM
SalesReporting.dbo.CurrentYearSales
GROUP BY
AcctCode)CY
ON RTRIM(DA.AcctCode) = RTRIM(CY.AcctCode)
WHERE
RTRIM(DA.AcctCode) = 'AM940'

GROUP BY
RTRIM(DA.AcctCode), DA.CompanyName

--*************************

The result set looks like this;

DAAcctCode DACompanyName FirstPerYr FirstPerMo FirstPeriodRev FirstPerYrAnnum FirstPerMoAnnum FirstPeriodAnnumRev
AM940 Sterling Educationa 2007 1 1769.75 2006 1 NULL

If the logic for CASE is the same to find the correct column for the previous year, then why would the result for the previous year come up NULL?

Thanks again for your help!

CSDunn

here ur doin a SUM in case statement...if ne of the data whose sum is being taken is null..it'll show the result as null...guess thats wats hapennin...check the data...|||

Thank you for your response. I tried to just set the True condition of the first WHEN to zero, and still got NULL. Then I tried to edit the exiting True condition from SUM(PY.JanRev) to SUM(ISNULL(PY.JanRev,0)) and still got NULL.

cdun2

|||

I found the problem. When evaluating @.FirstPerYrAnnum = DATEADD(YEAR,-1,GETDATE())), The variable @.FirstPerYrAnnum contained only the YEAR portion of the date. I needed to express my test as follows;

@.FirstPerYrAnnum = YEAR(DATEADD(YEAR,-1,GETDATE()))

Now I get back something that looks correct. I need to test.

Putting an ELSE condition in the outer CASE helped me find this.

Thank you again for your help!

CSDunn

|||put an else in both inner and outer case...see if it goes there...|||

o..u already got it...:)...

always use an else in case statements..gud practice..u nvr know when u need it..

Cascading updates question

I have two tables, Stock and Positions. Stock contains a Symbol column and a
Price column, and Symbol is the key. Positions contain the columns as well a
s
several other columns whose data should change with the Price. Positions can
have the same Symbol multiple times (it is keyed by Symbol-Account).
My design is to have a foreign key between the tables, so that when the
Price in the Stock column is updated, the Price in Positions also updates. I
am making some columns in the Positions table computed columns, so that they
recalculate when the Price updates.
My questions/concerns are:
1. Is this design any good? One alternative I was thinkning of was to use no
computed columns, and run a stored procedure frequently to update the
Positions.
2. Will the computed columns updated automatically when Price changes?
3. Will there be the possibility that some rows in Positions with the same
symbols are not updated simultaneously, so that the Price could be different
for the same symbol?
4. Do the rows lock while they are updating? This has implications because I
am querying this table often for other purposes.
5. If one computed column depends on another computed column, is there a way
to specify the order in which they calculate, or is this just a big no-no?
I am grateful for any insight.
Thank you,
CP Developer"CP Developer" <steved@.newsgroup.nospam> wrote in message
news:A40102EA-6AF1-4377-9332-09EFCAA83204@.microsoft.com...
>I have two tables, Stock and Positions. Stock contains a Symbol column and
>a
> Price column, and Symbol is the key. Positions contain the columns as well
> as
> several other columns whose data should change with the Price. Positions
> can
> have the same Symbol multiple times (it is keyed by Symbol-Account).
> My design is to have a foreign key between the tables, so that when the
> Price in the Stock column is updated, the Price in Positions also updates.
> I
> am making some columns in the Positions table computed columns, so that
> they
> recalculate when the Price updates.
> My questions/concerns are:
> 1. Is this design any good? One alternative I was thinkning of was to use
> no
> computed columns, and run a stored procedure frequently to update the
> Positions.
There are a few reasonable ways I can think of to have a comupted column
based on a column in a related table.
Put a phoney foreign key on (StockID, Price) and use cascade updates (your
idea).
Put a trigger on Stock to update the related positions.
Use a view to join the two tables and define the calculations there.

> 2. Will the computed columns updated automatically when Price changes?

> 3. Will there be the possibility that some rows in Positions with the same
> symbols are not updated simultaneously, so that the Price could be
> different
> for the same symbol?
No.

> 4. Do the rows lock while they are updating? This has implications because
> I
> am querying this table often for other purposes.
Yes. Make sure you have an index supporting the foreign key relationship.

> 5. If one computed column depends on another computed column, is there a
> way
> to specify the order in which they calculate, or is this just a big no-no?
>
No you cannot base one computed column on another. However you are free to
cut and paste the calculation for one column into the other.
Here's an example:
drop table position
drop table stock
go
create table stock
(
id int primary key,
price decimal(9,2),
constraint uk_id_price
unique (id,price)
)
create table position
(
account int not null, -- references account
stock int not null references stock,
price decimal(9,2) not null,
other_price as cast(price*.9 as decimal(9,2)),
constraint pk_position
primary key(account,stock),
constraint fk_position_stock_price
foreign key (stock,price)
references stock(id,price)
on update cascade
)
create index ix_position_stock_price
on position(stock,price)
go
insert into stock(id,price) values (1,3.50)
insert into position(account,stock,price) values (23,1,3.50)
go
update stock set price = 5.25 where id = 1
select * from position
David|||The columns will update when the price changes.
Create Table TableA
(
Symbol varchar(10),
Price Money,
Qty Int,
TotalCost As (Price * Qty)
)
Insert Into TableA
Select 'GBP', 14.52, 2
select * From TableA
Update TableA
Set Qty = 151, price = 45.65
select * From TableA
Drop table TableA
HTH
Barry|||I would store the price in one table only, and select it from there. If
you need you selects to be as fast as possible, consider using an
indexed view.

Cascading Security.

Hi,
I am looking for a way to implement the following security schema in SQL
2005.
Database 1 contains the Raw Data
Data base 2 contains views On the data in Database 1.
We need to create a user who can access in read mode the views in Database
2, but should not be allowed to explore through ODBC connection teh tables
and data in Database 1.
Is there a method to implement this?
thanks in AdvanceZrod
Do the users own the objects?
GRANT only SELECT on Views to the user in db2. DENY VIEW DEDINITION (for
details please refer to the BOL) on db1
"Zrod" <zrod@.aims-co.com> wrote in message
news:ubCuwCEVHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am looking for a way to implement the following security schema in SQL
> 2005.
> Database 1 contains the Raw Data
> Data base 2 contains views On the data in Database 1.
> We need to create a user who can access in read mode the views in Database
> 2, but should not be allowed to explore through ODBC connection teh tables
> and data in Database 1.
> Is there a method to implement this?
> thanks in Advance
>
>

cascade delete can not apply to two foreign keys

I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?

Table A:
ID
ProductID <foreign key>
CustomerID <foreign key
Table Product
ProductID <primary key
Table Customer
CustomerID <primary key
I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.In my opinion, relying on cascading updates and deletes isn't a good thing to do anyway. I think it's better to handle the deletes in your code when you explicitly want them to happen. I.e., the procedures to delete from either the product or customer table should include a delete statement against Table A. I don't think cascading deletes are a good idea to replace your own logic.

Carriage Return and Line Feed in TexBox

I want to create a report with a textbox which contains a field followed by a
Carriage Return and Line Feed then another field
e.g
=First(Fields!Field1.Value)+ Carriage Return and Line Feed +
Fields!Field2.Value
How do I do this? I see a suggestion in places for vbCRLF and also \n. But
neither work.
Does vbCRLF apply perhaps to a vb.net environment? I'm in a c# environment.
Many Thanks
--
FionaDMDid you know you can insert another detail row? Just r-click on the row
selector, and insert row below.
Mike G.
"FionaDM" <FionaDM@.discussions.microsoft.com> wrote in message
news:2AD857E3-998C-494F-A9D1-558C430A1848@.microsoft.com...
>I want to create a report with a textbox which contains a field followed by
>a
> Carriage Return and Line Feed then another field
> e.g
> =First(Fields!Field1.Value)+ Carriage Return and Line Feed +
> Fields!Field2.Value
> How do I do this? I see a suggestion in places for vbCRLF and also \n. But
> neither work.
> Does vbCRLF apply perhaps to a vb.net environment? I'm in a c#
> environment.
> Many Thanks
> --
> FionaDM|||Try using the following:
=First(Fields!Field1.Value) & vbCrLf & Fields!Field2.Value
The Reporting Services expression designer uses VB6 style concatenation
characters so it might have been because you were using the + instead of
the &.
If it still doesn't work try using Environment.NewLine or "\n\r" in place
of vbCrLf but that should work for you. You can see a documented example of
using this in the following article:
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
Just do a search for vbCrLf.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Carriage Return and Line Feed in TexBox
> From: =?Utf-8?B?RmlvbmFETQ==?= <FionaDM@.discussions.microsoft.com>
> Subject: Carriage Return and Line Feed in TexBox
> Date: Tue, 2 Oct 2007 06:25:01 -0700
> I want to create a report with a textbox which contains a field followed
by a
> Carriage Return and Line Feed then another field
> e.g
> =First(Fields!Field1.Value)+ Carriage Return and Line Feed +
> Fields!Field2.Value
> How do I do this? I see a suggestion in places for vbCRLF and also \n.
But
> neither work.
> Does vbCRLF apply perhaps to a vb.net environment? I'm in a c#
environment.
> Many Thanks
> --
> FionaDM
>|||Thanks - & vbCrLf & did the trick
--
FionaDM
"Chris Alton [MSFT]" wrote:
> Try using the following:
> =First(Fields!Field1.Value) & vbCrLf & Fields!Field2.Value
> The Reporting Services expression designer uses VB6 style concatenation
> characters so it might have been because you were using the + instead of
> the &.
> If it still doesn't work try using Environment.NewLine or "\n\r" in place
> of vbCrLf but that should work for you. You can see a documented example of
> using this in the following article:
> http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> Just do a search for vbCrLf.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > Thread-Topic: Carriage Return and Line Feed in TexBox
> > From: =?Utf-8?B?RmlvbmFETQ==?= <FionaDM@.discussions.microsoft.com>
> > Subject: Carriage Return and Line Feed in TexBox
> > Date: Tue, 2 Oct 2007 06:25:01 -0700
> >
> > I want to create a report with a textbox which contains a field followed
> by a
> > Carriage Return and Line Feed then another field
> >
> > e.g
> > =First(Fields!Field1.Value)+ Carriage Return and Line Feed +
> > Fields!Field2.Value
> >
> > How do I do this? I see a suggestion in places for vbCRLF and also \n.
> But
> > neither work.
> > Does vbCRLF apply perhaps to a vb.net environment? I'm in a c#
> environment.
> >
> > Many Thanks
> >
> > --
> > FionaDM
> >
>|||Great. Those concatenation things always got me between VB and C# so you
aren't the only one.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Carriage Return and Line Feed in TexBox
> From: <FionaDM@.discussions.microsoft.com>
> Subject: RE: Carriage Return and Line Feed in TexBox
> Date: Wed, 3 Oct 2007 02:56:01 -0700
> Thanks - & vbCrLf & did the trick
> --
> FionaDM
>
> "Chris Alton [MSFT]" wrote:
> > Try using the following:
> > =First(Fields!Field1.Value) & vbCrLf & Fields!Field2.Value
> >
> > The Reporting Services expression designer uses VB6 style concatenation
> > characters so it might have been because you were using the + instead
of
> > the &.
> >
> > If it still doesn't work try using Environment.NewLine or "\n\r" in
place
> > of vbCrLf but that should work for you. You can see a documented
example of
> > using this in the following article:
> >
> > http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> >
> > Just do a search for vbCrLf.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> > > Thread-Topic: Carriage Return and Line Feed in TexBox
> > > From: =?Utf-8?B?RmlvbmFETQ==?= <FionaDM@.discussions.microsoft.com>
> > > Subject: Carriage Return and Line Feed in TexBox
> > > Date: Tue, 2 Oct 2007 06:25:01 -0700
> > >
> > > I want to create a report with a textbox which contains a field
followed
> > by a
> > > Carriage Return and Line Feed then another field
> > >
> > > e.g
> > > =First(Fields!Field1.Value)+ Carriage Return and Line Feed +
> > > Fields!Field2.Value
> > >
> > > How do I do this? I see a suggestion in places for vbCRLF and also
\n.
> > But
> > > neither work.
> > > Does vbCRLF apply perhaps to a vb.net environment? I'm in a c#
> > environment.
> > >
> > > Many Thanks
> > >
> > > --
> > > FionaDM
> > >
> >
> >
>|||I have also seen controlchars.newline referenced and have personally used
System.Environment.NewLine. Is there one that is most efficient?
"Chris Alton [MSFT]" wrote:
> Great. Those concatenation things always got me between VB and C# so you
> aren't the only one.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > Thread-Topic: Carriage Return and Line Feed in TexBox
> > From: <FionaDM@.discussions.microsoft.com>
> > Subject: RE: Carriage Return and Line Feed in TexBox
> > Date: Wed, 3 Oct 2007 02:56:01 -0700
> >
> > Thanks - & vbCrLf & did the trick
> > --
> > FionaDM
> >
> >
> > "Chris Alton [MSFT]" wrote:
> >
> > > Try using the following:
> > > =First(Fields!Field1.Value) & vbCrLf & Fields!Field2.Value
> > >
> > > The Reporting Services expression designer uses VB6 style concatenation
> > > characters so it might have been because you were using the + instead
> of
> > > the &.
> > >
> > > If it still doesn't work try using Environment.NewLine or "\n\r" in
> place
> > > of vbCrLf but that should work for you. You can see a documented
> example of
> > > using this in the following article:
> > >
> > > http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> > >
> > > Just do a search for vbCrLf.
> > >
> > > --
> > > Chris Alton, Microsoft Corp.
> > > SQL Server Developer Support Engineer
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > --
> > > > Thread-Topic: Carriage Return and Line Feed in TexBox
> > > > From: =?Utf-8?B?RmlvbmFETQ==?= <FionaDM@.discussions.microsoft.com>
> > > > Subject: Carriage Return and Line Feed in TexBox
> > > > Date: Tue, 2 Oct 2007 06:25:01 -0700
> > > >
> > > > I want to create a report with a textbox which contains a field
> followed
> > > by a
> > > > Carriage Return and Line Feed then another field
> > > >
> > > > e.g
> > > > =First(Fields!Field1.Value)+ Carriage Return and Line Feed +
> > > > Fields!Field2.Value
> > > >
> > > > How do I do this? I see a suggestion in places for vbCRLF and also
> \n.
> > > But
> > > > neither work.
> > > > Does vbCRLF apply perhaps to a vb.net environment? I'm in a c#
> > > environment.
> > > >
> > > > Many Thanks
> > > >
> > > > --
> > > > FionaDM
> > > >
> > >
> > >
> >
>|||I personally haven't tried using either of those in a report but since it
supports .NET namespaces I don't see why it wouldn't.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Carriage Return and Line Feed in TexBox
> thread-index: AcgHaTZxHlx+/uxhRJCGUmWO9AQkmQ==> X-WBNR-Posting-Host: 207.46.19.197
> From: =?Utf-8?B?V2lsbGlhbQ==?= <William@.discussions.microsoft.com>
> References: <2AD857E3-998C-494F-A9D1-558C430A1848@.microsoft.com>
<pGoU9QQBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
<EF5C20BB-CFAB-4622-96D2-2F45B21444AC@.microsoft.com>
<e6Gm4coBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
> Subject: RE: Carriage Return and Line Feed in TexBox
> Date: Fri, 5 Oct 2007 09:03:04 -0700
> I have also seen controlchars.newline referenced and have personally used
> System.Environment.NewLine. Is there one that is most efficient?
> "Chris Alton [MSFT]" wrote:
> > Great. Those concatenation things always got me between VB and C# so
you
> > aren't the only one.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> > > Thread-Topic: Carriage Return and Line Feed in TexBox
> > > From: <FionaDM@.discussions.microsoft.com>
> > > Subject: RE: Carriage Return and Line Feed in TexBox
> > > Date: Wed, 3 Oct 2007 02:56:01 -0700
> > >
> > > Thanks - & vbCrLf & did the trick
> > > --
> > > FionaDM
> > >
> > >
> > > "Chris Alton [MSFT]" wrote:
> > >
> > > > Try using the following:
> > > > =First(Fields!Field1.Value) & vbCrLf & Fields!Field2.Value
> > > >
> > > > The Reporting Services expression designer uses VB6 style
concatenation
> > > > characters so it might have been because you were using the +
instead
> > of
> > > > the &.
> > > >
> > > > If it still doesn't work try using Environment.NewLine or "\n\r" in
> > place
> > > > of vbCrLf but that should work for you. You can see a documented
> > example of
> > > > using this in the following article:
> > > >
> > > > http://msdn2.microsoft.com/en-us/library/ms157328.aspx
> > > >
> > > > Just do a search for vbCrLf.
> > > >
> > > > --
> > > > Chris Alton, Microsoft Corp.
> > > > SQL Server Developer Support Engineer
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > > --
> > > > > Thread-Topic: Carriage Return and Line Feed in TexBox
> > > > > From: =?Utf-8?B?RmlvbmFETQ==?= <FionaDM@.discussions.microsoft.com>
> > > > > Subject: Carriage Return and Line Feed in TexBox
> > > > > Date: Tue, 2 Oct 2007 06:25:01 -0700
> > > > >
> > > > > I want to create a report with a textbox which contains a field
> > followed
> > > > by a
> > > > > Carriage Return and Line Feed then another field
> > > > >
> > > > > e.g
> > > > > =First(Fields!Field1.Value)+ Carriage Return and Line Feed +
> > > > > Fields!Field2.Value
> > > > >
> > > > > How do I do this? I see a suggestion in places for vbCRLF and
also
> > \n.
> > > > But
> > > > > neither work.
> > > > > Does vbCRLF apply perhaps to a vb.net environment? I'm in a c#
> > > > environment.
> > > > >
> > > > > Many Thanks
> > > > >
> > > > > --
> > > > > FionaDM
> > > > >
> > > >
> > > >
> > >
> >
> >
>

Carriage Contron in field

I am trying to split an address field which contains carriage controls. The address field can have 2 carriage controls in it, ie 3 address lines. I want to split the address into 3 seperate colomns based on the control carriage. I know how to combine 3 co
lumns into 1 but not how to split it out again.
Chard.
hi chard,
I assume you have carriage return in the string data which is represented by
char(13). See following example.
--sample data
create table t(col1 varchar(400))
insert into t values ('address1' + char(13)+ 'address2' + char(13) +
'address3')
insert into t values ('address4' + char(13)+ 'address5' + char(13) +
'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6')
insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
char(13) + 'address6xbbhghhw')
--query
select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
substring (col1, charindex (char(13), col1) + 1,
(charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
(char(13), col1))) '2nd_address',
right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
from t
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Hi Vishal,
Exactly what I was after. Thanks very much.
Chard.
"Vishal Parkar" wrote:

> hi chard,
> I assume you have carriage return in the string data which is represented by
> char(13). See following example.
> --sample data
> create table t(col1 varchar(400))
> insert into t values ('address1' + char(13)+ 'address2' + char(13) +
> 'address3')
> insert into t values ('address4' + char(13)+ 'address5' + char(13) +
> 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6')
> insert into t values ('addressxyz4' + char(13)+ 'addressyrtyr5125455' +
> char(13) + 'address6xbbhghhw')
>
> --query
> select left (col1, (charindex (char(13), col1)-1) ) 'first_address',
> substring (col1, charindex (char(13), col1) + 1,
> (charindex ( char(13), col1, charindex (char(13), col1) + 1 ) - charindex
> (char(13), col1))) '2nd_address',
> right (col1, ((charindex (char(13), reverse(col1))) - 1) ) '3rd_address'
> from t
>
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>

Capturing value of identity column for use later?

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @.@.IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@.WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

-------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@.WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM#EstimateLines;

SET@.CurWeekReportedLineID = @.@.IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@.WeekReportedID1,
@.CurWeekReportedLineID,
@.BeginDate1,
Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM#EstimateLines;
--------

The #Temp table create via VB:
--------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
--------

--
PeteCresswell"(Pete Cresswell)" <x@.y.z> wrote in message
news:dhmtov0kgau16c4opg7ktc2d69agtieh0t@.4ax.com...
> This doesn't work because the first INSERT is creating multiple
> records for multiple projects. @.@.IDENTITY, then, contains the Identity
> column value for the last tblWeekReportedLine record inserted.
> Consequently, all the hours records are then associated with
> that last value.

<snip
Without full DDL (including keys) and knowing how you populate your
variables, this is a guess, but it may be along the right lines - you can
join onto the tblWeekReportedLine table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedID,
w.IdentityColumn,
@.BeginDate1,
e.Estimate1,
@.DummyHoursTypeID,
@.DummyHoursType,
@.DummyTaxCodeID,
@.DummyTaxCode
FROM #EstimateLines e
join tblWeekReportedLine w
on e.ProjectID = w.ProjectID and
e.RelativeLineNumber = w.RelativeLineNumber
WHERE w.WeekReportedID = @.WeekReportedID1;

Simon|||RE/
>this is a guess,

Pretty good guess!

I'm still an SQL novice, and haven't learned to stop thinking sequential
processing yet...

Thanks. I may make my Monday deadline yet....

--
PeteCresswell

Cant work out this SQL to return 4 random records

I've got a product table in SQL 2000 that contains say these rows:

ProductID int
ProductName varchar
IsSpecial bit

I want to always return 4 random specials from a query and can do this fine by using:

SELECT TOP 4 ProductID,ProductName
FROM Products WHERE IsSpecial = 1
ORDER BY NEWID()

This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.

What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?

Is this possible?

Thanks

Hello my friend,

The following SQL should work. If you have any questions or problems with it, please let me know.

Kind regards

Scotty

SELECT TOP 4 * FROM
(
SELECT TOP 4 ProductID, ProductName FROM Products WHERE IsSpecial = 1

UNION ALL

SELECT ProductID, ProductName FROM Products WHERE IsSpecial = 0

) myTable

ORDER BY IsSpecial DESC, NewID()

|||

Excellent Scotty, that does exactly what i was looking for!

Cheers.