Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

sql

Pease help - Feild value is truncated

I use a stored procedure to populate a table and it truncates the value to
one character for the field eMail.
you have an idea what is the problem?
i have to say also that I have tried this and it works:
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
So it looks like there is a problem at the @.eMailPlus variable level.
TABLE IS:
Id int
eMail varchar(MAX)
STORED PROCEDURE IS:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
DECLARE @.IdMaxPlus AS INT
DECLARE @.eMailPlus AS VARCHAR
BEGIN TRANSACTION
SET @.eMailPlus = 'Ring' VARCHAR
SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
(UPDLOCK)
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
COMMIT TRANSACTIONok fixed it
DECLARE @.eMailPlus AS VARCHAR(MAX)
"Progman" <adfawefqw@.hotmail.com> wrote in message
news:1fmKf.2089$Jb7.681075@.weber.videotron.net...
>I use a stored procedure to populate a table and it truncates the value to
>one character for the field eMail.
> you have an idea what is the problem?
> i have to say also that I have tried this and it works:
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
> So it looks like there is a problem at the @.eMailPlus variable level.
> TABLE IS:
> Id int
> eMail varchar(MAX)
> STORED PROCEDURE IS:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
> DECLARE @.IdMaxPlus AS INT
> DECLARE @.eMailPlus AS VARCHAR
> BEGIN TRANSACTION
> SET @.eMailPlus = 'Ring' VARCHAR
> SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
> (UPDLOCK)
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
> COMMIT TRANSACTION
>

PDF export and invisible columns

I have a report with some columns that are visible\invisible based on a
parameter value. The report renders fine in HTML and when exported to Excel.
But when its exported to PDF the report shows blank white space for some of
the columns that are invisible, e.g. the report is 3 pages wide when the
collumns are all visible, it is 1 page wide when some are invisible, but if
you export the 1 page wide report to PDF it still comes out as 3 pages wide,
with the last 2 pages just alll blank space.
ANything I can do?Did you find any solutions? I had teh same problem.
I am setting the visibility for the Rows through the expression. They are
not showing in HTML mode. However, If I export to PDF it shows some white
spaces.
"NH" wrote:
> I have a report with some columns that are visible\invisible based on a
> parameter value. The report renders fine in HTML and when exported to Excel.
> But when its exported to PDF the report shows blank white space for some of
> the columns that are invisible, e.g. the report is 3 pages wide when the
> collumns are all visible, it is 1 page wide when some are invisible, but if
> you export the 1 page wide report to PDF it still comes out as 3 pages wide,
> with the last 2 pages just alll blank space.
> ANything I can do?|||Hi,
No, never found a solution. I am afraid that you dont get many replies to
your queries in this forum.
"GC" wrote:
> Did you find any solutions? I had teh same problem.
> I am setting the visibility for the Rows through the expression. They are
> not showing in HTML mode. However, If I export to PDF it shows some white
> spaces.
>
> "NH" wrote:
> > I have a report with some columns that are visible\invisible based on a
> > parameter value. The report renders fine in HTML and when exported to Excel.
> > But when its exported to PDF the report shows blank white space for some of
> > the columns that are invisible, e.g. the report is 3 pages wide when the
> > collumns are all visible, it is 1 page wide when some are invisible, but if
> > you export the 1 page wide report to PDF it still comes out as 3 pages wide,
> > with the last 2 pages just alll blank space.
> >
> > ANything I can do?|||I have experienced something like this before...the problem is that in
order to shrink up the hidden columns then you will need to set the
visibility property on each column, not just the tablerow. Very
tedious but I believe that this may be what your report needs...
Matt A|||i have a similar problem. html & xls work fine, but when you export to
csv, the columns are hidden no matter what you do...
reportdude wrote:
> I have experienced something like this before...the problem is that in
> order to shrink up the hidden columns then you will need to set the
> visibility property on each column, not just the tablerow. Very
> tedious but I believe that this may be what your report needs...
> Matt A|||Thanks for the Update. I have tried the option of applying the Expression for
the Visibiliy of every cell in that Row. Still it doesn't make any difference.
This is creating space at the end of the Report. ( I have few Sub Reports).
If I set the hidden false then the white spaces are disappearing. However
this works well in HTML Viewer.
"reportdude" wrote:
> I have experienced something like this before...the problem is that in
> order to shrink up the hidden columns then you will need to set the
> visibility property on each column, not just the tablerow. Very
> tedious but I believe that this may be what your report needs...
> Matt A
>

