|
what error are you getting ?
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
What happens when you remove the ORDER BY ?
|
|
|
|
|
The syntax works, but I have 3 disjoint selections as a result. I am looking to save one of the pieces of data in each row to a variable in VB.NET, if you can tell me how to do that without the union of the 3 results then that is fine. At the moment I don't know how
|
|
|
|
|
The UNION concatenates the results, but you can use this result as if it were a query again, e.g.;
SELECT * FROM
(
SELECT TOP 1 [name] FROM sysobjects WHERE [name] LIKE ('sysh%')
UNION
SELECT TOP 2 [name] FROM sysobjects WHERE [name] LIKE ('sys%')
UNION
SELECT TOP 1 [name] FROM sysobjects WHERE [id] < 0
) AS myCTE ORDER BY [name]
|
|
|
|
|
You are specifying ORDER BY too many times. You do not need to specify ORDER BY for each of the sub-queries; you only need to specify it once for the entire UNION result set.
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith'
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
union
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc
|
|
|
|
|
For Joe Smith, I have multiple rows that I want only the row that has both the name (WHERE TEMPLATEOWNER = 'Joe Smith') AND the row where the LASTCHANGE is the newest. So that first select should return the newest entry by Joe Smith. Then the second and third selects should find the one row that is the most recently updated by Jane and GI.
The way it stands now, query analyzer shows three disjoint results. I need a joint record set. The removal of the order by in each select breaks the function.
|
|
|
|
|
Try this. It should do exactly what you want
Select * from (
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Joe Smith'
order by lastchange desc
) d
union
Select * from (
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'Jane Smith'
order by lastchange desc
) d
union
Select * from (
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE TEMPLATEOWNER = 'GI Jane'
order by lastchange desc
) d
Wout Louwers
|
|
|
|
|
just remove column name
in order by clause simly place column index .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
hi...
i m new to postgresql and i have work with mssql,oracle n mysql.
can any body tell me how to make stored procedure and where to put in postgresql?give me other options if available.
thnxs in advance.
!- F - R - I - E - N - D - S -!
modified on Tuesday, July 28, 2009 4:41 AM
|
|
|
|
|
|
See ankur,
there are no stroed procedure in postgresql...
I have search all the topics on postgresql and i found nothig about stroed porcedure in postgresql...
But you can make your own functions and triggers...
Ok, Bye......
Hope you are satisfied with this answer.
|
|
|
|
|
Hi To All....!
I have two stored procedure 1)sp_User_Master 2)sp_User_Detail. When I save data it first execute sp_User_Master stored procedure and then sp_User_Detail. Now these are two diffrent stored procedure and are executed one after another.
My Problem is when an error occurs in sp_User_Detail stored procedure i am unable to rollback the data saved using sp_User_Master. So is it possible to rollback the data saved by first executed stored procedure.
Please help.
Thanks to all.
.
|
|
|
|
|
your code look like this:
try
{
begin tran
exec sp_User_Master
exec sp_User_Detail
commit tran
}
catch (Execption objException)
{
rollback transaction
}
|
|
|
|
|
1. What database are you using?
2. What programming language are you using?
|
|
|
|
|
I am Using SQL SERVER 2005 and C SHARP.NET
.
|
|
|
|
|
Use a controller procedure and structured error handling technique.
The controller procedure will call the 2 procedures for data insertion and with structured error handling you can eassily RollBack the transaction if any of the procedure fails...
|
|
|
|
|
use this,
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "Your_SP";
cmd.CommandType = CommandType.StoredProcedure;
if (con.State != ConnectionState.Open)
con.Open();
cmd.Transaction = con.BeginTransaction();
try
{
parentmainid=Subroutine_For_Parent_Insert(userid);
for (items = 0; items <= _alldata.Count - 1; items++)
{
Subroutine_For_Child_Insert(parentmainid,childdata);
}
cmd.Transaction.Commit();
_status = true;
}
catch (Exception e)
{
cmd.Transaction.Rollback();
_status = false;
}
Some important points :
1>Remember to declare cmd as global or pass it as byreference.
2> Do not reinitialze the connection in any of the functions.
3> Use cmd.Parameters.clear() on every function call or else you would get a "Too many parameters error".
4> Do not reinitialize the cmd object during the transaction or else the transaction will lose the data and won`t be rolled back.
When you fail to plan, you are planning to fail.
|
|
|
|
|
If one has to select about 30-100 different rows from a table based on their ids...
which one is best approach:
1.
select * from table where id1=3 or id1=9 or id1=200 or id1=205 ...
or
2. using a loop... eg
foreach(string id in ids)
{
stmt="select * from table where id3="+id
}
thankyou for guidance...
|
|
|
|
|
Generally speaking, the fewest trips between the database client and the server, the better the application will perform. This is especially true for queries that return small amounts of data. For this reason, option #1 is more likely to perform better.
Adam Maras | Software Developer
Microsoft Certified Professional Developer
|
|
|
|
|
|
Does MySql not have an IN operation, I hate using OR in a where clause.
Select * from Table where ID in(1,23,4,56,700)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Based on the 2 options that you gave, the first option is better.
|
|
|
|
|
So I am working on this SP for my DB at work:
CREATE PROCEDURE dbo.sp_CreateChecklistForPackage
(
@PACKAGE varchar(255)
)
AS
INSERT INTO TBL_SWBOM_CHECKLISTS
(
PACKAGE,
TEMPLATEOWNER,
STEPNUMBER,
STEPNAME,
DESCRIPTION,
LASTCHANGE,
LASTCHANGEBY
)
select
@PACKAGE,
TEMPLATEOWNER,
STEPNUMBER,
STEPNAME,
DESCRIPTION,
LASTCHANGE,
LASTCHANGEBY
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
GO
What I need it to do is find the newest entry for each of the three users (specific names that I can test against, like "TEMPLATEOWNER='Joe Smith') and put only that template into the SWBOM_CHECKLISTS table only. So the TBL_SWBOM_CHECKLISTS table should have one entry per package made up of three.
So basically I need it to find the newest entry from each of the three user's separate entries and then put that into the other table. I think it's fairly simple and doable, just not 100% sure.
|
|
|
|
|
Well, it is easy enough to find the newest entry for a given owner in your setup:
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE LASTCHANGEBY = 'Joe Smith'
ORDER BY LASTCHANGE DESC
Wrap something like that in your INSERT and you should be good to go, at least for the first of your three records.
|
|
|
|