Problem with CASE Expression

Hi, all here,

I have a problem with CASE expression in my SQL staments.

the problem is:

when I tried to just partly update the column a , I used the CASE expression : set a=case when b=null then 'null' end

the result was strange: then all the values for column a turned to null.

so what is the problem tho?

Thanks a lot in advance for any guidance.

Is it something like this you're trying to do?

create table #x ( a int null, b int null )
go
insert #x
select 1, 1 union all
select 2, null union all
select 3, 1 union all
select 4, null
go
select * from #x
go

a b
-- --
1 1
2 NULL
3 1
4 NULL

(4 row(s) affected)

update #x
set a = case when b is null then null else a end
go

select * from #x
go

a b
-- --
1 1
NULL NULL
3 1
NULL NULL

(4 row(s) affected)

drop table #x
go

/Kenneth

|||set a=case when b IS null then 'null' end|||The issue is with your comparison of the column value against NULL using equality operator. By default, <any non null value> <> NULL unless you set ANSI_NULLS to off and this affects few operations in the server. You can check the Books Online for more details. The recommended syntax is to use the IS NULL or IS NOT NULL clauses for checking NULL values.

Problem with case expression

In the Portal1 case expression in the script at the bottom I would like
to replace where the result 1 is returned, with the substring function
returned as Portal

{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}

However, I am experiencing errors. I think it is because The substring
function will not return a number as the case expression expects so I
must incorporate cast or convert, but do not know how. Can you help?

SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,
SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
Field1) - 8)
- (CHARINDEX('tonep', Field1) + 4)) AS portal,
Table.*
FROM TableOn 30 Sep 2005 02:02:28 -0700, chudson007@.hotmail.com wrote:

>In the Portal1 case expression in the script at the bottom I would like
>to replace where the result 1 is returned, with the substring function
>returned as Portal
>{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
>Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}
>However, I am experiencing errors. I think it is because The substring
>function will not return a number as the case expression expects so I
>must incorporate cast or convert, but do not know how. Can you help?
(snip)

Hi chudson007,

From your description, I don't understand what you're trying to achieve.
Maybe you could illustrate this some more by providing

- The table structure, posted as CREATE TABLE statements (irrelevant
colunms may be omitted, but please include all constraints)
- Some illustrative rows of sample data, posted as INSERT statements
- The expected output or results

See www.aspfaq.com/5006 for more details and some hints on how to
assemble this info.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi

As Hugo has requested DDL and sample data is really the only way to know
exactly what is happening.

But, when Field1 is less than 8 characters or does not contain ".txt" or
"tonep" you may get problems.

John

<chudson007@.hotmail.com> wrote in message
news:1128070948.064118.208110@.z14g2000cwz.googlegr oups.com...
> In the Portal1 case expression in the script at the bottom I would like
> to replace where the result 1 is returned, with the substring function
> returned as Portal
> {SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
> Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}
> However, I am experiencing errors. I think it is because The substring
> function will not return a number as the case expression expects so I
> must incorporate cast or convert, but do not know how. Can you help?
>
>
> SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,
> SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',
> Field1) - 8)
> - (CHARINDEX('tonep', Field1) + 4)) AS portal,
> Table.*
> FROM Table

problem with an xpath parameter to StoredProc