Pb to retreive max value with aggregate subquery

I would like to know which products are my best sells by sellers, but i
would like to retreive this info by product id, seller id and the total
amount of sells for this product.

My Sells table is :
Seller_idProduct_idTotaldate_s
1 2 1020/05/04
2 4 1512/05/04
3 5 2206/06/04
1 5 1807/06/04
4 8 1213/05/04
7 2 1119/05/04
3 4 1421/05/04
2 4 1418/05/04
1 5 1817/06/04
2 5 5008/05/04

etc...

I know how to retreive the total sells by product id and seller id

SELECT Seller_id, Product_id, SUM(Total) AS total
FROM Sells
WHERE date_s > '01/05/04'
GROUP BY Seller_id,Product_id order by Seller_id

Seller_idProduct_idTotal
1 5 36
1 2 10
2 5 50
2 4 29
3 5 22
3 4 14

I would like retreive only the max of total, and the Seller id and
product id, like this :

Seller_idProduct_idTotal
1 5 36
2 5 50
3 5 22

How can i do without using a temp table ?

Thanks for your help.Try this:

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
AND product_id = S.product_id) AS T) ;

--
David Portas
SQL Server MVP
--|||David Portas wrote:

> Try this:
> SELECT seller_id, product_id, SUM(total) AS total
> FROM Sells AS S
> WHERE date_s > '20040501'
> GROUP BY seller_id, product_id
> HAVING SUM(total) =
> (SELECT MAX(total)
> FROM
> (SELECT SUM(total) AS total
> FROM Sells
> WHERE date_s > '20040501'
> AND seller_id = S.seller_id
> AND product_id = S.product_id) AS T) ;

Thank but i think i need to add a GROUP by in the last select:

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
AND product_id = S.product_id GROUP BY seller_id, product_id )
AS T) ;

Thanks a lot|||Almost. I think you wanted the max for each Seller_ID so you just need
Seller_ID in that WHERE clause and Product_ID in the GROUP BY - which is
what I should have posted to start with.

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
GROUP BY product_id ) AS T) ;

--
David Portas
SQL Server MVP
--

Pattern extraction table value function

I need a table value function for extracting strings matching a particular pattern from a long string.

e.g. I have a table called cs_Posts, it has a column called FormattedBody, this value can be something like:

Code Snippet

this is the 1st photo <a href="http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPGhttp://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG">http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" /></a>

this is the 2nd photo<a href="http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPGhttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG">http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" /></a>

When this long string is input, the table value function should 2 rows:
http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG
http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG

Any idea?

This is just a sample,

Code Snippet

Create table Utility_Numbers (Number int);

Declare @.I as Int;

Set @.I = 1

While @.I<=8000

Begin

Insert Into Utility_Numbers Values(@.I);

Set @.I = @.I + 1;

End

Go

Code Snippet

Create Function GetUrls

(

@.html as varchar(8000)

) returns @.result Table(URL varchar(1000))

as

Begin

Set @.html = char(10) + @.html + char(10)

Declare @.Table Table (data varchar(1000))

Insert Into @.Table

Select Substring(@.html,number,charindex(char(10),@.html,number+1) - number) from Utility_Numbers where number<len(@.html)

and substring(@.html,number,1)=char(10)

Insert Into @.result

Select Substring(data,charindex('>http://',data)+1,charindex('</'< SPAN>,data,charindex('>http://',data)+1)-charindex('>http://',data)-1) from @.Table

return;

End

Go

Code Snippet

