Incorrect Totals

Here's my cube structure in a nutshell.

I've got a measure group called MG1 which contains measure "Units". I've got another measure group called MG2 which contains measure "Price".

I want to add a new measure called "Value" that should correspond to Units * Price.

I'm trying to use MDX script to do this calculation:

[Measures].[Value] = [Measures].[Units] * [Measures].[Price]

This seems to produce the right results at leaves level, but when i check the Totals in the Cube Browser they are incorrect because in the totals calculation Prices are summed up and multiplied by the sum of Units. The Totals should instead be calculated by doing a sum of all the leaves Values if you see what i mean.

Is there anything obvious i'm missing here?

To try and explain further, here're the (wrong) results i'm getting:

Units Price Value
Product A -27,880 0.00570 -158.92
Product B -11,845 0.00560 -66.33
Total -39,725 0.01130 -448.89

And here're the results i would like to get:

Units Price Value
Product A -27,880 0.00570 -158.92
Product B -11,845 0.00560 -66.33
Total -39,725 0.01130 -225.25

As you can see from above the problem is to do with the way Value is calculated for Total.

Any suggestion on how i could achieve the desired results through MDX script and/or changes in cube design ?

|||

Do you have the option of moving Price to a dimension? This is often how price is recorded if it is describing something like a product.

Barring this, I would recommend calculating Units * Price in your DSV and then simply recording it in a new measure in your MG1 measure group using an additive function.

Good luck,
Bryan

|||

Price is in measure group MG2 because it also depends on the value of another dimension "Catalog". The Catalog dimension is not directly linked to MG1. So depending on which catalog is chosen we have different prices accross products. So i really would need to keep Units and Price in 2 separate fact tables (measure groups) to avoid having a huge single fact table with all combinations.

I think what i'm trying to achieve is fairly simple, ie. the only issue is to do with Totals. But maybe i'm missing something.

I would really need to get this working through MDX script because i will need to use the "Value" measure inside other calculated measures so i cannot even use a Measure Expression, i think.

|||

So, if a product can have multiple prices depending on the catalog you have to know the catalog used in the purchase. In that case, does MG1 also have a direct relationship with the Catalog dimension?

Alternatively, can this be resolved in the DSV? Could you create a named calculation in the DSV that multiplies the units by price to give you a transaction total? The total seems to be additive. This might be an easier solutoin for you.

Bryan

|||

This seems like a good scenario for "measure expressions" - if you inspect the Adventure Works cube, various Sales Amount measures are multiplied by currency rates. For example, you could create a new "sum" measure like [Value] on the MG1 "Units" field. Then, for this new measure, configure the "Measure Expression" property as:

[Measures].[Value] * [Measures].[Price]

This entry in Chris Webb's blog discusses measure expressions:

...

But here's a cool cube design feature that doesn't cause any conflict of interest for me - measure expessions. The easiest way to explain what they are is to explain one scenario where they're useful. In AS2K, if you've tried to model currency conversion, you're probably aware that you need to multiply the measure values in your main fact table by the currency rate before any aggregation of the resulting values takes place. You then have two choices for your cube: either precalculate the values in the fact table itself or in a view, which leads to much faster queries but also much bigger cubes, and which means the rates can't then be altered without reprocessing; or do the currency conversion at runtime using MDX, which generally leads to slower queries but which allows users to change the currency rates dynamically (for example using writeback). Neither of these options are exactly ideal so in AS2005 measure expressions offer a kind of halfway house - they are calculated at query time and yet are much faster than the equivalent MDX, but the price you pay is that they are nowhere near as flexible as calculated members in terms of the calculations you can define.

...

|||I could not get the cube calculations correct with the 2 Fact tables as described above. So I've finally opted for 1 Fact table with all the "combinations": CatalogID, ProductID, Units, ProductPriceForCatalog, Value, etc. This means that Value is calculated as part of my SSIS package which updates the Fact table with new entries every day, so this calculation is not performed in the cube via MDX script any more. The downside of this solution is that my Fact table will contain many more rows...

Incorrect Totals

Here's my cube structure in a nutshell.

I've got a measure group called MG1 which contains measure "Units". I've got another measure group called MG2 which contains measure "Price".

I want to add a new measure called "Value" that should correspond to Units * Price.

I'm trying to use MDX script to do this calculation:

[Measures].[Value] = [Measures].[Units] * [Measures].[Price]

This seems to produce the right results at leaves level, but when i check the Totals in the Cube Browser they are incorrect because in the totals calculation Prices are summed up and multiplied by the sum of Units. The Totals should instead be calculated by doing a sum of all the leaves Values if you see what i mean.

Is there anything obvious i'm missing here?

To try and explain further, here're the (wrong) results i'm getting:

Units Price Value
Product A -27,880 0.00570 -158.92
Product B -11,845 0.00560 -66.33
Total -39,725 0.01130 -448.89

And here're the results i would like to get:

