Question about SELECT

Suppose i create sp
CREATE PROC myProc @.criteria1 int,
@.criteria2 nvarchar(50) = ''
AS
SELECT * FROM myTable
WHERE column1 = @.criteria1
AND
column2 = @.criteria2
GO
my question is if parameter @.criteria2 is empty (equal '')
i want the select will ignore this criteria
so it perform like:
SELECT * FROM myTable
WHERE column1 = @.criteria1
the same with other criterias
Message posted via http://www.webservertalk.comE B via webservertalk.com wrote:
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
>
WHERE
(column1 = @.criteria1 OR @.criteria1 ='')
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Simply try this:
CREATE PROC myProc @.criteria1 int,
@.criteria2 nvarchar(50) = ''
AS
Begin
If @.criteria2 = ''
SELECT * FROM myTable
WHERE column1 = @.criteria1
Else
SELECT * FROM myTable
WHERE column1 = @.criteria1
AND column2 = @.criteria2
End
Gert-Jan
"E B via webservertalk.com" wrote:
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> --
> the same with other criterias
> --
> Message posted via http://www.webservertalk.com|||however if i have suppose 20 paramaters, so i need to write a lot of SELECT
...., because it's a lot of combination.
Any ideas?
Message posted via http://www.webservertalk.com|||This should do. However, for some reason, this query runs very slowly.
SELECT *
FROM myTable
WHERE column1 = @.criteria1
AND column2 = ISNULL(@.criteria2,Column2)
Gopi
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:912aa0dbc4fb4c68b84db0a061654c2d@.SQ
webservertalk.com...
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> --
> the same with other criterias
> --
> Message posted via http://www.webservertalk.com|||Thanks u. u r the best
Message posted via http://www.webservertalk.com

Question about Neural Networks

I created a test table (name - "Nset") with the columns:
id (int), n1 (float), n2 (float), n3 (float) and c1 (varchar).
Then filled a table the followings information:
id n1 n2 n3 c1

1 0,1 0,1 0,6 one

2 0,2 0,1 0,5 one

3 0,7 0,5 0,1 two

4 0,4 0,9 0,3 two

5 0,5 0,1 0,5 three

And created a neural network with tuning by default. "id"-field is the key. n1, n2 and n3 are inputs. c1 - predict.

Then i tryed predict query, like:

SELECT

PREDICT([Nset].[c1])

FROM

[Nset]

NATURAL PREDICTION JOIN

(SELECT 0,5 AS [n1], 0,1 AS [n2], 0,5 AS [n3]) AS t

The result is "three". This is correct. And some other tests appeared correct.

But, when I filled the column c1 with numerical values (one = 1, two=2, three=3) and changed type to int, a predict query left off to work correctly.

Previous query return 4.

And other tests showed that a value returned large on unit.

Is this correct?

Thanks.

Hello

It can be explained. The algorithm actually works correctly, the models are different.

Here is what happens:

first case: "one", "two", "three" are strings, so the mining structure / model creation wizard decides that they should be treated as discrete. The net is trained to fit particular states of a distinct attribute (c1). State "three" is only described by one training point (the last row) so the weights of the network are optimized (actually, over-trained) to predict "three" for the combination of inputs that appears in the last row. This is why the prediction result is "three"

second case: 1, 2 and 3 are this time numbers. The wizard decides that, as numeric values, they should be treated as Continuous. You can change this behavior (the wizard only makes a suggestion) in one of the wizard pages. As the target is now a continuous variable, the network is not optimized independently for the distinct state '3'. It actually is optimized for the whole surface defined by the training points.

You can fix it immediately by changing, in the wizard, the content type for the target column, c1, from Continuous to Discrete.

The differences between results should reduce as more training cases are presented to the network

Hope this clarifies the issue

|||Thanks! Now working properly!|||

And how it is better to work with variable neurons number?

It is similar to the previous case, but the table in a following kind:

case_id

input_name

input_weight

output_val

1

n1

0,1

1

1

n2

0,1

1

1

n3

0,6

1

2

n1

0,2

1

2

n2

0,1

1

2

n3

0,5

1

3

n1

0,7

2

3

n2

0,5

2

3

n3

0,1

2

4

n1

0,4

2

4

n2

0,9

2

4

n3

0,3

2

5

n1

0,5

3

5

n2

0,1

3

5

n3

0,5

3

I can't use PIVOT of SQL Server, because i don't know neurons count...

I suppose, that i need to build the cube. And after that to build Neural Network model.

It is correct? There Is any other way?

|||

You could model like below:

case_id n1 n2 n3 output

1 0.1 0.1 0.6 1

2 ...