Select * from GetUrls('

this is the 1st photo http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" />

this is the 2nd photohttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" />

')

|||

Thanks Manivannan. But I got my answer now

Code Snippet

CREATE FUNCTION ExtractHTMLImgs
(
@.html nvarchar(max)
)
RETURNS
@.result TABLE
(
imgLink nvarchar(4000),
ordinal int
)
WITH SCHEMABINDING
AS
BEGIN
-- image link prefix & suffix
DECLARE @.imgPrefix char(10)
SET @.imgPrefix = '<img src="'
DECLARE @.imgSuffix char(2)
SET @.imgSuffix = '" '

-- image link ordinal
DECLARE @.ordinal int
SET @.ordinal = 1

-- searching positions
DECLARE @.imgStartPos int
SET @.imgStartPos = 1
DECLARE @.imgEndPos int
SET @.imgEndPos = 1

-- extract image links
WHILE @.imgEndPos < LEN(@.html)
BEGIN
-- search the image-link-prefix, starting from the last found image-end
SET @.imgStartPos = CHARINDEX(@.imgPrefix, @.html, @.imgEndPos)

IF @.imgStartPos = 0
BEGIN
-- NO more image link => STOP
BREAK
END
ELSE
BEGIN
-- image found => get the image-link-start-position
SET @.imgStartPos = @.imgStartPos + LEN(@.imgPrefix)

-- search the image-link-suffix, starting from the current image-start
SET @.imgEndPos = CHARINDEX(@.imgSuffix, @.html, @.imgStartPos)

-- populate results
INSERT @.result VALUES (
SUBSTRING(@.html, @.imgStartPos, (@.imgEndPos - @.imgStartPos)),
@.ordinal
)

-- next
SET @.ordinal = @.ordinal + 1
END
END

RETURN
END

PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHING
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. Fraz
Fraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz
|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by getting
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>
|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct. I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHI
NG
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. FrazFraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by gettin
g
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars
+
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.
%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct.
I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

patindex that I can use in RS?

Basically, I am trying this in a table cell expression:
=iif(patindex('%-%', Fields!courseName.Value) <>
0,left(Fields!courseName.Value, patindex('%-%',
Fields!courseName.Value)-1),Fields!courseName.Value)
It errors because it says patindex is not declared. I tried doing this part
in my query in a stored proc instead but was unable to get the results I
needed, so thought I would try in RS instead.
Is there any way for me to do this in RS? Basically in my stored proc I was
trying to do:
Query where patindex <> 0
Union
Query where patindex = 0
The field I am trying to fix is basically: â'Name â' explanationâ' and all I
want is the name before the â?...â'. But there are some fields with just â'Nameâ'
and no dash or explanation. With my union statement, instead of getting:
Name1
Name2
Name3
I get
Name1
Name1 â' Explanation
Name2
Name2 - Explanation
Name3
Name3 â' Explanation
The query I am using now gives me the results I want (correct records) and I
am trying to split out the name in RS.
Thank you for your time and help!WHen you are in an expression you are talking VB.NET...
Instr is the function you are looking for...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"SharinDenver" wrote:
> Basically, I am trying this in a table cell expression:
> =iif(patindex('%-%', Fields!courseName.Value) <>
> 0,left(Fields!courseName.Value, patindex('%-%',
> Fields!courseName.Value)-1),Fields!courseName.Value)
> It errors because it says patindex is not declared. I tried doing this part
> in my query in a stored proc instead but was unable to get the results I
> needed, so thought I would try in RS instead.
> Is there any way for me to do this in RS? Basically in my stored proc I was
> trying to do:
> Query where patindex <> 0
> Union
> Query where patindex = 0
> The field I am trying to fix is basically: â'Name â' explanationâ' and all I
> want is the name before the â?...â'. But there are some fields with just â'Nameâ'
> and no dash or explanation. With my union statement, instead of getting:
> Name1
> Name2
> Name3
> I get
> Name1
> Name1 â' Explanation
> Name2
> Name2 - Explanation
> Name3
> Name3 â' Explanation
> The query I am using now gives me the results I want (correct records) and I
> am trying to split out the name in RS.
> Thank you for your time and help!
>

Path to feedback?

My team keeps emphasizing the importance of community participation and value all the feedback posted in the groups, forums and all possible channels to communicate with you all, the customers; and loop the feedback provided to improve the quality of product. I personally felt the importance after being part of the community during our SP1 release cycle, it really helped to address most of the customer impact defects in our Service Pack1; however, not all could be addressed in the first service pack and we would like to give importance to them and start gathering the impact of those defects. We will evaluate and try to address most of the commonly reported customer bugs in our next Service pack, SP2.

This can be done effectively by your active participation in providing feedback. Though there are several ways to do it, the best way to track and evaluate the impact on unaddressed issues across customers, is through reporting them through MSDN product feedback center which is incorporated in SQL Server Management Studio. You can access MSDN Product Feedback Center from Management Studio by selecting “Send Feedback” menu item under “Community” Menu or by going directly to the link

http://lab.msdn.microsoft.com/productfeedback/

By using this channel, you could log bugs directly or browse over the existing bugs that correspond to the issue you are running into and vote the importance of the fix for that bug. Our team constantly watches the incoming bugs and goes over the importance of the bug to be addressed based on number of voters and the rating of how important the fix is needed.

We are starting to plan and work on our next service pack and your input is the key factor for success of SP2!

Thanks,

Gops Dwarak, SQL Server Tools Team, MSFT

I think service pack 2 is cool.The security enhancements are important, and necessary. I had no issues with it.If your looking for tech support in CT or windows help CT there are places to go, but for service pack 2, see Microsoft.|||

Using DMO to look up the distribution publisher, the publishing db and the publication name appear to be working. This is accomplished with the TransPublication dmo object.

We receive some information from the TransSubscription object, but not the DistributionJobID.

We get the correct subscriber count, article count, publiser name, subscriber server and subscriber databasename.

The DistributionJobID is all zeros, (00000000000000000000000000000000).

SQL2005 9.00.2047 with or without the backward compatible objects installed.

The objects and code has been working with SQL2000 for two years.
It is failing during our certification of SQL2005 for our distributed application.

We cannot proceed with certification and recommending SQL2005 to our 300+ customers without resolution.

|||

Hello Clifton,

I have confirmed that this is indeed a bug in our SQL2005 code base, and as far as I can tell, the bug has been there since 2002 so I am a bit confused by your comment that SQL2005 DMO has been working fine for you for two years. It would be great if you can clarify whether you really meant SQL2000 instead of SQL2005 in that comment. At the moment, we are going through a debate internally as to which delivery vehicle (QFE or SP2) we should use for the fix and I will post again to inform you of our decision. If you want to track the progress of this issue more closely, I would encourage you to log it @. https://login.live.com/ppsecure/secure.srf?lc=1033&id=64416&ru=https://connect.microsoft.com/default.aspx&tw=3600&fs=1&kv=4&ct=1150303879&ems=1&seclog=10&ver=2.1.6000.1&rn=CYWDyHyE&tpf=504beca26541936cfbb9d9e716bf9902

Or, you can let me if you want me to log an internal bug on your behalf.

Thank you for reporting the issue and sorry for the inconvenience.

-Raymond

|||

Hello Clifton,

We have decided to deliver the fix in SQL2005 SP2, if you need the fix sooner, I would encourage you to go through the product support channels to get a QFE request in.

-Raymond

|||

Thank you for the update. My notes are incorrect in one place. We are currently on SQL 2000 in production distribution of our product. The code has been working on SQL2000 for two years.

I would like to have a hotfix /qfe if possible.

|||

I am logged in to connect. I get to the feedback page, https://connect.microsoft.com/SQLServer/Feedback, but do not see a method of submitting feedback. All I have are search options. Do I need an invitation ID in order to connect/participate in the SQL server program?

|||

Hi Clifton,

The Connect site is rather new to me too so I needed to fumble a bit to navigate to the feedback form. The following is roughly what I did after the registration page:

1) Click on the "Available Connections" link on the left pane