Units Price Value
Product A -27,880 0.00570 -158.92
Product B -11,845 0.00560 -66.33
Total -39,725 0.01130 -225.25

As you can see from above the problem is to do with the way Value is calculated for Total.

Any suggestion on how i could achieve the desired results through MDX script and/or changes in cube design ?

|||

Do you have the option of moving Price to a dimension? This is often how price is recorded if it is describing something like a product.

Barring this, I would recommend calculating Units * Price in your DSV and then simply recording it in a new measure in your MG1 measure group using an additive function.

Good luck,
Bryan

|||

Price is in measure group MG2 because it also depends on the value of another dimension "Catalog". The Catalog dimension is not directly linked to MG1. So depending on which catalog is chosen we have different prices accross products. So i really would need to keep Units and Price in 2 separate fact tables (measure groups) to avoid having a huge single fact table with all combinations.

I think what i'm trying to achieve is fairly simple, ie. the only issue is to do with Totals. But maybe i'm missing something.

I would really need to get this working through MDX script because i will need to use the "Value" measure inside other calculated measures so i cannot even use a Measure Expression, i think.

|||

So, if a product can have multiple prices depending on the catalog you have to know the catalog used in the purchase. In that case, does MG1 also have a direct relationship with the Catalog dimension?

Alternatively, can this be resolved in the DSV? Could you create a named calculation in the DSV that multiplies the units by price to give you a transaction total? The total seems to be additive. This might be an easier solutoin for you.

Bryan

|||

This seems like a good scenario for "measure expressions" - if you inspect the Adventure Works cube, various Sales Amount measures are multiplied by currency rates. For example, you could create a new "sum" measure like [Value] on the MG1 "Units" field. Then, for this new measure, configure the "Measure Expression" property as:

[Measures].[Value] * [Measures].[Price]

This entry in Chris Webb's blog discusses measure expressions:

...

But here's a cool cube design feature that doesn't cause any conflict of interest for me - measure expessions. The easiest way to explain what they are is to explain one scenario where they're useful. In AS2K, if you've tried to model currency conversion, you're probably aware that you need to multiply the measure values in your main fact table by the currency rate before any aggregation of the resulting values takes place. You then have two choices for your cube: either precalculate the values in the fact table itself or in a view, which leads to much faster queries but also much bigger cubes, and which means the rates can't then be altered without reprocessing; or do the currency conversion at runtime using MDX, which generally leads to slower queries but which allows users to change the currency rates dynamically (for example using writeback). Neither of these options are exactly ideal so in AS2005 measure expressions offer a kind of halfway house - they are calculated at query time and yet are much faster than the equivalent MDX, but the price you pay is that they are nowhere near as flexible as calculated members in terms of the calculations you can define.

...

|||I could not get the cube calculations correct with the 2 Fact tables as described above. So I've finally opted for 1 Fact table with all the "combinations": CatalogID, ProductID, Units, ProductPriceForCatalog, Value, etc. This means that Value is calculated as part of my SSIS package which updates the Fact table with new entries every day, so this calculation is not performed in the cube via MDX script any more. The downside of this solution is that my Fact table will contain many more rows...

Incorrect results from FTS on multiple columns

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

The KB article only applies to SQL Server 7.0 as indicated. If you are having problems with your implementation please post a repro script with some sample data and the expected results.

|||My database contains the following structure:
Table name: Resumes
Columns:
ResumeID bigint identity(1,1),
Title varchar(100),
ResumeText varchar(max),
Skills varchar(250)

The ResumeID is set as primary key and columns Title, ResumeText and Skills
are FT indexed.

A record contains "Software Developer .NET" in Title,
resume text which also contains word "india" in ResumeText and
"C#, ASP.NET and SQL Server" in Skills columns.

If I pass a query as ("software" AND "developer" AND "india" AND "asp.net") in CONTAINSTABLE or CONTAINS which looks for a resume which statisfies this condition.

The queries which I wrote search on all the column which are FT indexed as a
result of which even if I pass the above query it does not returns any result.

It seems that the FT query is working on each columns individually.
That's why if I pass ("software" AND "developer") in a query it locates the record.

Should I create another column and dump the data of
Title, ResumeText and Skills in it and perform a FTS on this new column?
or
Is there any other feasible method to solve this problem?

incorrect query reults

Hi,
another problem occured!-)
I did the following SQL-query:
select * from queue_suchsourceindex
where contains(text, ' "oenorm*" ')
But in the result there are many rows, that do not contain the word "oenorm"
in its text field. First i thought i mixed somethiong up in Application, but
everything is working right there. Just the query return the wrong results.
I have no clue, how to find out what the problem is.
thx in advance, Gerald.
my configuration:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
I forgot to tell, that the query
select id, url from queue_suchsourceindex
where text like '%oenorm%'
returns no rows, as it should.
thx, Gerald.
"Gerald Baeck" schrieb:

