|
I think you have to pass like this for insert
execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200
First Parameter should be NULL
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
thanks a lot. if I insert your code absolutely I don't have any problem with inserting.
execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200
the issue is when I am doing update who can I send parameter.
for instance, I inserted 20 record by above code. now, I wanna update one of those record who have user entered id.
Update Person_prs
Set
--Id = @id,
fname_prs = @fname,
srname_prs = @srname,
gender_prs = @gender,
dob_prs = @dob,
transactionid_prs = @transactionid
where id_prs = ????????????????????????????????????????????????????????
|
|
|
|
|
I got it.its ok
thanks a lot for your help
|
|
|
|
|
Hi,
I tried this code to get the record Identity with C# Windows Form application bu tit's not working.. can any one help please...
CREATE OR REPLACE FUNCTION fn_create_resume(resume_name character)
RETURNS int AS
$$
DECLARE
record_identity int;
BEGIN
insert into resume_details (resume_id, resume_name) values (default, resume_name);
return resume_id;
END;
$$ LANGUAGE plpgsql;
|
|
|
|
|
Try the following
BEGIN
insert into resume_details (resume_id, resume_name) values (default, resume_name);
select resume_id into record_identity from resume_details where resume_name = resume_name;
return record_identity;
END;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
|
I have the following data returned buy a simple SQL query
| Site | X | Y | Z |
--------------------
A 1 2 3
B 4 5 6
C 7 8 9
I need to get it to the following format
| A | B | C |
--------------
1 4 7
2 5 8
3 6 9
Any ideas / code snippits most welcome
Mark
|
|
|
|
|
|
I have done that!
Can't work it out tho, as all the example sum values and I dont want to do that.
TSQL is not my strong point
|
|
|
|
|
Well Here[^] is an example on Pivot two or more columns. Read through it and try out.
|
|
|
|
|
Do I understand that both your sites (A,B,C) and your attributes (X,Y,Z) are variable in number ? And you are counting some sort of occurrence ?
My pivot tables show the months across the top, the salesman down the left and the number in the grid represents the number of orders closed in that month. Sound familiar ?
Your Pivot might look something like this:
select Salesman, [1] as Jan,[2] Feb,[3] Mar,[4] Apr,[5] May,[6] Jun,<br />
[7] Jul, [8] Aug, [9] Sep, [10] Oct, [11] Nov, [12] Dec<br />
from (<br />
select salesman, OrderID, datepart(month,order_date) as month<br />
from orders<br />
where datepart(year,order_date) = 2010)<br />
AS SourceTable<br />
PIVOT (COUNT(OrderID) FOR month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
|
|
|
|
|
Thanks for your reply, I will have a look at your code now.
My sites are a variable, but X,Y,Z are fixed (they are different types of accidents, and the data stored represents the number of occurances)
Just incase anybody want to have a go here is a create script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[X] [int] NOT NULL,
[Y] [int] NOT NULL,
[Z] [int] NOT NULL
) ON [PRIMARY]
insert into Example(Site, X,Y,Z) Values ('A',1,2,3)
insert into Example(Site, X,Y,Z) Values ('B',4,5,6)
insert into Example(Site, X,Y,Z) Values ('C',7,8,9)
select * from Example
|
|
|
|
|
This article [^]may be useful, I use MAX (min will do just as well) for the aggregator.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think the main problem is I need multiple aggregates (for X, Y and Z).
I have this so far
select *
from Example
pivot
(
Max(X)
for site in ([A],[B],[C])
) as p
|
|
|
|
|
Not sure I understand your problem, are you saying you want 3 aggregates(X,Y,Z) for each type(columns) (A,B,C)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think thats what I need, but not sure.
Basically I want to rotate the returned data by 90 degrees
|
|
|
|
|
You need to unpivot first, or normalize it actually, into something similar to this format:
CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Accident] [nvarchar] NOT NULL,
[Occations] [int] NOT NULL,
) ON [PRIMARY]
Either in the query or a temp table, but preferably it should be stored normalized in the database
After that you can make the pivot the normal way
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thanks, you were right
I now have it working.
|
|
|
|
|
You should consider normalizing the table. It would make it a lot easier in the future to add functionality.
Consider this:
CREATE TABLE Incidents (
Site varchar
IncidentType varchar,
IncidentDate Date,
Incidentinfo varchar,
...
) Then just make your pivot on Select site,incidenttype,count(*) as incidentcount from incidents
You may also exchange site and incidenttype for IDs referencing tables holding info on sites and incidenttypes.
If you add a site to your organisation you simply add a row in a table.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
mark_w_ wrote: I think thats what I need, but not sure.
In that case work through the article because that is EXACTLY what it does with 2 columns, just extend it to meet your requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to zip file with xp_cmdshell in sql server 2005
|
|
|
|
|
you can't personally I would use a cmd file that runs a winzip commandline application
Winzip[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Simon_Whale wrote: you can't
Why not?
If it runs an excutable, per your suggestion, then why would it not run what is already in windows?
|
|
|
|
|
tan873 wrote: how to zip file with xp_cmdshell in sql server 2005
I suppose that depends on exactly what you mean.
First step how would you do it in a command shell?
Windows has the 'compact' command built in.
Comand line options are documented in the help for that as well as at the following.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/compact.mspx?mfr=true[^]
If you want to use something besides 'compact' then you must explore the command line options for that tool.
Once you know how to do it from a regular shell then you explore the options with the shell in sql server.
|
|
|
|
|
Hi!
I've to check the database for existency. i.e. My table has a field called name. I've to check whether a particular string is present in the field. e.g I've to check whether "ratha" is prsent in name. I'm using SQLite. How to do this? Which Query should I use?
|
|
|
|
|