2) Click on the "SQL Server" link

3) Click on the "Feedback" link on the left pane

4) Click on the "Submit Feedback" link

5) The next page will force you to search the existing bug database, so just type in a few keywords and hit the Search button

6) Since there will probably nott be existing issue corresponding to yours, you can hit the "Submit Feedback" button

7) Now you have the of using either the Bug form or the Suggestion form

I know this is a bit of a hassle, but your QFE request will more likely be accepted if you go through the Microsoft product support organization (call them up) rather than logging it at the Connect site which pipes directly to the product development folks like me.

Hope that helps,

-Raymond

|||

What is the target SQL 2005 SP2 release dates?

|||

Hey

The feedback site is a bit confusing.

I can't seem to find the "Submit Feedback" link.

Any ideas?

Thanks

|||

What are the target release date(s) for MS SQL 2005 Service Pack 2?

Path to feedback?

My team keeps emphasizing the importance of community participation and value all the feedback posted in the groups, forums and all possible channels to communicate with you all, the customers; and loop the feedback provided to improve the quality of product. I personally felt the importance after being part of the community during our SP1 release cycle, it really helped to address most of the customer impact defects in our Service Pack1; however, not all could be addressed in the first service pack and we would like to give importance to them and start gathering the impact of those defects. We will evaluate and try to address most of the commonly reported customer bugs in our next Service pack, SP2.

