Help with connection string

I have a connection string in my web.config file:

<add key="cnString" value="data source=ServerName;initial catalog=DatabaseName;password=Password;persist security info=True;user id=UserName;packet size=4096" />

and it works just fine with the SQL production server.
Recently the testing SQL server was created and I can login to it with Enterprise Manager.

But when I try to change the connection string just putting a new server name, database, user name, and password, it gives me the error:

Login failed for user 'UserName'

What should I look in the database or SQL server that is different from a production one? Or what should I ask since I don't have admin rights to both servers?

BTW, I've already tried to play with Trusted_Connection parameter (True/False) and it didn't help.

Thank you in advance for your help.You should check whether the user you are specifying in the connection string exists on the sql server and whether that user has access to the database that you are setting as "Initial catalog"

Hope that helps
Kashif|||Yes, the user exists and has an access to that database.

help with Check contraints

I need to add a check contraint to a table in SQL Server 2000 that would do
the following
if the value of [type] = 1 then [year] cannot be null
is this possible to do? because for the other values of [type] i want the
[year] to be null
thanks for any help!
benALTER TABLE your_table
ADD CONSTRAINT CK_your_table__type_year_match
CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>I need to add a check contraint to a table in SQL Server 2000 that would do
> the following
> if the value of [type] = 1 then [year] cannot be null
> is this possible to do? because for the other values of [type] i want the
> [year] to be null
> thanks for any help!
> ben|||Thank you very much. That was exactly what i was looking for
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Sorry, one more question about check contraints.
is it possible to have a contraint that only allowed entries where for every
pair of columns [a] and [b] there is only 1 value in the [type] column? or
can this only be done through stored procedures and functions?
thanks again
"Jacco Schalkwijk" wrote:

> ALTER TABLE your_table
> ADD CONSTRAINT CK_your_table__type_year_match
> CHECK ((type = 1 AND year IS NOT NULL) OR (type <> 1 AND year IS NULL))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:8E1D564C-58FC-4DFC-8D0E-C4E32D9C322D@.microsoft.com...
>
>|||Please explain "only 1 value in the [type] column".
ML|||well, ill have 3 possible valuse for type: 1,2,3. the other columns
category_ID, area_ID are used to specify a location in a grid like system.
i
want there to only be 1 value in the type column for every combination of
category_id and area_id. this will have to be similar to a query on the
entire table to ensure that the pair doesnt have an entry with type = 1 and
a
entry with type = 2 but multiple entries of type=1 is alowed.
i hope that helped a little
"ML" wrote:

> Please explain "only 1 value in the [type] column".
>
> ML|||On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:

>Sorry, one more question about check contraints.
>is it possible to have a contraint that only allowed entries where for ever
y
>pair of columns [a] and [b] there is only 1 value in the [type] column? or
>can this only be done through stored procedures and functions?
Hi Ben,
UNIQUE (a, b, type)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||that is a valid check contraint? i tried that and it was giving me errors.
sql server 2k. also, i dont think that will allow me to have multiple type=
1
values for each a,b pair. I need that to be allowed, but the type values
cannot be different.
"Hugo Kornelis" wrote:

> On Fri, 28 Oct 2005 08:16:08 -0700, Ben <ben_1_ AT hotmail DOT com>
> wrote:
>
> Hi Ben,
> UNIQUE (a, b, type)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||In that case you should have a separate table with (category_ID, area_ID) as
the Primary Key and Type as the other column. Your database is not properly
normalised, and this will cause all kinds of problems.
Feel free to post your table definitions and a description of your business
problem, and people will give you advise on how to improve your database.
In the mean time, if you don't have the scope or authority to make these
changes, you can apply a band aid with an indexed view (untested):
CREATE vw_chk_your_table
WITH SCHEMA_BINDING
AS
SELECT category_ID, area_ID, Type, COUNT_BIG(*) AS cnt
FROM your_table
GROUP BY category_ID, area_ID, Type
GO
CREATE UNIQUE CLUSTERED INDEX ixc_vw_chk_your_table
ON vw_chk_your_table (category_ID, area_ID)
Jacco Schalkwijk
SQL Server MVP
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:45CE651A-D484-4FD1-8230-1D64BA2E34E8@.microsoft.com...
> well, ill have 3 possible valuse for type: 1,2,3. the other columns
> category_ID, area_ID are used to specify a location in a grid like system.
> i
> want there to only be 1 value in the type column for every combination of
> category_id and area_id. this will have to be similar to a query on the
> entire table to ensure that the pair doesnt have an entry with type = 1
> and a
> entry with type = 2 but multiple entries of type=1 is alowed.
> i hope that helped a little
> "ML" wrote:
>|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 52
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newshub.sd
su.edu!newsfeed.news2me.com!newsfeed2.easynews.com!newsfeed1.easynews.com!ea
synews.com!easynews!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-02!sn-post-01!supe
rnews.com!corp.supernews.co
m!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:562181
On Fri, 28 Oct 2005 23:44:03 -0700, Ben <ben_1_ AT hotmail DOT com>
wrote:
(cut topposting)
>"Hugo Kornelis" wrote:
>
(paste topposting)
>that is a valid check contraint? i tried that and it was giving me errors.
>sql server 2k.
Yes, it's valid. What were the errors you got? And what was the exact
text of the complete statement you used it in?