Alternatively, you could use the nested table feature and create a model like below. This would work if you do not know the number of inputs before modeling

(
Case_ID long key,
Output long continuous predict,
inputs Table
(
neuron text key,
weight double continuous
)
)

Assuming that you have a data source view containing the table above, you can train the mining structure/model self-joining the table with itself (using Case_ID as Key and then as Foreign Key).

In the wizard, you can specify the same table as both Case and Nested as long as you define a one-to-many reflexive relationship from case_id to case_id.

|||

Thank you for help again!

All works except for a Neural Network Viewer.

The NN Viewer gives out a message:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format..."

But it is not important. Prediction query works correct.

Once again thanks!

|||

Glad to hear it works

> All works except for a Neural Network Viewer

Some viewers problems were fixed in SP2 of SQL Server 2005, which should be available publicly soon

Question about Neural Networks

I created a test table (name - "Nset") with the columns:
id (int), n1 (float), n2 (float), n3 (float) and c1 (varchar).
Then filled a table the followings information:
id n1 n2 n3 c1

1 0,1 0,1 0,6 one

2 0,2 0,1 0,5 one

3 0,7 0,5 0,1 two

4 0,4 0,9 0,3 two

5 0,5 0,1 0,5 three

And created a neural network with tuning by default. "id"-field is the key. n1, n2 and n3 are inputs. c1 - predict.

Then i tryed predict query, like:

SELECT

PREDICT([Nset].[c1])

FROM

[Nset]

NATURAL PREDICTION JOIN

(SELECT 0,5 AS [n1], 0,1 AS [n2], 0,5 AS [n3]) AS t

The result is "three". This is correct. And some other tests appeared correct.

But, when I filled the column c1 with numerical values (one = 1, two=2, three=3) and changed type to int, a predict query left off to work correctly.

Previous query return 4.

And other tests showed that a value returned large on unit.

Is this correct?

Thanks.

Hello

It can be explained. The algorithm actually works correctly, the models are different.

Here is what happens:

first case: "one", "two", "three" are strings, so the mining structure / model creation wizard decides that they should be treated as discrete. The net is trained to fit particular states of a distinct attribute (c1). State "three" is only described by one training point (the last row) so the weights of the network are optimized (actually, over-trained) to predict "three" for the combination of inputs that appears in the last row. This is why the prediction result is "three"

second case: 1, 2 and 3 are this time numbers. The wizard decides that, as numeric values, they should be treated as Continuous. You can change this behavior (the wizard only makes a suggestion) in one of the wizard pages. As the target is now a continuous variable, the network is not optimized independently for the distinct state '3'. It actually is optimized for the whole surface defined by the training points.

You can fix it immediately by changing, in the wizard, the content type for the target column, c1, from Continuous to Discrete.

The differences between results should reduce as more training cases are presented to the network

Hope this clarifies the issue

|||Thanks! Now working properly!|||

And how it is better to work with variable neurons number?

It is similar to the previous case, but the table in a following kind:

case_id

input_name

input_weight

output_val

1

n1

0,1

1

1

n2

0,1

1

1

n3

0,6

1

2

n1

0,2

1

2

n2

0,1

1

2

n3

0,5

1

3

n1

0,7

2

3

n2

0,5

2

3

n3

0,1

2

4

n1

0,4

2

4

n2

0,9

2

4

n3

0,3

2

5

n1

0,5

3

5

n2

0,1

3

5

n3

0,5

3

I can't use PIVOT of SQL Server, because i don't know neurons count...

I suppose, that i need to build the cube. And after that to build Neural Network model.

It is correct? There Is any other way?

|||

You could model like below:

case_id n1 n2 n3 output

1 0.1 0.1 0.6 1

2 ...

Alternatively, you could use the nested table feature and create a model like below. This would work if you do not know the number of inputs before modeling

(
Case_ID long key,
Output long continuous predict,
inputs Table
(
neuron text key,
weight double continuous
)
)

Assuming that you have a data source view containing the table above, you can train the mining structure/model self-joining the table with itself (using Case_ID as Key and then as Foreign Key).

In the wizard, you can specify the same table as both Case and Nested as long as you define a one-to-many reflexive relationship from case_id to case_id.

|||

Thank you for help again!

All works except for a Neural Network Viewer.

The NN Viewer gives out a message:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format..."

But it is not important. Prediction query works correct.

Once again thanks!

|||

Glad to hear it works

> All works except for a Neural Network Viewer

Some viewers problems were fixed in SP2 of SQL Server 2005, which should be available publicly soon

question about like

Hello, i'm just wondering if i have for example a table with 2 colummns the first one is some primary key (int) and the other is of the image data type.
For example one row would be:

