|
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?
|
|
|
|
|
Look at the LIKE keyword...
here...[^]
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
If I use LIKE key word,it doesn't fully serve my purpose. This is my query:
irr::core::stringc test = "Select name from profile WHERE name LIKE '%";
test += str.trim();
test += "%'";
result.clear();
result = pManager->SQLdb.Query(test);
With this query, I can't add a new name "sri" to the name field if "srinivasan" is already present in the name field. I don't want this. If "srinivasan" is present, I could not add names like "srinivasan","SRINIVASAN" or "SrInIvAsAn". i.e I've to check the exact name without regard to case. How to do this?
Another question is:
I've to restrict the number of records in a table. i.e I don't want to update the table if it already has 10 records in it. How to do this?
modified on Thursday, October 28, 2010 6:29 AM
|
|
|
|
|