>also, i dont think that will allow me to have multiple type=1
>values for each a,b pair. I need that to be allowed, but the type values
>cannot be different.
Re-reading what you write, I now see I misinterpreted your question. The
asnwer to your original question should have been:
UNIQUE (a, b)
That would allow only 1 value for type for every pair of values for a
and b, as you originally requested. As such, it would NOT allow multiple
type=1 for a a,b pair, since multiple type=1 is incompatible with "only
1 value in the [type] column".
At this point, I have sincere doubts if these columns really should be
combined in the same table at all. But I must also admit that I'm no
longer sure if I actuallly understand your requirement. It might help if
you posted a few concrete examples of rows of data that can or can not
be in the table at the same time. It would also be a tremendous help if
you could explain the actual business problem that you're trying to
solve.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

help with an expression

I need to create an expression to handle a simple calculation. Here is what I have:

=Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

What do I need to add to handle the when the MH_POS_Goal = 0?

I tried isnull and nullif and kept getting errors.

Hi

You can create a User Define function for that where you can check the MH_POS_Goal value. If it is 0 then return 1 other wise retirn the acutal value... and I hope that you know anything deiveded by 1 is by nature and don't change the value . means that

1 = 1/1

200 = 200/1

|||

Something like this?

= IF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1,Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

,'')

|||

This expression processes ok. But I am still getting the error message. Can someone help me update it to work correctly?

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")),0)

|||

The render engine of SSRS evaluates the complete expresse you enter.

In general this means that preventing a DivByZero exception with an IIF statement to check for a zero (null) value and display something else in case it's true, isn't enough. For the 'divided by' number also a IIF statement is needed, because the complete expression is evaluated.

So in your case this would be: =IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Notice that the true-part, 1, of the last IIF statement is never used, but is only needed for the SSRS engine not to generate an error.

|||

I didn't means this ....

I mean .....

IDF_CHECK (value)
IF Value = 0
Value = 1
RETURN Value

And Now check ......