key data
232 0x7A5B6789D4E2...(thats just how sql server 2005 express shows me the data when i make a query insede Management Studio Express)

now i'm wondering if it is possible to do query for lets say %89D4% with LIKE,(i kinda want to search for a certain pattern of bits or in this case hexadecimal values ) i've tryed but i get an error. I just want to know if it is possible to do this with like and how and if it isn't, what other options do i have (full-text search - ?)?Well, the problem is that you are looking at binary data, not string data... Also, know that the image datatype is deprecated and should be replaced with varbinary(max).

What was the error you received?|||i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...|||

FitzFarseer wrote:

i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...

You need to surround your like statements with quotes...

select * from table where field like '%xxx%'|||select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.|||For your reading pleasure, and for reference:

MSDN Transact-SQL Reference for: LIKE

http://msdn2.microsoft.com/en-us/library/ms179859.aspx|||

FitzFarseer wrote:

select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.

I think this is a result of trying to use like on a binary column. Syntactically, it is correct.sql

Question about JOIN syntax

what is the syntax to join a table with the result of antoher query.
For example i have two tables
Create Table Customers (
CustomerID int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
PaymentDate Datetime )
What query will bring me the customers whose lastpaymentdate in the
customers table is not correct.
That can only be checked by comparing it with the max paymentdate for each
customer in the payments table.
I want this to be done by ansi standard sql. Not using any specific feature
of sql server.
thxSELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate)
FROM Payments AS P
WHERE P.customerid=C.customerid)
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2vydnRekZvROvmiiRVn-sw@.giganews.com...
> SELECT *
> FROM Customers AS C
> WHERE lastpaymentdate <>
> (SELECT MAX(paymentdate)
> FROM Payments AS P
> WHERE P.customerid=C.customerid)
>
oh may be my example was not the best!
what if I have
Create Table Customers (
CustomerID int,
LastPaymentAmount int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )
and I want to see if the LastPaymentAmount field holds correct values.
I couldnt use the max function for this obviously.
thx|||> I couldnt use the max function for this obviously.
Why not? My query should still work.
If you want a query that uses a join then try this:
SELECT C.*
FROM Customers AS C
LEFT JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL
This has a possible advantage over the correlated subquery version. It will
include Customers who have a Lastpaymentdate but don't have any non-NULL
dates in the Payments table.
--
David Portas
--
Please reply only to the newsgroup
--|||Oops, you wanted the *correct* values:
SELECT C.*
FROM Customers AS C
JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
--
David Portas
--
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:4eOdnfkpJ63Bqmii4p2dnA@.giganews.com...
> Oops, you wanted the *correct* values:
> SELECT C.*
> FROM Customers AS C
> JOIN
> (SELECT customerid, MAX(paymentdate) AS lastpaymentdate
> FROM Payments
> GROUP BY customerid) AS P
> ON C.customerid=P.customerid
> AND C.lastpaymentdate=P.lastpaymentdate
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
I want the LastPaymentAmount of Customers table to be compaired with the
Amount of the last payment in Payments table.
Ofcourse the last payment is the one with the maximum PaymentDate but the
Amount fields should be compared in both tables to see if they are equal.|||You didn't include any keys or constraints with your DDL but I'll have to
assume that (customerid, paymentdate) is unique in Payments - otherwise how
will you define which payment is the latest?
SELECT C.customerid, C.lastpaymentamount, C.lastpaymentdate
FROM
(SELECT customerid,
MAX(paymentdate) AS paymentdate
FROM Payments
GROUP BY customerid) AS P1
JOIN Payments AS P2
ON P1.customerid = P2.customerid
AND P1.paymentdate = P2.paymentdate
JOIN Customers AS C
ON P2.customerid = C.customerid
AND P2.paymentdate = C.lastpaymentdate
AND P2.amount = C.lastpaymentamount
--
David Portas
--
Please reply only to the newsgroup
--|||>> what query will bring me the customers whose last_payment_date in
the
customers table is not correct. <<
Your design is wrong; this is a computed value and you do not store
computed values in an RDBMS. Your Customers table should not exist at
all! And you do not have keys or anything that makes payments into a
table. Let's get the basics right:
CREATE TABLE Payments
(payment_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers
ON DELETE CASCADE
ON UPDATE CASCADE,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);
Now create a VIEW which will always be correct:
CREATE VIEW CurrentPayments (payment_id, customer_id, payment_date)
AS
SELECT payment_id, customer_id, payment_date
FROM Payments AS P1
WHERE P1.payment_date
= (SELECT MAX(payment_date)
FROM Payments AS P2
WHERE P1.customer_id = P2.customer_id);|||--CELKO-- (joe.celko@.northface.edu) writes:
> Your design is wrong; this is a computed value and you do not store
> computed values in an RDBMS.
Maybe in some small ideal model of a database you don't.
In real-world databases, you often find that you can save a lot of
cycles by storing computed data. For instance, if you have a transactions
table for a bank account, you probably want to store the balance on the
account after each transaction, because you will have several functions
in you system where you display this information. Having it computed
means that this function will run faster, and that the likelyhood that
they will show the wrong value because of bugs is less.
The one problem with storing computed values, is that if an underlying
value changes, the computed value changes too. But in the example I
just gave, this never happens due to business rules.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||>> this is a computed value and you do not store computed values in an
RDBMS. <<
May I know why so? Snapshots and stored joins, as recommended by the
relational model are typical examples where computed values are stored in an
RDBMS.
In SQL Server, you can materialize computed columns and views by physically
clustering them and I don't see why this should not be done, when required.
--
- Anith
( Please reply to newsgroups only )