> Hi,
> another problem occured!-)
> I did the following SQL-query:
>
> select * from queue_suchsourceindex
> where contains(text, ' "oenorm*" ')
> But in the result there are many rows, that do not contain the word "oenorm"
> in its text field. First i thought i mixed somethiong up in Application, but
> everything is working right there. Just the query return the wrong results.
> I have no clue, how to find out what the problem is.
> thx in advance, Gerald.
> my configuration:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
|||Gerald,
Why would you expect contains(text, ' "oenorm*" ') to return results when
using text like '%oenorm%' does not return results?
While often the T-SQL LIKE will return different results when compared to
CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
matching" method and using leading and trailing wildcards (%), then most
likely CONTAINS or FREETEXT will not return results as well as the search
string is most likely not in your column: text.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...[vbcol=seagreen]
> I forgot to tell, that the query
> select id, url from queue_suchsourceindex
> where text like '%oenorm%'
> returns no rows, as it should.
> thx, Gerald.
>
> "Gerald Baeck" schrieb:
"oenorm"[vbcol=seagreen]
but[vbcol=seagreen]
results.[vbcol=seagreen]
Windows[vbcol=seagreen]
|||John,
its the other way round. The CONTAINS-Query returns many rows and the
LIKE-Query does not. The LIKE-QUery return the correct resultset in
that case.
thx, Gerald.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Gerald,
> Why would you expect contains(text, ' "oenorm*" ') to return results when
> using text like '%oenorm%' does not return results?
> While often the T-SQL LIKE will return different results when compared to
> CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
> matching" method and using leading and trailing wildcards (%), then most
> likely CONTAINS or FREETEXT will not return results as well as the search
> string is most likely not in your column: text.
> Regards,
> John
>
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...
> "oenorm"
> but
> results.
> Windows
|||Thanks, Gerald,
Could you proved the exact text from a few of the rows that contain the
string 'oenorm', including all punctuation characters?
Is this HTML code or plain text that is stored in a text or varchar column?
Also, what is the language of the text stored in the column?
All of these factors, when considered with the OS platform (in your case
Win2K) can affect the results from the CONTAINS query.
Regards,
John
"Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> John,
> its the other way round. The CONTAINS-Query returns many rows and the
> LIKE-Query does not. The LIKE-QUery return the correct resultset in
> that case.
> thx, Gerald.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
"pattern[vbcol=seagreen]
search[vbcol=seagreen]
Application,[vbcol=seagreen]
14:22:05[vbcol=seagreen]
|||Hi John,
First of all thanks for your patient help!-)
I checked all the rows manually and i am sure that there is no string like
'oenorm'. You can have a look at this File
http://www.webwatch.at/oenorm_example.txt.
The data is stored in plain text and contains no html-tags.
The language of the text-field is german.
regards, Gerald.
"John Kane" wrote:

> Thanks, Gerald,
> Could you proved the exact text from a few of the rows that contain the
> string 'oenorm', including all punctuation characters?
> Is this HTML code or plain text that is stored in a text or varchar column?
> Also, what is the language of the text stored in the column?
> All of these factors, when considered with the OS platform (in your case
> Win2K) can affect the results from the CONTAINS query.
> Regards,
> John
>
> "Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
> news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...
> when
> to
> "pattern
> search
> Application,
> 14:22:05
>
>
|||You're welcome, Gerald,
I too searched the oenorm_example.txt file and found no hit on oenorm. While
asking for the @.@.version info is a start, sometimes for these more difficult
cases, I've most often ask for additional info as the different results may
be because of the language and possibly not using the correct "Language for
Word Breaker". Could you post the output from the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
-- may need to set show advanced options
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Depending upon the "Language for Word Breaker" that you are using along with
German text, this may explain the differences in results.
Thanks,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...[vbcol=seagreen]
> Hi John,
> First of all thanks for your patient help!-)
> I checked all the rows manually and i am sure that there is no string like
> 'oenorm'. You can have a look at this File
> http://www.webwatch.at/oenorm_example.txt.
> The data is stored in plain text and contains no html-tags.
> The language of the text-field is german.
> regards, Gerald.
> "John Kane" wrote:
column?[vbcol=seagreen]
compared[vbcol=seagreen]
most[vbcol=seagreen]
message[vbcol=seagreen]
word[vbcol=seagreen]
wrong[vbcol=seagreen]
Edition on[vbcol=seagreen]
|||Hi John,
here is the info:
Deutsch
default full-text language0214748364710311031
5Queue_SuchsourceC:\Programme\Microsoft SQL Server\MSSQL\FTDATA01
dboQueue_SuchsourceIndexPK_Queue_SuchsourceIndex41Queue_Suchsource
dbo1129771082Queue_SuchsourceIndexText2NULLNULL1031
Queue_SuchsourceIndexdbouser table2004-11-15 01:22:52.980
Urlvarcharno255 nononoSQL_Latin1_General_CP1_CI_AS
Texttextno16 yes(n/a)(n/a)German_PhoneBook_CI_AS
Zeitsmalldatetimeno4 no(n/a)(n/a)NULL
IDintno410 0 no(n/a)(n/a)NULL
ID110
No rowguidcol column defined.
PRIMARY
PK_Queue_SuchsourceIndexclustered, unique, primary key located on PRIMARYID
PRIMARY KEY (clustered)PK_Queue_SuchsourceIndex(n/a)(n/a)(n/a)(n/a)ID
WebinvestigatorDB.dbo.Queue_Clippings: FK_Queue_Treffer_Queue_SuchsourceIndex
thx, Gerald.
"John Kane" wrote:

> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||I noticed something interesting. I get now hits with the term "oenorm", but
the couple of incorrect hits with "oenorm*".
regards, Gerald.
"John Kane" wrote:

> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||Thanks, Gerald,
You're using German (windows collation 1031) for all aspects of your server,
including the @.@.language, default FTS language as well as the "Language for
Word Breaker" for your FT-enabled table (Queue_SuchsourceIndex) Text (text)
column. Additionally, you're using SQL Server 2000 SP3 on NT 5.0 (or Win2K),
so you're using the German infosoft.dll wordbreaker.
Now, the issue is understanding how the Win2K German infosoft.dll
wordbreaker breaks or tokenizes the German *word* oenorm. Getting hits with
"oenorm", but the couple of incorrect hits with "oenorm*", might be at the
heart of this issue with the Win2K German infosoft.dll wordbreaker, as I
neither read or write German, can you tell me what this word means in
English? Is it normally a stand-alone German word or is it a part of a
compound word in German? If so, is it usually used as a the leading part or
the trailing part of the compound word?
FYI, the Win2K German infosoft.dll wordbreaker has been problematic at best
and at worst not very useful and for this and other reasons, MS has
developed a new word breaker (langwbrk.dll) for the WinXP and Win2003 OS
platforms. Although, I'm not sure if for the German language, if this
wordbreaker is any better, I'm sad to say.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:5703BB7B-F1FD-43BE-BF4D-BA777577214F@.microsoft.com...
> I noticed something interesting. I get now hits with the term "oenorm",
but[vbcol=seagreen]
> the couple of incorrect hits with "oenorm*".
> regards, Gerald.
> "John Kane" wrote:
While[vbcol=seagreen]
difficult[vbcol=seagreen]
may[vbcol=seagreen]
for[vbcol=seagreen]
with[vbcol=seagreen]
like[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
results[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
2002[vbcol=seagreen]

Incorrect Positioning?

Hi,

I have created a couple of reports with a header that contains an embedded image and a textbox just to the right of the image, vertically aligned. So far so good but when i deploy the report it doesn't show exactly as in the preview...
Instead the textbox seems to goto another column? Yes it seems that some how a new column is created and the textbox is the only thing to be in it...

Anyone experiencing this? What can be causing this?

Best Regards,

Luis Simoes

It sounds like the image and the textbox overlap. This is only supported in image-based rendering (e.g. Preview, PDF). We currently don't support overlapping items in HTML (when viewed e.g. through report viewer in report manager). This is why one of the overlapping items will get pushed to the right/bottom in the generated output.

-- Robert

|||

No, they are not overlapped. They are side by side!

What can it be? That's really weird behavior.. only the text box on the right side of the embedded image get's to a new blank column...

This is the layout of the report:

Image here	Textbox here
Rest of the report

|||I have the same problem. The funniest thing is it depends of the image if textbox is moved or not. When I show the bigger image (property set to fit) textbox is ok side by side but when the image is smaller then the textbox is moved to the next page. Any sollution in this case?|||Got it! I inserted both image and textbox to the header-section and now it works. Strange...sql

Incorrect Positioning?

Hi,

I have created a couple of reports with a header that contains an embedded image and a textbox just to the right of the image, vertically aligned. So far so good but when i deploy the report it doesn't show exactly as in the preview...
Instead the textbox seems to goto another column? Yes it seems that some how a new column is created and the textbox is the only thing to be in it...

Anyone experiencing this? What can be causing this?

Best Regards,

Luis Simoes

It sounds like the image and the textbox overlap. This is only supported in image-based rendering (e.g. Preview, PDF). We currently don't support overlapping items in HTML (when viewed e.g. through report viewer in report manager). This is why one of the overlapping items will get pushed to the right/bottom in the generated output.

-- Robert

|||

No, they are not overlapped. They are side by side!

What can it be? That's really weird behavior.. only the text box on the right side of the embedded image get's to a new blank column...

This is the layout of the report:

Image here	Textbox here
Rest of the report

|||I have the same problem. The funniest thing is it depends of the image if textbox is moved or not. When I show the bigger image (property set to fit) textbox is ok side by side but when the image is smaller then the textbox is moved to the next page. Any sollution in this case?|||Got it! I inserted both image and textbox to the header-section and now it works. Strange...

Inconsistent Search Results

When searching for this street using this parameter:
'L''Avenue De La Reine Elizabeth'
I get a result ( The record contains 'L''Avenue De La Reine Elizabeth The
Second',)
but When I try searching for the whole thing, it won't return any rows, nor
will it for
'L''Avenue De La Reine Elizabeth The'.
Is there a word limit of some sort on full text search?
Can anyone throw some light on this?
Thanks In advance
Warren
Warren,
Could you post the full output of the following SQL script as it is helpful
info in troubleshooting SQL FTS issues?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
Reine Elizabeth The Second'? Could you post the exact SQL query that you
were using?
Thanks,
John
"WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> When searching for this street using this parameter:
> 'L''Avenue De La Reine Elizabeth'
> I get a result ( The record contains 'L''Avenue De La Reine Elizabeth The
> Second',)
> but When I try searching for the whole thing, it won't return any rows,
nor
> will it for
> 'L''Avenue De La Reine Elizabeth The'.
> Is there a word limit of some sort on full text search?
> Can anyone throw some light on this?
> Thanks In advance
> Warren
>
|||Thanks for replying, John - here is the stuff you asked for, though I can't
send you the SQL query itself, though it isd interesting to Note that if I
restrict the search to 5 words, it will search & find the record(s). If any
more than that, it does not bother searching!
------
us_english
(1 row(s) affected)
-----------
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
-----------
- -- --
5 Address Data
C:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA
0 4
6 Cat_Desc
C:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA
0 0
(2 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- --
------
dbo
building
PK_building
1
1 Address Data
dbo
DeliveryPoint
PK_DeliveryPoint
1
1 Address Data
dbo
parish
PK_parish
1
1 Address Data
dbo
road
PK_road
1
1 Address Data
(4 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID FULLTEXT_LANGUAGE
------
------
------
------
-- --
dbo
373576369 building
name
2 NULL
NULL 1033
dbo
741577680 DeliveryPoint
Business
2 NULL
NULL 1033
dbo
741577680 DeliveryPoint
SubElementDesc
3 NULL
NULL 1033
dbo
741577680 DeliveryPoint
LocationOnStreet
5 NULL
NULL 1033
dbo
741577680 DeliveryPoint
NameOfTerrace
6 NULL
NULL 1033
dbo
741577680 DeliveryPoint
Locality
8 NULL
NULL 1033
dbo
741577680 DeliveryPoint
PostCode
10 NULL
NULL 1033
dbo
533576939 parish
name
2 NULL
NULL 1033
dbo
709577566 road
name
2 NULL
NULL 1033
(9 row(s) affected)
Name
Owner
Type Created_datetime
------
------
road
dbo
user table 2004-05-06
10:14:12.887
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
------
id
int
no 4 10
0 no (n/a)
(n/a) NULL
name
varchar
no 80
no no
no SQL_Latin1_General_CP1_CI_AS
parishid
int
no 4 10
0 no (n/a)
(n/a) NULL
usrn
float
no 8 53
NULL yes (n/a)
(n/a) NULL
root
varchar
no 80
yes no
no SQL_Latin1_General_CP1_CI_AS
SortOrder
varchar
no 80
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
--- --
id
1
1 0
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
index_name
index_description
index_keys
------
---------
-----------
-
PK_road
clustered, unique,
primary key located on PRIMARY
id
constraint_type
constraint_name
delete_action
update_action status_enabled status_for_replication constraint_keys
-------
------
-- -- -- --
-----------
-
FOREIGN KEY
FK_road_parish
No Action No Action
Enabled Is_For_Replication parishid
REFERENCES APMS.dbo.parish (id)
PRIMARY KEY (clustered)
PK_road
(n/a) (n/a) (n/a)
(n/a) id
Table is referenced by foreign key
-----------
-
APMS.dbo.DeliveryPoint: FK_DeliveryPoint_road
APMS.dbo.parentRoad: FK_parentRoad_road
APMS.dbo.parentRoad: FK_parentRoad_road1
APMS.dbo.roadAlias: FK_roadAlias_road
No views with schema binding reference this table.
"John Kane" wrote:

> Warren,
> Could you post the full output of the following SQL script as it is helpful
> info in troubleshooting SQL FTS issues?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
> Reine Elizabeth The Second'? Could you post the exact SQL query that you
> were using?
> Thanks,
> John
>
> "WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
> news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> nor
>
>
|||Sorry John - here is the relevenat bit of the SQL Statement - @.searchStreet
is the parameter in question...
where (
contains (d.*, @.searchtext1)
or d.business like @.Business+'%'
or contains( b.*, @.searchtext1)
or b.name like @.Business+'%'
or contains( r1.*, @.searchtext1)
or r1.name like @.Business+'%'
or contains( r2.*, @.searchtext1)
or d.NameOfTerrace like @.Business+'%'
or d.postcode like @.Business+'%'
or d.locality like @.searchtext2+'%')
and contains(r2.* ,@.searchstreet) and contains(p.*,@.searchTown)
"John Kane" wrote:

> Warren,
> Could you post the full output of the following SQL script as it is helpful
> info in troubleshooting SQL FTS issues?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
> Reine Elizabeth The Second'? Could you post the exact SQL query that you
> were using?
> Thanks,
> John
>
> "WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
> news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> nor
>
>
|||Warren,
Thank you for the info! You're using SQL Server 2000 SP3 on Windows Server
2003 (Win2003) with US_english (1033) both the default language as well as
the "Language for Word Breaker" for your FT-enable varchar columns.
Since you're using Win2003 and it's new wordbreaker dll (LangWrbk.dll), the
string "L''Avenue De La Reine Elizabeth The Second" (without the double
quotes) breaks as follows:
Original text: 'L''Avenue De La Reine Elizabeth The Second'
IWordSink::PutWord: cwcSrcLen 9, cwcSrcPos 0, cwc 9, 'L''Avenue'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 10, cwc 2, 'De'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 13, cwc 2, 'La'
IWordSink::PutWord: cwcSrcLen 5, cwcSrcPos 16, cwc 5, 'Reine'
IWordSink::PutWord: cwcSrcLen 9, cwcSrcPos 22, cwc 9, 'Elizabeth'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 32, cwc 3, 'The'
IWordSink::PutWord: cwcSrcLen 6, cwcSrcPos 36, cwc 6, 'Second'
When you pass the above string into the @.searchstreet variable are you also
passing in double quotes (" ")? This would indicate to SQL Server 2000 that
this is a phrase query. Additionally, if you search only on "Avenue De La
Reine Elizabeth The Second" (with the double quotes) do you get a hit on
this? Note, there is no word limit of any sort on full text search queries,
but there are issues with punctuation characters (such as single quotes that
are escaped) that depend upon the OS-supplied wordbreaker dll.
Thanks,
John
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:C6D8715A-9C48-4CA5-87B2-3C551A77859D@.microsoft.com...
> Sorry John - here is the relevenat bit of the SQL Statement -
@.searchStreet[vbcol=seagreen]
> is the parameter in question...
> where (
> contains (d.*, @.searchtext1)
> or d.business like @.Business+'%'
> or contains( b.*, @.searchtext1)
> or b.name like @.Business+'%'
> or contains( r1.*, @.searchtext1)
> or r1.name like @.Business+'%'
> or contains( r2.*, @.searchtext1)
> or d.NameOfTerrace like @.Business+'%'
> or d.postcode like @.Business+'%'
> or d.locality like @.searchtext2+'%')
> and contains(r2.* ,@.searchstreet) and contains(p.*,@.searchTown)
> "John Kane" wrote:
helpful[vbcol=seagreen]
La[vbcol=seagreen]
The[vbcol=seagreen]
rows,[vbcol=seagreen]

Including Report Parameters in SQL Query

I have created a DAtaset for using in my report. Everything works. My SQL
contains Top 4 ... which I want to pass as a parameter.
I created a report parameter called "Months". How can I replace top 4 by top
(parameters!months.value) '
However I try to include the parameter in my SQL query it fails to work.I think in this case you need to use an expression for the source. Use the
generic query designer and do this:
= "some sql statements blah blah Top " & parameters!Months.value & " rest of
SQL Statement"
Remember that when refering to a parameter it is case sensitive so it much
match exactly.
When I do this I will sometimes first work out the expression using a report
with a single textbox and my parameters and set the textbox to the
expression so I can see it prior to using it for the source of the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>I have created a DAtaset for using in my report. Everything works. My SQL
> contains Top 4 ... which I want to pass as a parameter.
> I created a report parameter called "Months". How can I replace top 4 by
> top
> (parameters!months.value) '
> However I try to include the parameter in my SQL query it fails to work.|||Thanks fror your reply.
I have tried that. What I don't understand is where should I stop the SQL
String with " ? I have created the query in VS 2003 on the data tab with the
grafical query creator (looking like SQL Enterprise manager).
There is no SQL String inside quotation marks I could interrupt, enter the
parameter and continue the sql string with quotation marks. Do you get my
problem?
markus
"Bruce L-C [MVP]" wrote:
> I think in this case you need to use an expression for the source. Use the
> generic query designer and do this:
> = "some sql statements blah blah Top " & parameters!Months.value & " rest of
> SQL Statement"
> Remember that when refering to a parameter it is case sensitive so it much
> match exactly.
> When I do this I will sometimes first work out the expression using a report
> with a single textbox and my parameters and set the textbox to the
> expression so I can see it prior to using it for the source of the dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >I have created a DAtaset for using in my report. Everything works. My SQL
> > contains Top 4 ... which I want to pass as a parameter.
> >
> > I created a report parameter called "Months". How can I replace top 4 by
> > top
> > (parameters!months.value) '
> >
> > However I try to include the parameter in my SQL query it fails to work.
>
>|||The issue is that you are in the graphical query instead of the generic
query designer. There is a button (hover over them) to switch to the other
designer. Then put an = sign in from of it.
For instance, let's say that your query looks like this:
select * from sometable
Click on the button (to the right of the ...). Now do this:
= "select * from sometable"
It should work for you. Now modify it to include your parameter as I had it
below.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> Thanks fror your reply.
> I have tried that. What I don't understand is where should I stop the SQL
> String with " ? I have created the query in VS 2003 on the data tab with
> the
> grafical query creator (looking like SQL Enterprise manager).
> There is no SQL String inside quotation marks I could interrupt, enter the
> parameter and continue the sql string with quotation marks. Do you get my
> problem?
> markus
> "Bruce L-C [MVP]" wrote:
>> I think in this case you need to use an expression for the source. Use
>> the
>> generic query designer and do this:
>> = "some sql statements blah blah Top " & parameters!Months.value & " rest
>> of
>> SQL Statement"
>> Remember that when refering to a parameter it is case sensitive so it
>> much
>> match exactly.
>> When I do this I will sometimes first work out the expression using a
>> report
>> with a single textbox and my parameters and set the textbox to the
>> expression so I can see it prior to using it for the source of the
>> dataset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
>> message
>> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>> >I have created a DAtaset for using in my report. Everything works. My
>> >SQL
>> > contains Top 4 ... which I want to pass as a parameter.
>> >
>> > I created a report parameter called "Months". How can I replace top 4
>> > by
>> > top
>> > (parameters!months.value) '
>> >
>> > However I try to include the parameter in my SQL query it fails to
>> > work.
>>|||Ok, I found it. I changed my SQL to:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM dbo.v_Calls_Statistik_Sum
ORDER BY Jahr DESC, Monat DESC"
But after editing my SQL I get an error:
"Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler: [BC30648]
Zeichenfolgenliterale müssen mit einem doppelten Anführungszeichen enden."
~
"The expression for Object 'DATASETNAME' contains error BC30648. Literals
have to end with double quotation marks."
My Parameter is an integer. Why or where do I have to use quotation marks?
Thank you so much,
markus
"Bruce L-C [MVP]" wrote:
> The issue is that you are in the graphical query instead of the generic
> query designer. There is a button (hover over them) to switch to the other
> designer. Then put an = sign in from of it.
> For instance, let's say that your query looks like this:
> select * from sometable
> Click on the button (to the right of the ...). Now do this:
> = "select * from sometable"
> It should work for you. Now modify it to include your parameter as I had it
> below.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> > Thanks fror your reply.
> >
> > I have tried that. What I don't understand is where should I stop the SQL
> > String with " ? I have created the query in VS 2003 on the data tab with
> > the
> > grafical query creator (looking like SQL Enterprise manager).
> > There is no SQL String inside quotation marks I could interrupt, enter the
> > parameter and continue the sql string with quotation marks. Do you get my
> > problem?
> > markus
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I think in this case you need to use an expression for the source. Use
> >> the
> >> generic query designer and do this:
> >> = "some sql statements blah blah Top " & parameters!Months.value & " rest
> >> of
> >> SQL Statement"
> >>
> >> Remember that when refering to a parameter it is case sensitive so it
> >> much
> >> match exactly.
> >>
> >> When I do this I will sometimes first work out the expression using a
> >> report
> >> with a single textbox and my parameters and set the textbox to the
> >> expression so I can see it prior to using it for the source of the
> >> dataset.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
> >> message
> >> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >> >I have created a DAtaset for using in my report. Everything works. My
> >> >SQL
> >> > contains Top 4 ... which I want to pass as a parameter.
> >> >
> >> > I created a report parameter called "Months". How can I replace top 4
> >> > by
> >> > top
> >> > (parameters!months.value) '
> >> >
> >> > However I try to include the parameter in my SQL query it fails to
> >> > work.
> >>
> >>
> >>
>
>|||MarkusPoehler wrote:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> But after editing my SQL I get an error:
> "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> Anführungszeichen enden." ~
Markus,
schreib das mal so:
write it in that way:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
Du muss den ganzen String quoten
You have to quote the whole string with "
best regards
Frank
--
www.xax.de|||Frank Matthiesen wrote:
nochmal wg. Umbruch
= "SELECT TOP " +
" & parameters!anzMonate.Value & " +
" dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum " +
" ORDER BY Jahr DESC, Monat DESC"
gruss
frank|||Hi Frank,
Hi Fränk, :)
vielen Dank, das war ein entscheidender Hinweis. Darauf wäre ich nie
gekommen, dass dieser Text-SQL Editor plötzlich Zeilen unterscheidet.
Thank you! This was the crux.
Markus
"Frank Matthiesen" wrote:
> MarkusPoehler wrote:
> > = "SELECT TOP " & parameters!anzMonate.Value & "
> > dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> > dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> >
> > But after editing my SQL I get an error:
> >
> > "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> > [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> > Anführungszeichen enden." ~
>
> Markus,
> schreib das mal so:
> write it in that way:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, " +
> " Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> Du muss den ganzen String quoten
> You have to quote the whole string with "
> best regards
> Frank
> --
> www.xax.de
>
>
>

Including NULL Option in WHERE Clause

Hi,
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statement
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a field
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statement
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CASE
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the second
> returns TRUE.
> I would further like to be able to construct this solution or any other that
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
> I think you're looking for the ISNULL function. It would be used like this:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
> > Hi,
> >
> > I'm trying to create predicates that will work the same way whether a field
> > contains a blank or a null value. This is based on the fact that nulls get
> > converted to blanks when loaded into a VS control, then loaded into the
> > e.Values or e.OldValues arrays when deleting or updating the current row,
> > respectively, in a VS/C# FormView.
> >
> > At the point where the query is constructed, I cannot tell whether the
> > original value was a null or not, so I attempted to use a CASE WHEN statement
> > in my WHERE clause like the following:
> >
> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> > ELSE '' END) ...
> >
> > However, this doesn't work because the when [Address] is null, the CASE
> > statement returns a null which results in the predicate containing:
> >
> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >
> > and that is not the same as:
> >
> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >
> > the first returns FALSE event though [Address] is null, while the second
> > returns TRUE.
> >
> > I would further like to be able to construct this solution or any other that
> > works in a generic method that can be called by any query to construct its
> > predicate for each field where this situation is a possibility.
> >
> > Thanks|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>> I think you're looking for the ISNULL function. It would be used like
>> this:
>> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
>> "WJB" wrote:
>> > Hi,
>> >
>> > I'm trying to create predicates that will work the same way whether a
>> > field
>> > contains a blank or a null value. This is based on the fact that nulls
>> > get
>> > converted to blanks when loaded into a VS control, then loaded into the
>> > e.Values or e.OldValues arrays when deleting or updating the current
>> > row,
>> > respectively, in a VS/C# FormView.
>> >
>> > At the point where the query is constructed, I cannot tell whether the
>> > original value was a null or not, so I attempted to use a CASE WHEN
>> > statement
>> > in my WHERE clause like the following:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
>> > null
>> > ELSE '' END) ...
>> >
>> > However, this doesn't work because the when [Address] is null, the CASE
>> > statement returns a null which results in the predicate containing:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = null) ...
>> >
>> > and that is not the same as:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
>> >
>> > the first returns FALSE event though [Address] is null, while the
>> > second
>> > returns TRUE.
>> >
>> > I would further like to be able to construct this solution or any other
>> > that
>> > works in a generic method that can be called by any query to construct
>> > its
>> > predicate for each field where this situation is a possibility.
>> >
>> > Thanks|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts the
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the original
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> > Not exactly. I had tried that already. The problem is that the field
> > itself
> > actually could contain either null or blank. The ISNULL function as below
> > works if the field is blank but not if it is null. I need a solution that
> > works for both. Essentially, it needs to be a test that uses one value if
> > true and another if false. The problem is that "= null" in SQL Server is
> > not
> > the same as "is null". If the ISNULL function had an overload that would
> > return a value if false and would equate to "IS NULL" if true, that would
> > work.
> >
> > Can you suggest any other options?
> >
> > Thanks
> >
> > "Code Wench" wrote:
> >
> >> I think you're looking for the ISNULL function. It would be used like
> >> this:
> >>
> >> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> >>
> >> "WJB" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to create predicates that will work the same way whether a
> >> > field
> >> > contains a blank or a null value. This is based on the fact that nulls
> >> > get
> >> > converted to blanks when loaded into a VS control, then loaded into the
> >> > e.Values or e.OldValues arrays when deleting or updating the current
> >> > row,
> >> > respectively, in a VS/C# FormView.
> >> >
> >> > At the point where the query is constructed, I cannot tell whether the
> >> > original value was a null or not, so I attempted to use a CASE WHEN
> >> > statement
> >> > in my WHERE clause like the following:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
> >> > null
> >> > ELSE '' END) ...
> >> >
> >> > However, this doesn't work because the when [Address] is null, the CASE
> >> > statement returns a null which results in the predicate containing:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >> >
> >> > and that is not the same as:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >> >
> >> > the first returns FALSE event though [Address] is null, while the
> >> > second
> >> > returns TRUE.
> >> >
> >> > I would further like to be able to construct this solution or any other
> >> > that
> >> > works in a generic method that can be called by any query to construct
> >> > its
> >> > predicate for each field where this situation is a possibility.
> >> >
> >> > Thanks
>
>