This can be done effectively by your active participation in providing feedback. Though there are several ways to do it, the best way to track and evaluate the impact on unaddressed issues across customers, is through reporting them through MSDN product feedback center which is incorporated in SQL Server Management Studio. You can access MSDN Product Feedback Center from Management Studio by selecting “Send Feedback” menu item under “Community” Menu or by going directly to the link

http://lab.msdn.microsoft.com/productfeedback/

By using this channel, you could log bugs directly or browse over the existing bugs that correspond to the issue you are running into and vote the importance of the fix for that bug. Our team constantly watches the incoming bugs and goes over the importance of the bug to be addressed based on number of voters and the rating of how important the fix is needed.

We are starting to plan and work on our next service pack and your input is the key factor for success of SP2!

Thanks,

Gops Dwarak, SQL Server Tools Team, MSFT

I think service pack 2 is cool.The security enhancements are important, and necessary. I had no issues with it.If your looking for tech support in CT or windows help CT there are places to go, but for service pack 2, see Microsoft.|||

Using DMO to look up the distribution publisher, the publishing db and the publication name appear to be working. This is accomplished with the TransPublication dmo object.

We receive some information from the TransSubscription object, but not the DistributionJobID.

We get the correct subscriber count, article count, publiser name, subscriber server and subscriber databasename.

The DistributionJobID is all zeros, (00000000000000000000000000000000).

SQL2005 9.00.2047 with or without the backward compatible objects installed.

The objects and code has been working with SQL2000 for two years.
It is failing during our certification of SQL2005 for our distributed application.

We cannot proceed with certification and recommending SQL2005 to our 300+ customers without resolution.

|||

Hello Clifton,

I have confirmed that this is indeed a bug in our SQL2005 code base, and as far as I can tell, the bug has been there since 2002 so I am a bit confused by your comment that SQL2005 DMO has been working fine for you for two years. It would be great if you can clarify whether you really meant SQL2000 instead of SQL2005 in that comment. At the moment, we are going through a debate internally as to which delivery vehicle (QFE or SP2) we should use for the fix and I will post again to inform you of our decision. If you want to track the progress of this issue more closely, I would encourage you to log it @. https://login.live.com/ppsecure/secure.srf?lc=1033&id=64416&ru=https://connect.microsoft.com/default.aspx&tw=3600&fs=1&kv=4&ct=1150303879&ems=1&seclog=10&ver=2.1.6000.1&rn=CYWDyHyE&tpf=504beca26541936cfbb9d9e716bf9902