Question about GroupBY and Aggregate functions.

Ok I know this is totally a noob question but I'm working with a table
of the following structure
ID (int), FK (int), Date (datetime)
I would like to select the ID & Max(Date) grouped by the FK. I don't
want to group by the ID but I want it included in the result set.
If I do a simple select like
select ID, FK, Max(Date) from tbl group by FK, ID
I get a result set that includes discreet dates for each ID, not the
max date for a given FK having this ID.
for example
ID FK Date
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
w/ the previous sql returns
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
What I would want is something like this
2 100 1/2/2006
4 150 1/2/2006
Thanks in advance.
S*untested*
select t1.ID,t1.FK,t1.Date
from tbl t1
inner join(
select FK, Max(Date)
from tbl
group by FK ) t2(FK,Date) on t2.FK=t1.FK and t2.Date=t1.Date
Note that if there are multiple IDs sharing the same
maximum date, the query will return both IDs.|||Thanks, I think that works, but is there any way to filter duplicate
dates? Perhaps a distinct on the subquery?
Also is there any way to do it without performing a date comparison?
That's what makes this particular solution work, but wouldn't that be
fairly costly from a resource perspective?|||
> Thanks, I think that works, but is there any way to filter duplicate
> dates? Perhaps a distinct on the subquery?
Not sure I understand your question. If your sample expected
results aren't what you really want (because they share
the same date), can you post some more information.

> Also is there any way to do it without performing a date comparison?
> That's what makes this particular solution work, but wouldn't that be
> fairly costly from a resource perspective?
You can't get away from doing some sort of date comparison
here since your requirements are based on the maximum date.

Question about converting bigint field to int field

We made a poor decision a long time ago when designing our database
structure. We used bigint data types as the identity keys for many of
our base tables. For many reasons I would like to change these fields
to int at the largest. The largest data in these fields is around
200,000. I know that int can easily store this.

What should I be worried about when changing these fields from bigint
to int? If anything. Your help is appreciated. I did several
searches without much luck.I think you've covered the one biggy - make sure your existing data will
fit!

Others...

a) Make sure anything you join with are the same type, basically make
sure you change it everywhere including your foreign keys table.
b) Remember to do the stored procedures, udfs, triggers that may use them
as parameter.
c) You'll need to drop any constraints on your column definied with the
identity property, see example problem...

drop table t

create table t (
mycol bigint identity primary key,
t char(1) )

insert t ( t) values( 'a' )

alter table t alter column mycol int not null

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<mamorgan1@.gmail.com> wrote in message
news:1137768775.133857.115810@.g44g2000cwa.googlegr oups.com...
> We made a poor decision a long time ago when designing our database
> structure. We used bigint data types as the identity keys for many of
> our base tables. For many reasons I would like to change these fields
> to int at the largest. The largest data in these fields is around
> 200,000. I know that int can easily store this.
> What should I be worried about when changing these fields from bigint
> to int? If anything. Your help is appreciated. I did several
> searches without much luck.|||Just curious... What problems are there with having bigint as an
identity column?|||There aren't any problems - it works just fine.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"pb648174" <google@.webpaul.net> wrote in message
news:1138052526.544803.80680@.z14g2000cwz.googlegro ups.com...
> Just curious... What problems are there with having bigint as an
> identity column?|||I think its because
Bigint takes 8 bytes storage and Int takes 4 bytes.
SQL Server will not automatically promote other integer data types
(tinyint, smallint, and int) to bigint.

Regards
Amish Shah|||> What problems are there with having bigint as an identity column?

extra storage space causing slower performance of everything