losing some results

i have created a report that fits the layout to achieve the fields that i
require, i then created an aspx page where my users can select any number of
fields and values to use in the where clause of the sql statement. My aspx
page then builds an sql statement based on these selections and passes this
sql statement to the report as a parameter. The report calls a stored
procedure that executes the sql statement passed in. This works great for
all but one situation that i have found. When a user enters '%bel%' to use
in the where clause for some reason when it gets to reporting services
report the sql statement is modified to 'l%'. Dropping the '%be'. Is
'%be' a reserved command.
example:
if my table had the following entries in a column name city Boston,
Belville,New York,Detroit,Los Angeles, Lakeville
my user wants to find all cities that have 'bel' in the name
the resulting sql would be select city from table where city like '%bel%'
i setup up my report to show the parameters when the aspx page redirects to
the report using the url of the report
the sql that shows up in the parameter field is select city from table where
city like 'l%'
Any help would be appreciated.
Thank youSolved my own problem. what i had to do was replace all my '%' to '%25' to
encode my url before i issued a response.redirect.
"Mike" <mike.no.spam.please@.no.spam.com> wrote in message
news:u2LsSw6tEHA.1596@.TK2MSFTNGP10.phx.gbl...
>i have created a report that fits the layout to achieve the fields that i
>require, i then created an aspx page where my users can select any number
>of fields and values to use in the where clause of the sql statement. My
>aspx page then builds an sql statement based on these selections and passes
>this sql statement to the report as a parameter. The report calls a stored
>procedure that executes the sql statement passed in. This works great for
>all but one situation that i have found. When a user enters '%bel%' to use
>in the where clause for some reason when it gets to reporting services
>report the sql statement is modified to 'l%'. Dropping the '%be'. Is
>'%be' a reserved command.
> example:
> if my table had the following entries in a column name city Boston,
> Belville,New York,Detroit,Los Angeles, Lakeville
> my user wants to find all cities that have 'bel' in the name
> the resulting sql would be select city from table where city like '%bel%'
>
> i setup up my report to show the parameters when the aspx page redirects
> to the report using the url of the report
> the sql that shows up in the parameter field is select city from table
> where city like 'l%'
> Any help would be appreciated.
> Thank you
>

Looping rows to Increment a value

I currently have a table that contains a Serial Number and a quantity.

SN Qty
4352301 3
6892103 2

I need to be able to loop through this table and increment each unique Serial Number by the quantity. The desired result set is:

4352301
4352302
4302303
6892103
6892104

I can pull off a Do While loop in ASP however I am having trouble with the TSQL syntax. Any help would be GREAT !!

joeUPDATE table SET serial = serial + quantity ?|||Something like this?

Code:
-------------------------------------
create table #tmp(sn int, qty int)
insert into #tmp values(4352301,3)
insert into #tmp values(6892103,2)
select * From #tmp
declare @.sn int, @.qty int, @.cntr int
select @.sn = min(sn) from #tmp
while (@.sn is not null) begin
select @.qty = qty, @.cntr = 1 from #tmp where sn = @.sn
while (@.cntr < @.qty) begin
select @.sn = @.sn + 1, @.cntr = @.cntr + 1
insert into #tmp values(@.sn,@.qty)
end
select @.sn = min(sn) from #tmp where sn > @.sn
end
select * from #tmp order by sn
-------------------------------------

Results:
-------------------------------------
sn qty
---- ----
4352301 3
6892103 2

sn qty
---- ----
4352301 3
4352302 3
4352303 3
6892103 2
6892104 2
-------------------------------------

loop through tables for UNION ALL

Hey folks,
I have a variable number of identically structured & similarly named
tables for which I would like to expeditiously execute a UNION ALL in
succession. e.g. "SELECT * from (x-2) UNION ALL SELECT * FROM (x-1) UNION
ALL SELECT * FROM x UNION ALL" etc... where (x-2), (x-1), x are three such
tables. I can pull all the tables I need to create this very large UNION ALL
query from INFORMATION_SCHEMA.TABLES, so is there any way to somehow
construct a looped stored procedure such that I don't have to manually type
in the full UNION ALL query?
Thanks,
Dave
--
______________________________
Remove "_SPAM" to reply directly.David Shorthouse wrote:
> Hey folks,
> I have a variable number of identically structured & similarly named
> tables
Why?