Hey,
I am getting a parse error on this SP expression. I'd assumed this work
work.
error is "Incorrect syntax near the keyword 'exists'."
CREATE PROCEDURE dbo.sp_ListTemplates
(
@.PropXPath varchar(1024),
)
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SELECT fileName, docProps, version
FROM Template
WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
END
GO
Chris Harrington
Active Interface, Inc.
http://www.activeinterface.comNever mind - "exist" not "exists"
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Hey,
> I am getting a parse error on this SP expression. I'd assumed this work
> work.
> error is "Incorrect syntax near the keyword 'exists'."
> CREATE PROCEDURE dbo.sp_ListTemplates
> (
> @.PropXPath varchar(1024),
> )
> AS
> BEGIN
> SET XACT_ABORT ON
> SET NOCOUNT ON
> SELECT fileName, docProps, version
> FROM Template
> WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
> END
> GO
> Chris Harrington
> Active Interface, Inc.
> http://www.activeinterface.com
>|||Seems it doesn't work after all. SP compiles but when I pass in an XPath
expression, it returns all records - regardless of the XPath I pass in:
EXEC dbo.sp_ListTemplates '/o:CustomDocumentProperties[o:Category="6"]';
GO
-- returns all records, not just those which match xpath
Obviously I don't fully understand the use of sql:variable() here.
Anyone got a solution for passing a string param which is used as an XPath
expression?
Chris
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Hey,
> I am getting a parse error on this SP expression. I'd assumed this work
> work.
> error is "Incorrect syntax near the keyword 'exists'."
> CREATE PROCEDURE dbo.sp_ListTemplates
> (
> @.PropXPath varchar(1024),
> )
> AS
> BEGIN
> SET XACT_ABORT ON
> SET NOCOUNT ON
> SELECT fileName, docProps, version
> FROM Template
> WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
> END
> GO
> Chris Harrington
> Active Interface, Inc.
> http://www.activeinterface.com
>|||Chris,
The behavior you're experiencing is by design. When you use sql:variable()
the way you do you construct a text node, and therefore the exist method wil
l
always return one since the expression didn't return the empty sequence. The
contents of the variable are in no way interpreted as an XPath expression.
Currently there is no way to parameterize the expression used by the exist
method. You can however construct the whole T-SQL statement in a string vari
able
and run it using sp_executesql.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:uTDXOEKlGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Seems it doesn't work after all. SP compiles but when I pass in an XPath
> expression, it returns all records - regardless of the XPath I pass in:
> EXEC dbo.sp_ListTemplates '/o:CustomDocumentProperties[o:Category="6"]';
> GO
> -- returns all records, not just those which match xpath
> Obviously I don't fully understand the use of sql:variable() here.
> Anyone got a solution for passing a string param which is used as an XPath
> expression?
> Chris
>
> "ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
> news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
>

problem with an UPDATE...

trying to create an UPDATE but am getting and error.
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

update XAPCHECKS
set xapck_amt =
(select sum(apph_paymnts), * from APPHISTF
LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = '01') and (apph_vend = '1010') and
xapck_check = apph_payck and xapck_chk_type = (CASE chm_type WHEN null THEN ' ' ELSE chm_type END) and xapck_check_status = (CASE chm_stat when null then ' ' ELSE chm_stat END)
and xapck_bank = apt_bank
GROUP by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id, apph_paymnts)the problem is here --

set xapck_amt = (select sum(apph_paymnts), *

the error says the subquery has more than one column|||"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."|||ah, that's because the subquery used in a SET can return only one column, one row

it's called a scalar subquery because it's supposed to return only a single scalar value|||not sure why i had that in there but it seems to working ok.
thanks

Problem with "Compile Error"

I got some problem about message "Compile Error, In Query expression "
Someone help me please
Best Regard
My E-mail = paiboonm@.cuel.co.thoooooooommmmmmmmmmmmmmm

oooooooommmmmmmmmmmmmmm

oooooooommmmmmmmmmmmmmm

Nope not getting anything on the telepathic channel...

maybe if you post your code and the actual error message

Problem while giving Quarter function

Sir,

When I am giving the command like Quarter(DateOfOrder) in new named dimension expression column , one error is showing ' Quarter is not recognised built in function ' . Please help me to find out the quarter of Date of Order column..

Thanks in advance..

Regards

Polachan

You need to use an expression such as

DATEPART(quarter,DateOfOrder)

To get the result you require

|||

Dear Sir,

Thanks a lot

Sir Please can u give me a help for the following problem while deployment of the project

Error 4

When I am deploying the project I got the following error. Please help me sir

Errors in the OLAP storage engine: An error occurred while processing the 'Product Tran Header' partition of the 'Product Tran Header' measure group for the 'Test' cube from the productreport database. 0 0

I done the following steps

1. added new measure selecting new source table

2. selected one column dateoforder

3. Add new dimension as without using data source

4. selected server time dimension

5. Selected Year/Month/Quarter/date

6 Selected fiscal year

7.Selected dimension usage in cube desgn

8. selected time dimension and selected regular relation, Granulary attribute as Date

9. Measure group table 'Product tran header' new measure group

10 selected measure group column as dateoforder.

after that while deploying the above mentioned error will come

Please help

|||As far as I know, I answered your question. Why have you answered that with a completely different question ? |||

Sir,

I wan to know how to give relationship Servertime Dimension with the column in the table DateOfOreder. When I am giving this with the mentioned step I got the error. Please help me sir...

Problem using the textAlign expression

I am trying to use an iif function in the textAlign property.

=iif(A=B, left, center)

I am getting an error on the left...it seems to be expecting arguments for the left function.

How do I get around this?

Forscuis

Try

=IIF(A=B,"Left","Center")

This is should resolve your issue.

Ham

|||thanks that worked.|||

not a problem,

Can you mark it as answers so that others will searching for answers will find our solution?

Ham