Or, you can let me if you want me to log an internal bug on your behalf.

Thank you for reporting the issue and sorry for the inconvenience.

-Raymond

|||

Hello Clifton,

We have decided to deliver the fix in SQL2005 SP2, if you need the fix sooner, I would encourage you to go through the product support channels to get a QFE request in.

-Raymond

|||

Thank you for the update. My notes are incorrect in one place. We are currently on SQL 2000 in production distribution of our product. The code has been working on SQL2000 for two years.

I would like to have a hotfix /qfe if possible.

|||

I am logged in to connect. I get to the feedback page, https://connect.microsoft.com/SQLServer/Feedback, but do not see a method of submitting feedback. All I have are search options. Do I need an invitation ID in order to connect/participate in the SQL server program?

|||

Hi Clifton,

The Connect site is rather new to me too so I needed to fumble a bit to navigate to the feedback form. The following is roughly what I did after the registration page:

1) Click on the "Available Connections" link on the left pane

2) Click on the "SQL Server" link

3) Click on the "Feedback" link on the left pane

4) Click on the "Submit Feedback" link

5) The next page will force you to search the existing bug database, so just type in a few keywords and hit the Search button

6) Since there will probably nott be existing issue corresponding to yours, you can hit the "Submit Feedback" button

7) Now you have the of using either the Bug form or the Suggestion form

I know this is a bit of a hassle, but your QFE request will more likely be accepted if you go through the Microsoft product support organization (call them up) rather than logging it at the Connect site which pipes directly to the product development folks like me.

Hope that helps,

-Raymond

|||

What is the target SQL 2005 SP2 release dates?

|||

Hey

The feedback site is a bit confusing.

I can't seem to find the "Submit Feedback" link.

Any ideas?

Thanks

|||

What are the target release date(s) for MS SQL 2005 Service Pack 2?

patch null value?

following is my sql:

select a.dcode, b.district from table1 a, table2 b
where
a.id = b.id

and return following result:

dcode district
123 south
321 north
456 east
789 west
123
789

so for those records that district are null, how can i fill them up as i know they have the same dcode as the other records?

use Isnull or coalesce

Code Snippet

select a.dcode, isnull(b.district,-1) from table1 a, table2 b

where

a.id = b.id

--or

select a.dcode, COALESCE(b.district,-1) from table1 a, table2 b

where

a.id = b.id

|||

This could possibly point towards a de-normalized database design.

It might be better if you created a separate lookup table to store a distinct list of 'dcode' values against the appropriate 'district' value (and possibly even split distinct 'district' values out to a second lookup table) - you could then store 'dcode' in both table1 and table2 and remove the 'district' column from table2. Depending on your database design there may even be scope to store 'dcode' in either table1 or table2 (and not both).

Granted, you might have to re-work substantial amounts of code but it would help you in situations such as the one you're currently facing and would, more importantly, help to maintain the integrity of your data.

Chris

|||

Joe,

Below is one way that you could accomplish this.

declare @.temptable table
(dcode int,
district varchar(20))

insert into @.temptable values (123,'south')
insert into @.temptable values (321,'north')
insert into @.temptable values (456,'east')
insert into @.temptable values (789,'west')
insert into @.temptable values (123,NULL)
insert into @.temptable values (789,NULL)

select a.dcode,COALESCE(a.district,b.district,'')
from @.temptable a
left join (select dcode,district
from @.temptable
where district is not null) b
on a.dcode = b.dcode

Let me know if you have any other questions.

crusso.

|||

I completely agree with Chris that you have an improperly desiged database.

First thing: You need to create a District table that has a district code/Id and description, and use this code in both of your tables. This fixes the inconsistency of codes.

Second: Why are you recording district in both tables? (your names give no clue) Does this district mean the same thing? Or could the be different for some logical reason? If they have the same meaning, then delete one and join to get the other one if you need it in a query. This will make sure that you don't get out of sync values.

|||

i've figured myself how to solve this problem:

select a.dcode, b.district into #temp from table1 a, table2 b