(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")/IDF_CHECK(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))

|||

I tried this:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

and I got this error: Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'

Any ideas?

|||

There was an extra ')'.

This works:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Thanks for the help!

|||

If I use this in a report with subtotals I get the calculation at the total level and not at the subtotal level. Any ideas how to get the calculation to perform at the subtotal level?

|||

Anyone?

Help with an expression

The error is:
The value expression for the textbox â'BillAddressâ' contains an error:
[BC30201] Expression expected.
=If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value) = 0 AND
Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value) = 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value)= 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.value)= 0 AND
Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
Fields!AcctName.Value + Chr(10) +
"No Address Available" + Chr(10) +
"ID: " + AccountNumber
Many ThanksJeff,
I thought VB was the scripting language to be used in expressions. Why
are you concatinating with '+' operators, when you should be using '&'
operators?
thx
-jsh
"Jeff Metcalf" wrote:
> The error is:
> The value expression for the textbox â'BillAddressâ' contains an error:
> [BC30201] Expression expected.
>
> =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr (10) +
> Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
>
> Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.value)= 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> Fields!AcctName.Value + Chr(10) +
> "No Address Available" + Chr(10) +
> "ID: " + AccountNumber
> Many Thanks|||Beats me...I'm just trying to get it to work. The '+' operator works for
for concatinating in other SRS reports and in SQL. I'm not a VB or .Net
programmer, so I have no idea if it's right or not, lol.
"jsh02_nova@.hotmail.com" wrote:
> Jeff,
> I thought VB was the scripting language to be used in expressions. Why
> are you concatinating with '+' operators, when you should be using '&'
> operators?
> thx
> -jsh
> "Jeff Metcalf" wrote:
> > The error is:
> >
> > The value expression for the textbox â'BillAddressâ' contains an error:
> > [BC30201] Expression expected.
> >
> >
> > =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> > And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr (10) +
> > Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> >
> > Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.value)= 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > "No Address Available" + Chr(10) +
> > "ID: " + AccountNumber
> >
> > Many Thanks|||I'm not sure if this is what's causing the problem but SRS uses an
"inline if" -IIF- instead of IF THEN ELSE.
The syntax is =IIF(test expression,true action, false action).
Using your expression from above it would be something like this:
=IIf( Len(Fields!BillTo_Line1.Value)= 0 And
Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 ,
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " +
Fields!BillTo_StateOrProvince.Value + " " +
Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value ,
IIF( Len(Fields!BillTo_Line2.Value)= 0 And
Len(Fields!BillTo_Line1.Value) > 0 And Len(Fields!BillTo_Line3.Value) >
0 , and so on
Nested IIFs can get really ugly. If I were you and if it's possible
I'd do as much as you can with case statements in SQL.
Good luck

HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

Help with a switch statement.

Hey again,

So here's what I'm trying to do: I have three columns of data. Sometimes only one of the columns will contain a value while others may contain a null. If two or three contain a value it will be the same. So if I'm building a table in the layout designed and I want the value of the table to be the value stored in these columns. In pseudocode it looks like this:

Switch(column1 and column2 are null, value = column3, if column1 and column3 are null, value = column2, otherwise, value = column1)

Something like that where column1 is the default so if column 1 has a value then set the textbox value to it otherwise find a column that has it. I know that at least one column will definitely have a value. Anyone that can provide guidance on how to execute these I would appreciate it greatly.

Thank you!

Hello Keith,

Try this:

=Switch(

Fields!Column1.Value is nothing and Fields!Column2.Value is nothing, Fields!Column3.Value,

Fields!Column1.Value is nothing and Fields!Column3.Value is nothing, Fields!Column2.Value,

1 = 1, Fields!Column1.Value

)

Hope this helps.

Jarret

|||Is 'nothing' the keyword for null? I kept typing in null and it gave me invalid identifier or something, I couldn't figure out how to check it in the switch.|||

Yes sir.

You could also use Len(Fields!Column1.Value) > 0 for that check.

Jarret

|||I'll try it out, thanks alot!|||Do you, or anyone else, know how I would do this with the actual query, so they they are all consolidated into one field? I know I need to use the case statement but I can't get the syntax right. Basically it's the same situation, I'm taking from three different places, one or more may have a value but I just want to end up with one column populated. Thank you!|||Looks like you could use a COALESCE() to do this in a query. COALESCE will return the first non null value in the list of values. Ex. COALESCE(Value1, Value2, Value3) will return the first non null value checking them in order of value1, value2 and value3.|||That's great, thank you!sql

Help with a special stored procedure

Hi

I have a table there have a primary key, this value have i also in another
table as a forreignkey, so good so far.

Here it is
If the number in may primarykey in table 1 , dosn't exits in table 2 then
delete records in table 1

I have made this in a ASP page with a view there list all records where the forreignkey in table 2 are NULL and then delte all records in table one

Can i made this as a stored procedure ?

regards

alvin

You really want a trigger on table 1 to check table 2 before insert.

Research INSTEAD OF Insert Triggers.

What this will do is instead of inserting a new record into table 1, it will check table 2 for a match. If there is no match, no record is inserted into table 1.

It's better to catch the record before it goes in, than to insert the record and then have to remove it later.

Adamus

|||

Hi

I don't believe you understand

When i made a post in table one i also make a post in table 2

All works fine

But tabel 2 i also connected with table 3 and here i have in my diagram a cascade delete

So when i delete a record in table 3 it's delete a record or more in table 2
then i have my record in table 1, this i can't delete when i delete the record in table 3
so what i want is to delete all record in table 1 if the record in table 2 are deleted

hop you understand ?

Alvin

|||

Ok table 3 is new...but the answer is still the same...triggers not sp's

When you delete from any table, also delete from other tables...correct?

So you want INSTEAD OF DELETE

Adamus

|||

I try to explain and maybe you can help

In table 1 i have a ID theis ID can bee many times in table 2
Table 2 have also a int there connect to table3

all works fine

When a date field in table3 is over current date then i delete the record in table
And when it does this it also delete the record in table2.

after sometime all the records in table2 there have the ID from table 1 is been deleted
and when there are no more record in table2 there in my feild have the same numbers
as the ID in table 1 then i wnt to delete the record in table 1

Like:

Delete all records in table 1 if table1.ID <> from table2.field

In vbscript i can make a loop to check if the Id from table1 are in table2
if the catch this number = do nothing
if the find the number = delte record

i Can't explain it better. Sorry

Alvin

|||

It looks to me like you're wanting something like this:

create procedure delete_records_from_table1

as

delete from table1

from table1

left join table2

on table1.ID = table2.ID

where table2.ID is null

The left join will include everything in table1, regardless of whether there's a matching record in table2, and then the where clause excludes all of the records where there is a match in table2. This just leaves those records where there's no match in table2, and these can be deleted.

Is this the sort of thing you were looking for?

Iain

|||

Yes yes yes

Thanks

Alvin

sql

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

AS

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

Help wit Query to list duplicates

I am trying to create a list of those cases where a specific field value was
duplicate with a set of rows. The query I am trying is shown below (it is
listing all rows instead of just those where there is a duplicate value in
the field Eqp1Voc.
ID is an unique key for each row.
What did I overlook?
Wayne
========================================
=
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores A JOIN DCScores B
ON A.ID <> B.ID
AND A.Eqp1Voc = B.Eqp1Voc)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
========================================
=> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
(SELECT B.ID
FROM DCScores
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Thanks for the quick response but that doesn't return any rows (and I know
there are duplicates in that field!) With your suggestion, the query now
looks like this:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores B
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238nw0CEQFHA.688@.TK2MSFTNGP14.phx.gbl...
> (SELECT B.ID
> FROM DCScores
> Group by B.ID
> HAVING COUNT(Eqp1Voc) >1)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Sorry for that missed up something...
Here it goes:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE Eqp1Voc IN
(SELECT Eqp1Voc
FROM DCScores
Group by Eqp1Voc
HAVING COUNT(Eqp1Voc) > 1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Jens.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Jens;
Thanks again but with that change, it returns all rows, not just those with
a duplicate value?
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23FGm0YEQFHA.204@.TK2MSFTNGP15.phx.gbl...
> Sorry for that missed up something...
> Here it goes:
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE Eqp1Voc IN
> (SELECT Eqp1Voc
> FROM DCScores
> Group by Eqp1Voc
> HAVING COUNT(Eqp1Voc) > 1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> Jens.
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Try this:
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
Group By Eqp1Voc
Having Count(*) > 1)
WHERE CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
"Wayne Wengert" wrote:

> I am trying to create a list of those cases where a specific field value w
as
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>
>|||Thanks for the suggestion but that yields an error: "Incorrect syntax near
the keyword 'WHERE'."
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> Try this:
> SELECT UnitName, Eqp1Judge,
> Eqp1Voc
> FROM DCScores S
> Where Exists
> (Select * From DCScores
> Where Eqp1Voc = S.Eqp1Voc
> Group By Eqp1Voc
> Having Count(*) > 1)
> WHERE CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> "Wayne Wengert" wrote:
>
was
is
in|||Replace this "WHERE CircuitID" WITH "AND CircuitID"
Jens Smeyer.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Thanks for the suggestion but that yields an error: "Incorrect syntax near
> the keyword 'WHERE'."
> Wayne
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> was
> is
> in
>|||Jens;
I took that advice and also realized that the Where clause needs to be
applied to that inner select. I changed it to the following and now it
works.
I appreciate all the help.
==================================
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Group By Eqp1Voc
Having Count(*) > 1)
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
==================================
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KCoZCFQFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Replace this "WHERE CircuitID" WITH "AND CircuitID"
> Jens Smeyer.
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
near
(it
value
>

Help w/aggregate function in Matrix

On a report we have a matrix. The data cell has the following expression:
=iif(Fields!Score.Value=0, "", Fields!Score.Value)
When we run the report, we get the following warning:
The value expression for the textbox 'Score' references a field
outside an aggregate function. Value expressions in matrix cells should be
aggregates, to allow for subtotaling.
What does this mean and how can I resolve it?
The goal it to suppress the display of zero (0). We have tried setting the
format of the data cell to be "#", but the zero is still displayed. So we
have been using expressions like the above to achieve this.
The user creating this report is using the stand-alone C# IDE with Reporting
Services. This warning prevents them from previewing the report. Another
user using VS.NET 2003 is able to preview the report despite the warning.
The report renders on our test reporting server. If we need to just ignore
the warning, how can we get the user using C# to be able to preview the
report?
Thanks,
ChrisMatrix cells are always in the scope of two groupings and you could have
multiple data rows which match the group instance values. Therefore, you
should always use aggregate functions when referencing fields in a matrix
cell (hence, a processing warning gets generated).
If you don't use an explicit aggregate function in the matrix cell, we would
implicitly use the first row's field value. I believe you actually don't
want just the first value, but rather the sum - so you should change the
expression to:
=iif(Sum(Fields!Score.Value)=0, "", Sum(Fields!Score.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
In your case, I believe you want
"Chris Walls" <chwalls@.community.nospam> wrote in message
news:OVqzX42OFHA.1500@.TK2MSFTNGP09.phx.gbl...
> On a report we have a matrix. The data cell has the following expression:
>
> =iif(Fields!Score.Value=0, "", Fields!Score.Value)
>
> When we run the report, we get the following warning:
>
> The value expression for the textbox 'Score' references a field
> outside an aggregate function. Value expressions in matrix cells should
> be aggregates, to allow for subtotaling.
>
>
> What does this mean and how can I resolve it?
>
>
> The goal it to suppress the display of zero (0). We have tried setting
> the format of the data cell to be "#", but the zero is still displayed.
> So we have been using expressions like the above to achieve this.
>
>
> The user creating this report is using the stand-alone C# IDE with
> Reporting Services. This warning prevents them from previewing the
> report. Another user using VS.NET 2003 is able to preview the report
> despite the warning. The report renders on our test reporting server. If
> we need to just ignore the warning, how can we get the user using C# to be
> able to preview the report?
>
>
> Thanks,
> Chris
>
>

Help using a value from one dataset to lookup a value from a second dataset

Hi All,

I'm quite new to SSRS (and .net development as well) so any help would be useful. My problem is this: I'm running a report from a Sybase ASE datasource to pull data into a table control. One of the fields I'm pulling in is called assignment group. Now, in the same report output I also need to display who the manager of that group is... unfortunately this information is not stored in the same database. We store the manager information in a completely seperate Oracle database. So, I need some method of 'linking' the data from my Sybase dataset to a table in Oracle and I can't quite figure out how to go about it. Every record would have a manager and there could be 10k records returned so I need to be carefull about performance issues.

I was starting to go down the path of creating a .net dataset in a custom assembly which would be filled via a query to the Oracle database. What I was thinking is to create and fill a dataset with my lookup data from Oracle then, in the report, call a function from the table control which would search through the dataset in memory and return the actual manager name.

Am I on the right track or am I overcomplicating the solution? Will this be too inefficient from a performance perspective? Would a subreport be a more efficient solution?

There are a couple of solutions for you:

1) you can write a custom data extension that does the magic merge under the covers. A custom assembly won't help here because it operates on one row at a time - if you have a reasonable nuumber of rows, it might work, but with lots of rows it will probably be inefficient.

2) you can use the Linked server feature of SQL Server. This allows you to build a table/view that uses ODBC connections to other data bases to retrieve data. It has pretty reasonable performance and you don't have to create and maintain custom code. Downside, is you need to add a SQL database in addition to your existing Oracle and Sybase ones :-).

Hope that helps,

-Lukasz

Help Using a field value in a url jump statement

I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fine
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
"535d58cc-a1b3-da11-9864-001320020c86" & "');"
but this one does not allow for the pointer hand to show up when cusror is
over the row
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
Fields!res_dealCompanyid.Value & "');"
Do I need to convert Fields!res_dealCompanyid.Value to a string?
Can I assign it to a variable and then use it in the Jump to URL?
I do not know what else to try
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> "535d58cc-a1b3-da11-9864-001320020c86" & "');"
> but this one does not allow for the pointer hand to show up when cusror is
> over the row
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> Fields!res_dealCompanyid.Value & "');"
> Do I need to convert Fields!res_dealCompanyid.Value to a string?
> Can I assign it to a variable and then use it in the Jump to URL?
> I do not know what else to try
> Mike

Help translating Access SQL to T-SQL

Can someone tell me what this Where clause (from MS Access) should be T-SQL?

Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1.

Where IIf([reason] LIKE '%DIST%',Val([movement]),1)>=1

Thanks!

something like this. "iif" is not a valid sql fucntion

where reason like '%dist%' and movement=> 1

val is also not valid so you may use cast or convert

therefore

where reason like '%dist%' and cast (movement, money) => 1

|||

You can do below:

where case when reason like '%DIST%' then sign(Movement) end = 1

-- or

where reason like '%DIST%'

and sign(Movement) = 1

-- or below which will use index on Movement if available

where reason like '%DIST%'

and Movement >= 1

|||

Replace the IIF() with Case:

Case when [reason] like '%DIST%' then...

Am I correct in intrepreting Val() as converting to a number (saves me a google)? Then you want to use Convert().

Final syntax:

Where Case When [reason] Like '%Dist%' then Convert(int, [movement] ) Else 1 End >= 1

You could also use NULL with the Else case.

....Guess I'm a slow typist...

|||

Thanks anomolous! That was the right answer. I just had to change int to real because [movement] holds a decimal value.

Yes, Val() in Access converts a string to a number And if it's NULL, Val() will return 0. Will Convert() return a 0 for a NULL value? Or do I need to use a different function so that I get a 0 if [movement] holds a NULL value?

|||

You have to use coalesce or isnull to check for NULL values. All built-ins return NULL for NULL input. Do something like:

where reason like '%DIST%'

and cast(coalesce(Movement, '') as int) >= 1

Note that CAST or CONVERT in TSQL will return error if the value cannot be converted. I don't know the behavior of VAL in Access. So in that case, you will have to do additional checks like:

where reason like '%DIST%'

and cast isnumeric(Movement) when 1 then cast(coalesce(Movement, '') as int) end >= 1

Even use of ISNUMERIC will not work for all cases since it checks for integer, numeric and money data type conversion semantics. So it is possible that you could have values that can convert to money but not int. So you will be better off actually modifying the schema such that Movement column is integer and it stores only integer values. Mixing different data types in a string column and manipulating it using TSQL is problematic in lot of ways. And it is often done incorrectly leading to bad performance due to conversions, wrong results, run-time errors and so on.

help Stored proc

Hi,
Could any one please help me in creating sp.
It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100.
Thanks in advance.
-ssTry this:

create procedure p_odd_even(@.i int)
as
select number -1 + @.i from master..spt_values where type = 'P' and number % 2 = 1 and number <= 100|||Very clever trick - what is the purpose of this table in SQL|||too clever

you would need DISTINCT in there, vaxman, and also restrict number to between 0 and 100, not just less than or equal to 100

the use of master..spt_values is a hack

better to declare an integers table, because it will come in handy in so many other queries|||Not sure what spt_values is for. I have seen it used for things like this. In examining the table, it looks like type 'P' does return distinct integers, but as Rudy says, much better to have your own table. I create one like this:

select top 8000 id = identity(int,1,1) into Numbers from sysobjects s1, sysobjects s2, sysobjects s3

(8000 because I usually use it for parsing varchar strings but change for your needs)

Or you can just generate your 50 numbers on the fly. For 50 numbers this is probably more efficient (no I/O and 50 numbers is small enough not to generate a work table) but for larger counts a real table is better because statistics will be kept for it and indexes used.

create procedure p_odd_even(@.i int)
as

select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||You could always use:CREATE PROCEDURE pSSkris
@.arg INT = 1
AS

SELECT n
FROM (SELECT 1 + 10 * tens + ones AS n
FROM (SELECT 0 AS ones UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN (SELECT 0 AS tens UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS b) AS c
WHERE n % 2 = 2 - @.arg
ORDER BY n

RETURN
GO-PatP|||uh Pat, didn't I say that? (but you only need 50 numbers...)|||uh Pat, didn't I say that? (but you only need 50 numbers...)Sort of... You relied on another table outside of the problem definition, but my suggestion was self contained. I suspect that mine will be easier to explain too, but that's a relative kind of thing.

-PatP|||I think you missed this part:

create procedure p_odd_even(@.i int)
as
select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||Sorry, I was only looking at your first posting in my previous comment.

-PatP|||As an interesting wrinkle, you could also use:SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 0 AS b UNION SELECT 1) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n-PatP|||As an interesting wrinkle, you could also use:CREATE PROCEDURE pSSkris2
@.arg INT = 1
AS

SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 2 - @.arg AS b) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n

RETURN-PatP|||I wonder if you'll get an "A"...|||Who wants to explain it? Without an explanation, I'd expect that the code is worthless.

-PatP|||if it's for a school assignment, i would stringly suggest to sskris to submit the solution in post #2

that'll get an A+|||I would expect that any of the solutions that we've offered will get a trip to the Dean's office and an opportunity to use this experience in their upcomming ethics class (whether it was part of the curriculum before now or not). These solutions may help them think about the answer they want to give, but I can't imagine any of them being "safe" to turn in as they are.

-PatP|||Thanks to all of you..
I really appreciate that.
Kris

help SP Code

I type Code in SP

Exec ('INSERT INTO XXX (A,B,C)

Select A,'Y8',C From YYY

GROUP BY A,B');

But it has error, Value 'ABC' not correct

Why, Thanks

William

Exce SP Code have error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Y8'.

|||

Try this:

EXEC ('INSERT INTO XXX (A,B,C) Select A,''Y8'',C From YYY GROUP BY A,B');

Chris

|||

If you want to use the single quote (‘) with in the sql string you have to use the escape sequence.

Many languages support \ as escape sequential char. But in sql server the same char need to be repeated (twice).

Example:

Select @.a = 'Sql Server''s'

So you have to change your query as follow as,

Exec (

'INSERT INTO XXX (A,B,C)

Select A,''Y8'',C From YYY

GROUP BY A,B'

);

help selecting all rows that contains no null value

I have data on MS SQL Server that have over 60 columns, I would like to
select 30 of these columns that may or may not contain NULL and I don't
want to write out all 30 columns and check for IN NOT NULL. Does anyone
know how to do that?Not trying to be harsh, but... start typing. :)
Is this a recurring problem, or are you shooting for a one time
solution? If one-time, the easiest way to do it is to just write the
SQL query that you're attempting to avoid. If you're trying to develop
some sort of administrative tool to help you validate data, you could
script something that uses dynamic SQL (using the syscolumns table).
That's not something that I would advocate giving to the average user.
Stu
timhz...@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||You must perform each IS NOT NULL test in the query. However, you can
save some typing. This will generate a query to get you started, just
copy the results.
declare @.tbl varchar(50)
set @.tbl = 'Categories'
SELECT CASE WHEN C.ORDINAL_POSITION =
(select min(ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS C2
where C2.TABLE_NAME = @.tbl
and C2.IS_NULLABLE = 'YES')
THEN 'SELECT * FROM ' + C.TABLE_NAME +
char(13) + CHAR(10) +
' WHERE '
ELSE ' AND '
END +
C.COLUMN_NAME + 'IS NOT NULL' + char(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @.tbl
AND C.IS_NULLABLE = 'YES'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
On 29 Jun 2006 08:48:16 -0700, timhzhou@.gmail.com wrote:

>I have data on MS SQL Server that have over 60 columns, I would like to
>select 30 of these columns that may or may not contain NULL and I don't
>want to write out all 30 columns and check for IN NOT NULL. Does anyone
>know how to do that?|||No, there are no lazy shortcuts in T-SQL to select "a set of columns".
But, there are lazy shortcuts to generating the list so that you can create
a valid and reasonable T-SQL statement more quickly. What are you using,
6.5, 7.0, 2000, 2005? In Query Analyzer or Management Studio, when you
expand a table and you see a folder called columns, drag it to the query
window. Voila, like magic, huh?
Laziness is not enough of a reason to use SELECT * (or s a similar
alternative).
A
<timhzhou@.gmail.com> wrote in message
news:1151596096.534039.98000@.p79g2000cwp.googlegroups.com...
>I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?
>|||before you begin anything .. you might want to consider clean up the
data to update those columnes with a default value.
ie.
update table
set column = ''
where column is null
possibly put them in a temporary table?
timhzhou@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||Contrary to Developer opinion, sometimes NULL is an appropriate value.
(Albieit, not as often as it is used just because folks don't take time to
understand the implications.)
A question I posit is: How many responses are there to a Yes/No question?
The correct response is Four. Yes, No, Not Answered, Not Applicable.
The use of the data determines whether or not there is a distinction between
the last two. For example, if I'm analyzing survey results, I wouldn't want
to confound the analysis by combining Not Answered and Not Applicable.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1151597871.024544.185460@.b68g2000cwa.googlegroups.com...
> before you begin anything .. you might want to consider clean up the
> data to update those columnes with a default value.
> ie.
> update table
> set column = ''
> where column is null
> possibly put them in a temporary table?
> timhzhou@.gmail.com wrote:
>

Help retreving Value form sql reader

hello,

I have tried myReader.GetSqlString, GetSqlValue, GetSqlInt16, etc...etc...

But I keep getting an error (System.InvalidCastException was caught
Message="Conversion from type 'SqlInt32' to type 'String' is not valid."
Source="Microsoft.VisualBasic"
StackTrace:
at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value)
at ImportDelimitedFile.SumCurrentAmount() in C:\Documents and Settings\emg3703\My Documents\Visual Studio 2005\EscuelasComunidad\ImportDelimitedFile.aspx.vb:line 556)

Here is my code:

Public Function SumCurrentAmount()As String

Dim sqlconnAs New SqlConnection(ConfigurationManager.ConnectionStrings("GDBRemitanceConnectionString1").ConnectionString)
Dim sqlcmdAs New SqlCommand("SELECT SUM(CONVERT (Int, Field_6)) AS TotalAmount, Record_Type FROM tblTempWorkingStorage_NACHA GROUP BY Record_Type HAVING (Record_Type ='6')", sqlconn)

Try

sqlcmd.Connection.Open()
Dim myReaderAs SqlDataReader
myReader = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read()Then

SumCurrentAmount =CType(myReader.GetSqlValue(0),String)
Return SumCurrentAmount

Else

End If
myReader.Close()

Catch

End Try
sqlcmd.Connection.Close()

End Function

I need to know which "GetSql...(type) should I used to extract field numbre one from myReader.

Thanks a lot,

Try to change this line:

SumCurrentAmount =CType(myReader.GetSqlValue(0),String)

to:

SumCurrentAmount = (myReader.GetDecimal(0)).ToString()

For more information about mapping SqlDbTypes to CLR data types, you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqldbtype.aspx

Help required to update SQL

I have a table named Car with the field name as Position. I want to
update the Position field. If i enter a new value as 4 for position
which already exist, then the existing value 4 and all the below items
like 5,6 and 7 must be incremented by 1
Position
1
2
3
4
5
6
7
How can this be achievedmora wrote:
> I have a table named Car with the field name as Position. I want to
> update the Position field. If i enter a new value as 4 for position
> which already exist, then the existing value 4 and all the below items
> like 5,6 and 7 must be incremented by 1
>
> Position
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> How can this be achieved
UPDATE Car SET Position = Position + 1 WHERE Position >= 4 ;
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
--|||CREATE TRIGGER blahblah INSTEAD OF INSERT AS
BEGIN
declare @.pos int
select @.pos = inserted.position
UPDATE table1 SET position = position+1 WHERE position >= @.pos
INSERT INTO table1 SELECT * from inserted
END
-- please verify the syntax before using - I'm just giving an idea,
don't have a server/BOL around to test & debug it.|||>> I have a table named Car with the field [sic] name as position. I want to update th
e position field [sic]. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Your vageu narrative is also wrong. Columns are not fields. POSITION()
s a reserved word in Standard SQL. Since you used a singular name, you
must have one car; otherwise you would have used a collective or plural
noun.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" , "below" and "last" are
totally meaningless.
I am going to make a guess at what you meant. Do you have a motorpool
in which you assign parkging spaces?
CREATE TABLE Motorpool
(space_nbr INTEGER NOT NULL PRIMARY KEY
CHECK (space_nbr > 0),
vin CHAR(17) NOT NULL);
Re-arrange the display order based on the space_nbr column:
CREATE PROCEDURE SwapParkingSpacees (@.old_space_nbr INTEGER,
@.new_space_nbr INTEGER)
AS
UPDATE Motorpool
SET space_nbr
= CASE space_nbr
WHEN @.old_space_nbr
THEN @.new_space_nbr
ELSE space_nbr + SIGN(@.old_space_nbr - @.new_pos)
END
WHERE space_nbr BETWEEN @.old_space_nbr AND @.new_space_nbr
OR space_nbr BETWEEN @.new_space_nbr AND @.old_space_nbr;
When you want to drop a few rows, remember to close the gaps with this:
CREATE PROCEDURE CloseParkingSpaceGaps()
AS
UPDATE Motorpool
SET space_nbr
= (SELECT COUNT (F1.space_nbr)
FROM Motorpool AS F1
WHERE F1.space_nbr <= Motorpool.space_nbr);
To insert a new car into the motorpool, add the new vehicle to the "end
of the line" and then swap it with the target parking space.

Help required for Splitting up string variable using comma separator

I need a help in SQL Server 2000.

I am having a string variable in the format like -- (1,23,445,5,12)

I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.

Can anyone help me out in splitting the variable using the comma separator...

You can just use the split command e.g.

Dim sAs String ="1,23,445,5,12"Dim splitAs String() = s.Split(",")For Each itemAs String In split Response.Write("Item: " & item &"<br>")Next

|||

I doesnt want this in VB.NET.

I want the same using SQL query in SQL Server 2000.

|||You'll have to create a function to do this. Here's a starting point:http://www.madprops.org/cs/blogs/mabster/archive/2005/12/05/T_2D00_SQL-to-Split-a-varchar-into-Words.aspx|||

Oh, sorry. If you want to do this in SQL, it's a bit harder as it doesn't really have built in string manipulation functions. If you were using a later version of SQL Server you could have registered that .NET code as a CLR function but as you ar using SQL Server 2000, you will have to do something like this:

CREATE PROCEDURE SplitString
@.yourStringvarchar(100)
AS
BEGIN
DECLARE @.StringCountint, @.mycountint, @.mystrlenint
DECLARE @.myvalvarchar(100)
set @.StringCount=Len(@.yourString)
set @.mycount=1
if (CHARINDEX(',',@.yourString,1)=0)
print @.yourString
WHILE (CHARINDEX(',',@.yourString,1)<>0)
BEGIN
if @.mycount=1
set @.myval=substring(@.yourString,@.mycount,CHARINDEX('^',@.yourString,1)-1)
print @.myval
set @.yourString =substring(@.yourString,Len(@.myval)+2,Len(@.yourString))
set @.StringCount= @.StringCount -1
if (CHARINDEX(',',@.yourString,1)=0)
print ,@.yourString
END
end
GO

Help Reqd Urgently (SQL Query)

Pls consider the following table:--

Table Name: ChemoAdmin

Fields:

Field Name: Field Type: Typical Value:

patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg

I require the following output using one query:--

DATE 09/17/2004 09/21/2004

Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg

Pls help.You mean you'd like a specify a daterange and have it shown above the other results returned by the same select-statement (seperated by an empty line)?

Is there any reason why you need this in a single sql-statement?|||It is better if this can be done using one select statement. Because, I would like to create a view that would give the output as shown.|||I don't see a way of doing that in a view. You might want to create a stored procedure instead that produces both the daterange as well as the select output. However, I feel that your aim is off.|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this case

i always write a full outer join as a left outer join unioned with a right outer join where there's no matching row (and then i always flip the right outer join over into a left)
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t2.stationDate = '2004-09-21'
where t1.stationDate = '2004-09-17'
union all
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t2
left outer
join ChemoAdmin as t1
on t2.drugName
= t1.drugName
and t1.stationDate = '2004-09-17'
where t2.stationDate = '2004-09-21'
and t1.drugName is null|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this caseOk, that just begs the question: Why do you avoid FULL OUTER JOIN when it does exactly what is needed? While I use FULL and CROSS joins rather sparingly, when they do exactly what I want I'll cheerfully use the little beggars.

-PatP|||because not every database supports FULL OUTER (yes, i know that microsoft sql server does, but it's easier remembering the workaround than trying to remember which database supports it)

but more importantly, in this instance the join condition include conditions on only one table or the other

i'm not certain that this will produce the same results:select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
full outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate = '2004-09-17'
and t2.stationDate = '2004-09-21'
feel like testing it for us? :)|||Because your code tests both tables, it effectively reduces the join to a conventional INNER join. Any result set rows that "miss" (either right or left) will fail due to the comparison with NULL. The only difference between the two (FULL versus unions of left and right) would be if you used UNION instead of UNION ALL.

-PatP|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see it|||Can any drug in your list have more than 2 dosages ? if so joining the table just twice wont work. Use the following query to find the maximum number of doses for any drug in that table.

select max(counter) max_number from
(select drugName, count(*) counter from ChemoAdmin
group by ChemoAdmin) derived

if the max_number is very high and likely to be a variable then the best way is to use a stored procedure.

if it is just 2 or 3 then, the you can use the self left outer joins not a full outer join

for instance if the max_number is 2 then

select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate >= '2004-09-17'
and t1.stationDate < '2004-09-21'
and t2.stationDate >= '2004-09-17'
and t2.stationDate < '2004-09-21'
and t1.dose <> t2.dose
and t1.doseUnit <> t2.doseUnit

if the max_number is 3 then join the result again with the table and so on, but do not join too many times though because it would cause performance problems.|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see itI didn't pooh on anything. In my first post I just asked why you avoided a FULL join when I use them occaisionally.

The point that I raised in my second post is that if you look at things from the set based perspective, if either set it empty (returning NULL in SQL) then the WHERE clause comparison has to fail, reducing the result set to the intersection (an INNER join in SQL) or less.

Sorry if you got the impression that I was denigrating your work around for a full join. I didn't mean to do that at all.

I'm still not at all convinced that we understand what the original poster wanted. They showed us a third of the equation (the results they want), without giving us either the input data or the rules they used to determine the output. Before we debate the relative merits of solutions, we need to understand what the rules are!

-PatP|||I'm still not at all convinced that we understand what the original poster wanted. so true

but then, the most interesting threads are where the original poster asks something innocuous (to them) and we just run with it in all sorts of directions

:rolleyes: