Case Statement Help Please

Hello,
I have tried the following syntax:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE
WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
END
and it is not working - I get the error of
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.
I'm not sure what is missing/wrong on that line?
Thanks!Your syntax is wrong, the THEN clause of a CASE cannot be a boolean
expression -- it must be scalar. Please refer to SQL Server Books Online for
syntax details.
Based on a brief glance through the code, the comparable t-SQL code would be
something along the lines of:
UPDATE stage_phx_fact_policy_rf
SET wind = ( SELECT short_code_2
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number )
WHERE occupancy = 'poultry'
AND EXISTS ( SELECT *
FROM wh_view_endorsement v1
WHERE v1.policy_number
= stage_phx_fact_policy_rf.policy_number
AND v1.policy_date_time
= stage_phx_fact_policy_rf.policy_date_time
AND v1.reference_number
= stage_phx_fact_policy_rf.sequence_number ) ;
If only unique columns are participating in the correlation, you can use
t-SQL FROM clause as well.
Anith|||Patrice,
You have to put the END statement at the point you are finished with the
CASE clause:
UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
SET WIND =
CASE WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
END
WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
John Scragg
"Patrice" wrote:

> Hello,
> I have tried the following syntax:
> UPDATE WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF
> SET WIND =
> CASE
> WHEN (WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.OCCUPANCY = 'POULTRY')
> THEN WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.WIND =
> WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.SHORT_CODE_2
> WHERE WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.IDENTIFIER = '072'
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_NUMBER
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.POLICY_DATE_TIME =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.POLICY_DATE_TIME
> AND WHJOINTDATA.DBO.WH_VIEW_ENDORSEMENT.REFERENCE_NUMBER =
> WHJOINTDATA.DBO.STAGE_PHX_FACT_POLICY_RF.SEQUENCE_NUMBER
> END
>
>
>
> and it is not working - I get the error of
> Server: Msg 170, Level 15, State 1, Line 6
> Line 6: Incorrect syntax near '='.
>
> I'm not sure what is missing/wrong on that line?
> Thanks!
>

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
Ludek
Hi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:

> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John
|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions
|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can not
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:

> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>
|||Hi Ludek
"Ludek" wrote:

> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with this
> 5 fields from all database + detailed view for one record. But database have
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first example?
John

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
LudekHi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:

> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can no
t
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:

> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
>
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>|||Hi Ludek
"Ludek" wrote:

> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can
not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with th
is
> 5 fields from all database + detailed view for one record. But database ha
ve
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first exampl
e?
John

CASE problem (or is it a null problem?)

I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyFirst, you should be using IS NULL. Second, you should have an ELSE in each
of those CASE's. Otherwise, the default is NULL. However, this can be done
without CASE's. Looks like you may want:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT
ISNULL (TIN, 'Dummy')
, ISNULL (PSTATE, 'Dummy')
, ISNULL (PCITY, 'Dummy')
, PNAME1
, PADDR
FROM dbo.EXPTRANS
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company|||More correct is
Case WHEN TIN IS NULL THEN 'Dummy' ELSE TIN END
Bojidar Alexandrov|||Thank you all
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
> I have this insert statement:
> INSERT INTO dbo.TinNormalized
> (TIN, PState, PCity, PName1, PAddr)
> SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
> Case When PSTATE = NULL Then 'Dummy' END,
> Case When PCITY = Null Then 'Dummy' END,
> PNAME1, PADDR
> FROM dbo.EXPTRANS
> it fails with this message:
> Cannot insert the value NULL into column 'TIN', table
> 'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I have tried it with IS NULL as well and yet I get the same error. What am
I
> missing?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>

CASE problem (or is it a null problem?)

I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
First, you should be using IS NULL. Second, you should have an ELSE in each
of those CASE's. Otherwise, the default is NULL. However, this can be done
without CASE's. Looks like you may want:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT
ISNULL (TIN, 'Dummy')
, ISNULL (PSTATE, 'Dummy')
, ISNULL (PCITY, 'Dummy')
, PNAME1
, PADDR
FROM dbo.EXPTRANS
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
|||More correct is
Case WHEN TIN IS NULL THEN 'Dummy' ELSE TIN END
Bojidar Alexandrov
|||Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
> I have this insert statement:
> INSERT INTO dbo.TinNormalized
> (TIN, PState, PCity, PName1, PAddr)
> SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
> Case When PSTATE = NULL Then 'Dummy' END,
> Case When PCITY = Null Then 'Dummy' END,
> PNAME1, PADDR
> FROM dbo.EXPTRANS
> it fails with this message:
> Cannot insert the value NULL into column 'TIN', table
> 'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I have tried it with IS NULL as well and yet I get the same error. What am
I
> missing?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>
sql

CASE problem (or is it a null problem?)

I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyFirst, you should be using IS NULL. Second, you should have an ELSE in each
of those CASE's. Otherwise, the default is NULL. However, this can be done
without CASE's. Looks like you may want:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT
ISNULL (TIN, 'Dummy')
, ISNULL (PSTATE, 'Dummy')
, ISNULL (PCITY, 'Dummy')
, PNAME1
, PADDR
FROM dbo.EXPTRANS
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company|||More correct is
Case WHEN TIN IS NULL THEN 'Dummy' ELSE TIN END
Bojidar Alexandrov|||Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
> I have this insert statement:
> INSERT INTO dbo.TinNormalized
> (TIN, PState, PCity, PName1, PAddr)
> SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
> Case When PSTATE = NULL Then 'Dummy' END,
> Case When PCITY = Null Then 'Dummy' END,
> PNAME1, PADDR
> FROM dbo.EXPTRANS
> it fails with this message:
> Cannot insert the value NULL into column 'TIN', table
> 'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I have tried it with IS NULL as well and yet I get the same error. What am
I
> missing?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>

case expression stor proc, need some help

ALTER PROCEDURE dbo.TEST_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <--this part gave me an error, because of the comma,
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END

The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~Are you saying you don't get the data you want if you remove the datename part from your group by clause? You are getting the month name out by the select part of your procedure and I don't see the need to also group by it if you only want to group by number.|||well, I am trying to get data displayed in the name of the month, but not in ABC order.
i.e.

January
Feb
March

instead of ABC order,
April
December
Febuary

I did the sql before grouping them together.
And this worked,

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)

Yet, the GROUP BY clause consist oftwoexpressions for it to function, (from my understanding)
and I don't know how to make that clause work in a CASE expression.

Thx in advance~|||oh yea, this doesn't work from my understanding:

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)|||How about this:-

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END,
CASE @.varDate
WHEN 'Year' Then ??
WHEN 'Quarter' Then ??
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END

I think it would only work if you could put something in for the Year and Quarter too (where the ?? are). Might not work at all.
The only other thing would be to perhaps use a sql if to have 2 different selects, one for month with case no longer needed and one for the other 2 using case:-

if @.varDate='Month'
begin
SELECT DATENAME(mm, CALLSTARTTIME), COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)
end
else
begin
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
end|||Thank you Brian. That will work.

Side Question: is there a way to name the heading by case?

e.g.
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END,|||Yes there is:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname|||SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname

Can myheadingname varies by case? like can it be a parameter/variable,and it will display different headings depending on the case selected.

e.g.
When @.varDate = 'Year', the column header will be 'Year'.
When @.varDate = 'Month', the column header will be 'Month'.
When @.varDate = 'Quarter', the column header will be 'Quarter'

the above example, as myheadingname is a generalized header. It will not differ no matter what @.varDate is.|||I don't think you can but no harm in trying something like that:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) as 'Month'
END

You will probably get an error if it doesn't work.|||yup, got an error.|||Well other than that you can expand the if for each possibility and do without the case.

Can't you have a variable TOP in a select statement?

Hi,
I got a stored procedure like this
CREATE PROCEDURE dbo.readImport
(
@.Start INTEGER,
@.Number INTEGER
)
AS
SELECT TOP @.Number * FROM Import WHERE RowID >= @.Start ORDER BY RowID
GO
However, it doesn't seem to like having an unknown @.Number.
Any ideas?
MortenHi Morten,
If you are using SQL 2k its not possible.
The only thing is to use dynmiac sql for that.
HTH, Jens Suessmeyer.|||Ok, thanks
Morten
On Fri, 11 Nov 2005 09:30:25 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>|||... or SET @.@.ROWCOUNT...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131697825.522144.101160@.g49g2000cwa.googlegroups.com...
> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>

can't update table owned by dbo with impersonation account

I am using an impersonation account to execute a procedure via
executenonquery in asp.net. If the table owner is dbo I get the following
error in .net:
input string was not in a correct format.
When I step through my code it actually is saying that permission is denied
on the table. I've given the impersonation account full priveledges on this
table and it still doesn't work. However, if I change the owner of the tabl
e
to somone else it works fine. Any suggestions on how to resolve this would
be greatly appreciated.Can you show us your query?
"ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
news:E5FEB9AA-D527-4AC5-9B08-03FD45EDDFAF@.microsoft.com...
>I am using an impersonation account to execute a procedure via
> executenonquery in asp.net. If the table owner is dbo I get the following
> error in .net:
> input string was not in a correct format.
> When I step through my code it actually is saying that permission is
> denied
> on the table. I've given the impersonation account full priveledges on
> this
> table and it still doesn't work. However, if I change the owner of the
> table
> to somone else it works fine. Any suggestions on how to resolve this
> would
> be greatly appreciated.|||Here you go.
TRUNCATE TABLE dbo.MYTABLE
INSERT INTO dbo. MYTABLE(THEID,ERRORFLAG,ERRORCODE,CREATI
ONDATE)
VALUES(@.IDValue ,'Y',@.LocalError,GETDATE())
When I run my code in asp.net I get the error "input string is not in the
correct format"
If I use
sp_changeobject 'MYTABLE', 'newowner'
and run it again it works fine?
"Uri Dimant" wrote:

> Can you show us your query?
> "ASP Developer" <ASPDeveloper@.discussions.microsoft.com> wrote in message
> news:E5FEB9AA-D527-4AC5-9B08-03FD45EDDFAF@.microsoft.com...
>
>