where

a.id = b.id

I

I

I

V

update #temp
set district = b.district
from #temp a, (select * from #temp) b
where
a.dcode = b.dcode
and
a.district is null

patch null value?

following is my sql:

select a.dcode, b.district from table1 a, table2 b
where
a.id = b.id

and return following result:

dcode district
123 south
321 north
456 east
789 west
123
789

so for those records that district are null, how can i fill them up as i know they have the same dcode as the other records?

use Isnull or coalesce

Code Snippet

select a.dcode, isnull(b.district,-1) from table1 a, table2 b

where

a.id = b.id

--or

select a.dcode, COALESCE(b.district,-1) from table1 a, table2 b

where

a.id = b.id

|||

This could possibly point towards a de-normalized database design.

It might be better if you created a separate lookup table to store a distinct list of 'dcode' values against the appropriate 'district' value (and possibly even split distinct 'district' values out to a second lookup table) - you could then store 'dcode' in both table1 and table2 and remove the 'district' column from table2. Depending on your database design there may even be scope to store 'dcode' in either table1 or table2 (and not both).

Granted, you might have to re-work substantial amounts of code but it would help you in situations such as the one you're currently facing and would, more importantly, help to maintain the integrity of your data.

Chris

|||

Joe,

Below is one way that you could accomplish this.

declare @.temptable table
(dcode int,
district varchar(20))

insert into @.temptable values (123,'south')
insert into @.temptable values (321,'north')
insert into @.temptable values (456,'east')
insert into @.temptable values (789,'west')
insert into @.temptable values (123,NULL)
insert into @.temptable values (789,NULL)

select a.dcode,COALESCE(a.district,b.district,'')
from @.temptable a
left join (select dcode,district
from @.temptable
where district is not null) b
on a.dcode = b.dcode

Let me know if you have any other questions.

crusso.

|||

I completely agree with Chris that you have an improperly desiged database.

First thing: You need to create a District table that has a district code/Id and description, and use this code in both of your tables. This fixes the inconsistency of codes.

Second: Why are you recording district in both tables? (your names give no clue) Does this district mean the same thing? Or could the be different for some logical reason? If they have the same meaning, then delete one and join to get the other one if you need it in a query. This will make sure that you don't get out of sync values.

|||

i've figured myself how to solve this problem:

select a.dcode, b.district into #temp from table1 a, table2 b

where

a.id = b.id

I

I

I

V

update #temp
set district = b.district
from #temp a, (select * from #temp) b
where
a.dcode = b.dcode
and
a.district is null

patch null value?

following is my sql:

select a.dcode, b.district from table1 a, table2 b
where
a.id = b.id

and return following result:

dcode district
123 south
321 north
456 east
789 west
123
789

so for those records that district are null, how can i fill them up as i know they have the same dcode as the other records?

use Isnull or coalesce

Code Snippet

select a.dcode, isnull(b.district,-1) from table1 a, table2 b

where

a.id = b.id

--or

select a.dcode, COALESCE(b.district,-1) from table1 a, table2 b

where

a.id = b.id

|||

This could possibly point towards a de-normalized database design.

It might be better if you created a separate lookup table to store a distinct list of 'dcode' values against the appropriate 'district' value (and possibly even split distinct 'district' values out to a second lookup table) - you could then store 'dcode' in both table1 and table2 and remove the 'district' column from table2. Depending on your database design there may even be scope to store 'dcode' in either table1 or table2 (and not both).

Granted, you might have to re-work substantial amounts of code but it would help you in situations such as the one you're currently facing and would, more importantly, help to maintain the integrity of your data.

Chris

|||

Joe,

Below is one way that you could accomplish this.

declare @.temptable table
(dcode int,
district varchar(20))

insert into @.temptable values (123,'south')
insert into @.temptable values (321,'north')
insert into @.temptable values (456,'east')
insert into @.temptable values (789,'west')
insert into @.temptable values (123,NULL)
insert into @.temptable values (789,NULL)