> for which I would like to expeditiously execute a UNION ALL in
> succession. e.g. "SELECT * from (x-2) UNION ALL SELECT * FROM (x-1) UNION
> ALL SELECT * FROM x UNION ALL" etc... where (x-2), (x-1), x are three such
> tables. I can pull all the tables I need to create this very large UNION A
LL
> query from INFORMATION_SCHEMA.TABLES, so is there any way to somehow
> construct a looped stored procedure such that I don't have to manually typ
e
> in the full UNION ALL query?
>
Create a UNION by querying the INFORMATION_SCHEMA and then cut and
paste the result. Put the UNION in a view and query that in your proc.
Take a look at the partitioned views topic in Books Online.
Better still, consider combining this into a single table unless it's a
case of "I'm not allowed to fix my database".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I'll take it as read that performance is not a problem here as if it were
the driving factor a DB redesign would not be out of the question.
Insead of trying to perform a union all, maybe a different appear would be
better...
1. Create a temporary table with the structure of your output.
2. create a cursor which will loop through the tablenames that you need to
query.
3. use the table name to create a dynamic sql statement (see sp_executesql)
that will populate the temporary table with the data that you need to
select.
4. after the loop, perform a single select on the temp table.
Be warned, the performance of this will be extremely bad.
Regards
Colin Dawson
www.cjdawson.com
"David Shorthouse" <davidshorthouse@.shaw_SPAM.ca> wrote in message
news:uX5XwbqPGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have a variable number of identically structured & similarly named
> tables for which I would like to expeditiously execute a UNION ALL in
> succession. e.g. "SELECT * from (x-2) UNION ALL SELECT * FROM (x-1) UNION
> ALL SELECT * FROM x UNION ALL" etc... where (x-2), (x-1), x are three such
> tables. I can pull all the tables I need to create this very large UNION
> ALL query from INFORMATION_SCHEMA.TABLES, so is there any way to somehow
> construct a looped stored procedure such that I don't have to manually
> type in the full UNION ALL query?
> Thanks,
> Dave
> --
> ______________________________
> Remove "_SPAM" to reply directly.
>|||
> Why?
This is simply the way the db has been designed. Individual clients access
individual tables.

> Create a UNION by querying the INFORMATION_SCHEMA and then cut and
> paste the result. Put the UNION in a view and query that in your proc.
> Take a look at the partitioned views topic in Books Online.
> Better still, consider combining this into a single table unless it's a
> case of "I'm not allowed to fix my database".
This is what I am attempting to do; scheduling a table creation by creating
a UNION ALL of these individual tables. Rather than having to manually add
these tables to the UNION ALL query prior to the creation of the one large
result, I am attempting to programmatically create this UNION ALL because
there are a variable number of individual tables. So, I'm not sure how the
idea you have above will accomplish this.
Dave|||
> Insead of trying to perform a union all, maybe a different appear would be
> better...
> 1. Create a temporary table with the structure of your output.
> 2. create a cursor which will loop through the tablenames that you need to
> query.
> 3. use the table name to create a dynamic sql statement (see
> sp_executesql) that will populate the temporary table with the data that
> you need to select.
> 4. after the loop, perform a single select on the temp table.
> Be warned, the performance of this will be extremely bad.
Colin,
Thanks for the ideas. Performance is not an issue here because I am indeed
creating a table from this large UNION ALL query result.|||>> This is simply the way the db has been designed. Individual clients acces
s individual tables. <<
Well, kill the moron who did this and fix it!! The name of this design
flaw is "attribute splitting" and it can take several forms.
1) Putting the same set of entities into many tables. There are two
sub-cases.
a.The absolute newbie usually mimics sequential numbered or
temporally labeled tapes in each table.
b. The newbie with a few ws experience splits the table on the
values of one or more attributes. For example, taking a Personnel
table and making it into "MalePersonnel" and "FemalePersonnel" tables.
2) Taking a single attribute and splitting in into multiple columns.
There are two sub-cases.
a. The multiple columns are in the same table. The most common
example is having a group of bit flag columns instead of a single
well-design encoding in a column. They have to be combined to get the
attribute's value and you need multi-column CHECK() constraints to
control the combinations.
b. The split column is in multiple tables. The most common example
is trying to force an OO design in an RDBMS, so that joins are required
to assemble a meaningful entity. . They have to be combined via a join
to get the attribute's value.|||As David has suggested, look at partitioned views. You can create a
single entity (a view) that contains all of the data from your tables.
Through the use of check constraints, the optimizer can do partition
exclusion, which means it will only select data from the appropriate
underlying tables. The VIEW is also able to be UPDATED, so you end up
dealing with a lot less mess than trying to dynamically select the
appropriate tables during your selection time.
Of course, the only real reason for using a partioned view is to help
performance when dealing with large datasets; in your case, it sounds
like you're having to do an end-run around a design choice that should
be reconsidered.
Stu

Loop through relation to set incremental number

I have a relation with two attributes acting as the primary key. Each key
is a foreign key to another table. The requirements have changed and now I
need to set the relation with a single auto increment primary key. The
existing relation has about 45k tuples.
As you might guess, if I add a new attribute with the increment on I get
errors because the new attribute can't contain NULL values. I have tried to
look at ways to loop through each tuple and set the new attribute equal to a
counter variable but the end result is the value is always one. (That is
the start of my counter).
Any thoughts and suggestions are greatly appreciated.
WBIf I understand your question properly...
Try the following...
Alter Table <TableName> Add <NewColumnName> Integer Identity Not Null
This should add a new column (Attribute) to the table (Relation) and
automatically populate it with non-null sequentially increasing unique
numeric values.
"WB" wrote:

