|
Hello,
I have the following procedure
CREATE PROCEDURE tryproc AS
declare @first int;
select sum(t) as first from demand where product='LPG' AND LOCATION = 'Mumbai';
create table abc (demandsum int);
insert into abc (demandsum) values (@first);
In the above procedure the value inserted in te table is NULL. I want the sum got from the table to be inserted in the table abc.
What must be the problem?
Prithaa
|
|
|
|
|
prithaa wrote: What must be the problem?
Problem is you are not assigning the variable value. See this and rewrite it
CREATE PROCEDURE tryproc
AS
DECLARE @first INT
SET @first = ( SELECT SUM(t) AS first
FROM demand
WHERE product = 'LPG'
AND LOCATION = 'Mumbai' )
CREATE TABLE abc (demandsum INT)
INSERT INTO abc (demandsum) VALUES (@first) Hope it helps
|
|
|
|
|
Hi,
you can use this query ...
however you can reduce the no. of DML stm..
SELECT dem.first INTO abc FROM
(SELECT SUM(t) AS first FROM demand WHERE product = 'LPG' AND LOCATION = 'Mumbai' ) dem
|
|
|
|
|
I wanted a code which would connect with the MS Access database.
|
|
|
|
|
what is your front-end ?
|
|
|
|
|
my front end will be asp.net.
|
|
|
|
|
|
Hello Experts,
Suppose I have three fields in table named ID,Order Date and Order NO.
I have to fetch the max order no along with their Id and date. actually I have more than 1000 records on a particular date. So I have to fetch max order no and max order date.....
Please help
Regards
Pankaj Joshi
If you want to shape your dreams into reality, please wake-up...
|
|
|
|
|
try like:
select max(OrderNo), max(orderDate) from <tablename>
|
|
|
|
|
That is not working properly. I want to know max order date first and
after that what is the max order no on that particular date.
Please help.
Regards
Pankaj Joshi
If you want to shape your dreams into reality, please wake-up...
|
|
|
|
|
Hi,
Use this query...
select id ,ord_num, ord_Date
from <table_name>
where ord_num = (select max(ord_num) from <table_name> where ord_Date =(select max(ord_Date) from <table_name>))
|
|
|
|
|
Thank you for reply.
Regards
Pankaj Joshi
If you want to shape your dreams into reality, please wake-up...
|
|
|
|
|
I know this question doesn't really fit here, and should probably be asked of some db admin types on another site, but I don't know of any such sites/people, so I thought I'd give you guys a shot...
I have an ASP.Net web service backed by SQL Server 2005 Standard happily running on a server hosted in or near Canary Warf. It's been up for 6 Months, with no down time except for scheduled maintenance. However, I've finally gotten funding to do what I wanted to do in the first place, and replicate the whole she-bang in a completely different part of the country, just so that when the inevitable happens (such as a builder cutting through the internet pipe at the host) the service can continue running with the minimum of fuss.
Whilst I can quite easily manage keeping the ASP.Net side of things in sync (unless anyone knows how to have IIS do this for me?), keeping the two databases synchronized might be more of a challenge.
Now I understand SQL Server has all manner of features that can do this kind of thing, but I'm googling around without really knowing what to google for. Database Mirroring keeps coming up, but I'm not sure if this is what I want - is it? Or is replication what I should be looking for (having read about the publisher/subscriber model, I'm unclear how we'd be able to get stuff back from the backup database on to the primary, once the primary has recovered)?
Cheers,
Martin.
"On one of my cards it said I had to find temperatures lower than -8. The numbers I uncovered were -6 and -7 so I thought I had won, and so did the woman in the shop. But when she scanned the card the machine said I hadn't.
"I phoned Camelot and they fobbed me off with some story that -6 is higher - not lower - than -8 but I'm not having it."
-Tina Farrell, a 23 year old thicky from Levenshulme, Manchester.
|
|
|
|
|
Yup - mirroring is what you want. This[^] article is a good start.
|
|
|
|
|
Cool - cheers!
"On one of my cards it said I had to find temperatures lower than -8. The numbers I uncovered were -6 and -7 so I thought I had won, and so did the woman in the shop. But when she scanned the card the machine said I hadn't.
"I phoned Camelot and they fobbed me off with some story that -6 is higher - not lower - than -8 but I'm not having it."
-Tina Farrell, a 23 year old thicky from Levenshulme, Manchester.
|
|
|
|
|
INSERT INTO `pic_newsletter_email_addr` VALUES ('', 'foo@foo.com')
It's a string sent into this function:
<br />
Public Function query(ByVal query_str As String)<br />
Dim query_ole As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand(query_str, conn)<br />
Return query_ole.ExecuteReader()<br />
End Function<br />
I am connected to the Access database. The error I get is "Data Type Mismatch in criteria expression"
*Edit*
The first field is an auto number and the second field is a text field with a length of 255
|
|
|
|
|
It's telling you that at least one of your parameters is the wrong type. I would suspect that it's the first one - I'm guessing that you are using an autoincrementing column here. If so, you should really write your query out with an explicit definition as in
INSERT INTO pic_newsletter_email_addr (column2) VALUES ('foo@foo.com');
|
|
|
|
|
|
How cool that I could guess this without even seeing the code - not that I've ever hit this problem before.
|
|
|
|
|
I want to take an IP address e.g. 111.222.333.444 and convert it to either a float or an unsigned datatype.
Thanks,
|
|
|
|
|
Why? An IP address is really a string of numbers - not a number. Removing the dots won't help because you really don't know where to put them back. I suppose you could always store it as four numbers, but it still doesn't gain you much.
|
|
|
|
|
The reason is for faster search indexing. I found this after I posted;
Create FUNCTION [dbo].[Dot2LongIP]( @IP VarChar(15) )
RETURNS BigInt
AS
BEGIN
DECLARE @ipA BigInt,
@ipB Int,
@ipC Int,
@ipD Int,
@ipI BigInt
SELECT @ipA = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipA) - 1 )
SELECT @ipB = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipB) - 1 )
SELECT @ipC = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipC) - 1 )
SELECT @ipD = @ip
RETURN ( @ipA * 256*256*256 ) + ( @ipB * 256*256 ) + ( @ipC * 256 ) + @ipD
END
RETURN @ipI
END
This does speed it up and seems to meet my needs.
Thanks,
|
|
|
|
|
hi
i have table that have id file, and i want that only one value will
apir in arecord.
my table
========
123
333
444
123
444
123
what i need
===========
123
333
444
thank's alot
|
|
|
|
|
i work in access 2007
name of the table: men
name of the fild : id
|
|
|
|
|
Are the rest of the values on the row different like this? Are you just trying to get a list of DISTINCT ColumnA values?
ColumnA ColumnB
-----------------
123 ABC
222 ABD
123 ABF
222 SDS
|
|
|
|