select a.dcode,COALESCE(a.district,b.district,'')
from @.temptable a
left join (select dcode,district
from @.temptable
where district is not null) b
on a.dcode = b.dcode

Let me know if you have any other questions.

crusso.

|||

I completely agree with Chris that you have an improperly desiged database.

First thing: You need to create a District table that has a district code/Id and description, and use this code in both of your tables. This fixes the inconsistency of codes.

Second: Why are you recording district in both tables? (your names give no clue) Does this district mean the same thing? Or could the be different for some logical reason? If they have the same meaning, then delete one and join to get the other one if you need it in a query. This will make sure that you don't get out of sync values.

|||

i've figured myself how to solve this problem:

select a.dcode, b.district into #temp from table1 a, table2 b

where

a.id = b.id

I

I

I

V

update #temp
set district = b.district
from #temp a, (select * from #temp) b
where
a.dcode = b.dcode
and
a.district is null

passing the value of the Name property from a textbox to a parameter ?

Hi All,
I'd like to pass the name or label of a textbox to a parameter but I
am not sure of how to get it done.
I have a table (the PK is on Language and Field)
Language(PK)
Field (PK)
FieldName
and a data set query:
"SELECT * from table
WHERE Language = @.Language AND Field = @.Field"
The parameter @.LanguageCode is based on user input and I want the
parameter @.Field to be based on the Name of a textbox (or eq. object
in the report)
(@.Field=Fields!Field.Name, "table")
The Value of the textbox should be set to the appropriate fieldname
based on the users choice of language and the identifier of the
textbox...
...something like:
=IIF(Parameters!Language.Value = 1, Fields!FieldName.Value, "table",
IIF(Parameters!Language.Value = 2, Fields!FieldName.Value, "table",
'Not Translated!'))
Anyone got any ideas?
Thanks a million in advance!
Regards NickWell...
I dynamically transposed the data in the table in order to use the
First() function in RS, problem solved.
Still I wonder how do you use labels and such from the report to
filter data in the data set?
/n|||You'd have to use parameters or fields from a dataset. Report items cannot
be used in filter expressions.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Niklas W" <niklas.westerholm@.intellibis.se> wrote in message
news:655bff8b.0408130442.754cd47f@.posting.google.com...
> Well...
> I dynamically transposed the data in the table in order to use the
> First() function in RS, problem solved.
> Still I wonder how do you use labels and such from the report to
> filter data in the data set?
> /n

passing table name as parameter and get count(*) value

Hi

I want to get a count(*) value (thro a procedure) for a table whose name will be passed as parameter

CREATE PROCEDURE PCount ( @.TName char(50), @.SCount int OUTPUT )
AS
Select @.SCount = Count(*) FROM @.TName

DECLARE @.TCnt int
EXEC PCount @.TName = 'tbl_users', @.TCnt = @.SCount OUTPUT

How to acheive this

Thanks-- the procedure if exists and then create it
if exists(select name from sysobjects where name = 'PCount' and type = 'P')
drop PROc PCount
GO
CREATE PROCEDURE PCount
(
@.TName varchar(50), @.SCount int OUTPUT
)
AS
SET NOCOUNT ON
EXEC ('select COUNT(*) as [count] into Tmp FROM '+@.TName+'')
select @.SCount = [count] from Tmp
drop table Tmp
SET NOCOUNT OFF

GO
-- SQL to test that SP
DECLARE @.TCnt int
EXEC PCount @.TName = 'sysobjects', @.SCount = @.TCnt OUTPUT
PRINT @.TCnt|||CREATE PROCEDURE PCount ( @.TName char(50))
AS
DECLARE @.NSQL NVARCHAR(1000)
DECLARE @.SCount INT
SET @.NSQL = ''
SET @.NSQL = @.NSQL + N'SELECT COUNT(*) FROM ' + @.TName
EXEC SP_EXECUTESQL @.NSQL, N'@.SCount INT OUTPUT', @.SCount OUTPUT

EXEC PCount @.TName = 'tbl_users'