> I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
to
> look at ways to loop through each tuple and set the new attribute equal to
a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>
>|||"WB" <none> wrote in message news:O1WVap0IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
> I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
> to
> look at ways to loop through each tuple and set the new attribute equal to
> a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>
--Starting with
create table t(c1 int not null, c2 int not null, c3 varchar(255),
constraint pk_t primary key (c1,c2))
--create
create table t_new(id int identity primary key, c1 int, c2 int, c3
varchar(555),
constraint ak_t unique(c1,c2))
insert t_new(c1,c2,c3) select c1,c2,c3 from t
exec sp_rename t,t_old
exec sp_rename t_new,t
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e5RcQ40IFHA.3628@.TK2MSFTNGP15.phx.gbl...
> insert t_new(c1,c2,c3) select c1,c2,c3 from t
Might want to add an ORDER BY clause to that one (if there's some
meaningful order?)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||I failed to mention that the database is part of a merge replication. Will
your suggestions work across merge replication?
"WB" <none> wrote in message news:O1WVap0IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
to
> look at ways to loop through each tuple and set the new attribute equal to
a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>sql

Loop insert

Hi,

I will do my best to explain my question:

I have a field called CUSTOMER which contains a number such as C00001,
C0002 etc.

However i now have a new field called XCUSTOMER which is the new number that they relate to.

CUSTOMER C00001 now relates to XCUSTOMER 493845.
CUSTOMER C00002 now relates to XCUSTOMER 494343.

Basically there are hundreds of these and i dont have the time to manually enter the data. I just want to say "where customer = C00001 then insert 49494 into XCUSTOMER and then loop through and insert them all.

My table is called CUSTOMERINFO.

If anyone could help it would be much apprieciated as it would save me so much time.

Thanks:)update customerinfo
set xcustomer = case customer
when 'c00001' then 493845
when 'c00001' then 494343
...
end

Loop

Hi ,
I like to build a query to generate a virtual running number from the
table.
Something like "Select @.X , fieldA from TableA" . The result should look
like ,
@.x FieldA
-- --
1 AAA
2 BBB
3 CCC
Please help
Travis Tan
On Wed, 5 Oct 2005 00:04:04 -0700, Travis wrote:

>Hi ,
> I like to build a query to generate a virtual running number from the
>table.
>Something like "Select @.X , fieldA from TableA" . The result should look
>like ,
>@.x FieldA
>-- --
>1 AAA
>2 BBB
>3 CCC
>Please help
Hi Travis,
Maybe something like this?
SELECT COUNT(*) AS [@.x], a.FieldA
FROM TableA AS a
INNER JOIN TableA AS b
ON b.FieldA <= a.FieldA
GROUP BY a.FieldA
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Lookup on a range

I want to check to see if my document number (let's say, 11) is in between two values: from a table in my database, I have a min value and a max value; how can I check to see if the number is between the min and the max?

Thanks!

Jim Work
In the control flow, you may want to issue an execute sql task with the query being: select min(value) as minvalue, max(value) as maxvalue from table.

Then map the results to two variables.

Then, in the data flow, you can compare input to the two variables using a conditional split, derived column, etc...

Lookup Arrays

I need to load an array of order numbers.. Then later I need to be able to
'lookup' in that area for a specific order number (for example one that user
keys into text box).
how is this accomplished? ive researched Array and Arraylist and cannot
figure how without a bunch of work.
There must be something simple for this.
Can you help?
Thanks.
jrHello Jr,
Welcome to the MSDN newsgroup.
From your description, you have an order number list which want to be
stored in a list container and you'll lookup some certain order numbers
from that container later in your SQL Reporting service's report, correct?
Based on my experience, since these code should be in your custom code
period or an separate assembly, so you're using them as custom code or
custom assembly in your report , right? If this is the case, what
available to us are all those utility or collection classes in the .net
framework system.collections namespace(Such as the Array, ArrayList you
mentioned).
For your scenario, I'm wondering how will you identify a certain order
number? Just through the orderID or will you give each order number another
identity(a key value) for lookup? Here are two generic cases:
1. If you just want to get the order item's index in the container(such as
Array) through the order number(suppose its of string type), you can just
Array.IndexOf method to locate a certain item in the Array:
#Array.IndexOf, m¨¦thode (Array, Object)
http://msdn2.microsoft.com/fr-fr/library/7eddebat.aspx
Here is a simple test class which intializing the Array(orderList) in the
type's static initializer and expose a public static method for lookup item
index in the array:
==================public class ArrayUtil
{
public static string[] OrderArray;
public ArrayUtil()
{
}
static ArrayUtil()
{
OrderArray = new string[10];
for (int i = 0; i < OrderArray.Length; i++)
{
OrderArray[i] = "Order_" + i;
}
}
public static int FindOrder(string num)
{
return Array.IndexOf(OrderArray, num);
}
}
=====================
2. If each order nunber will be stored with an identity value as the lookup
key, I think the "HashTable" class is the reasonable choice. HashTable
class support storing multiple key/value pairs and have method for looking
up a certain item in it via its key value:
#Hashtable Class
http://msdn2.microsoft.com/en-us/library/system.collections.hashtable.aspx
Hope this helps. If there is any paricular concerns in your scenario or if
you have any other ideas ,please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Jr,
How are doing on this issue, have you got any progress or does my last
reply helps you a little on this? If you have anything unclear or still
anything else we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)