|
what database are you using...Selecting data out of a stored procedure isnt allowed by some servers, i'm pretty sure oracle doesnt or at least didnt allow that...Sql Server 2000 does..you also need a group by clause, which the server should have told you...you may want to test the query in a query window by itself before you put in into a sp, doing so always seems to make my life easier when i'm writing stored procedures.
|
|
|
|
|
I'm using a MS SQL 2000 database. I would like to return a different field than the one i'm doing the MIN on.
|
|
|
|
|
You'll probably have to return two fields with the second in a group by clause, or you could use a sub query
SELECT Tap_IsInPunch
FROM TimeAtendancePunches
WHERE Tap_IsInPunch IS NOT NULL
AND Tap_DateAndTime_DT = (SELECT MIN(Tap_DateAndTime_DT)
FROM TimeAtendancePunches
WHERE Tap_DateAndTime_DT >= @fromDateTime
AND Tap_DateAndTime_DT <= @toDateTime)
this way would not be the fastest, but it should work
SELECT Tap_IsInPunch, MIN(Tap_DateAndTime_DT)
FROM TimeAtendancePunches
WHERE Tap_IsInPunch IS NOT NULL
AND Tap_DateAndTime_DT >= @fromDateTime
AND Tap_DateAndTime_DT <= @toDateTime
GROUP BY Tap_IsInPunch
should also work and run faster you'll just have to have your code ignore the second column.
Hope this helps
|
|
|
|
|
Try something like this
SELECT <br />
Tap_IsInPunch<br />
FROM TimeAtendancePunches tp1<br />
WHERE Tap_IsInPunch IS NOT NULL<br />
and Tap_DateAndTime_DT = (<br />
SELECT <br />
MIN(Tap_DateAndTime_DT)<br />
FROM TimeAtendancePunches tp2<br />
WHERE Tap_DateAndTime_DT >= @fromDateTime AND <br />
Tap_DateAndTime_DT <= @toDateTime AND <br />
Tap_IsInPunch IS NOT NULL<br />
and tp2.Tap_IsInPunch = tp1.Tap_IsInPunch )
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you Ashfield
That works perfectly. What you did there is called an alias correct? I have added a join to this query
to check a value in a different table. Thanks again Ashfield
|
|
|
|
|
You are welcome. Some would call it an alias, personally I would call it a correlated sub query.
BTW, just be warned if using SQL Server 2000, ther is a bug that means if you nest more than 4 deep with sub queries it doesn't work - and before I get a load of comments to the effect that you should never nest sub queries that deep, it was an experiment to see if you could 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi.
I'm creating backup files of my database using SMO. The problem is that I can only store those files on the pc running the SQL Server.
Is there a way to get SMO to store the backup file on the pc running my SMO application?
Here is my code:
<br />
Backup backup = new Backup();<br />
backup.Devices.AddDevice("C:\\temp\\myBackup.bak", DeviceType.File);<br />
backup.Database = "MyDatabase";<br />
backup.SqlBackup(server);<br />
Thank you for any help!
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
You have to consider that running backup is not done by your PC, it is done by the server. So think like a server, you need to have your drive mapped to the server, when it looks at C: it is the SERVERS local drive.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That's Ok... My question was if somebody knows enough SMO to solve what I need...
The user may work on different pc's... I have to give him the possibility to create a backup file and store it on the pc where he is working (CD, USB drive,...) ...
Same way I need to send a backup file to the server from whichever pc in order to execute the backup.
Is it possible? Or is there any other solution?
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
AFAIK you are going to have to have a mapped drive from the server to the client, if the client changes you have a problem. Sorry, no solution from here.
Ahh
Have SQL create the backup to a shared server folder and then use a UNC path to copy to the local client using your UI. 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i am trying to add a out put parameter for parameter array like this
string SpName = "USP_SHIPPINGMETHOD";//store procedure name
OracleParameter[] param = {
new OracleParameter("STORESHIPPINGMETHODID",this.StoreShipId),
new OracleParameter("STOREID", this.StoreId),
new OracleParameter("PRICE",this.Price),
new OracleParameter("ONLINESHIPPINGMETHOD",this.OnLineShippingMethodID),
new OracleParameter("ACTIVE",this.Active),
new OracleParameter("PERCENT",this.percent),
new OracleParameter("SHIPPING_METHOD_NAME",this.ShippingMethodName),
new OracleParameter("METHOD_TYPE",this.MethodType),
new OracleParameter("CUSTOMCRITERIAID",this.CriteriaID),
new OracleParameter("QTY",this.Qty),
new OracleParameter("ADDITIONALCOST",this.AdditionalCost),
new OracleParameter("ADDITIONALQTY",this.AdditionalQty),
new OracleParameter("TEMPID",this.AdditionalQty).Direction=ParameterDirection.Output,
};
int r = OracleHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, SpName, param);
r = int.Parse(param["TEMPID"].Value.ToString());
Error Cannot implicitly convert type 'System.Data.ParameterDirection' to 'System.Data.OracleClient.OracleParameter' (For TempId);
thanks
sam 
Web Developer
|
|
|
|
|
Hi we have been running the DTS package for Direct Deposits, for a while now and suddenly is very slow and data is not updated on applications for users.We get data from ftp files to the tables.
Thank you
|
|
|
|
|
This is more of a comment than a question! Not a lot to go on is there? To help we need to know a bit about the dts package, the tables etc etc.
A guess? Try checking your error messages fromt the dts steps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I have a column that contains varchar values, the structure that the values are built is always the same, first six characters is a string prefix, anything after the prefix must be int or it is invalid.
What I want to do is subtring the column starting at character 7 for the rest of the value, but how do I validate if that value returned by the substring is int.
something like if value is int, is there a way that I can do this?
So lets say the column contains the following values:
abcdef123321
abcdef78998745
abcdef4568547
abcdef123548/
it should only return the first 3 rows because the fourth row is invalid because of the "/" at the end
Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
|
|
|
|
|
You can use the sql ISNUMERIC
declare @variable varchar(100)<br />
set @variable = 'abcdef78998745'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 1<br />
<br />
set @variable = 'abcdef4568547'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 1<br />
set @variable = 'abcdef123548/'<br />
select isnumeric(substring(@variable,7,datalength(@variable)-6))<br />
--returns 0 as not numeric
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I think this will do the trick, thanks a million.
No matter how long he who laughs last laughs, he who laughs first has a head start!
|
|
|
|
|
No problem
Bob
Ashfield Consultants Ltd
|
|
|
|
|
here it is
select *,substring(value,7,len(value)) as intValue from myTable<br />
where charindex('/',value) =0
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Dear Friends,
I m working on VC++ application using Oracle with ODatabase.
I m able to execute the query "SELECT SYSDATE FROM DUAL"...
but how to take this data in a Date variable in my application...
Can anybody help me out???
Megha
|
|
|
|
|
I've an MFC application which uses SQL connection using Namedpipes. I'm getting crash in the application log as follows.
Fault address: 7331293E 01:0000193E C:\bin\DBNMPNTW.DLL
Call stack:
Address Frame
7331293E 013D68DC ConnectionTransact+1AE
I'm not getting any clue of why the crash is happening at this point. After opening for a long time, it just crashes at this point !!!!!
Appreciate if some body can shed a light on the reason, what all could be the reason.
Arun K Ramachandran
|
|
|
|
|
I am trying to understand the impact of network traffic on data performance in my environment.
For example, if I am inserting 2 million rows into a database then how can I determine how much time is spent on network traffic? And how can I determine the savings of performing a batch update?
|
|
|
|
|
I don't know what to tell you about HOW to measure the network overhead as I am not a network professional, I imagine you'd have to inspect the packets going over the wire. But I CAN tell you that batches get exponentially faster than RBAR (row by agonizing row) transfers the more data you're working with. Imagine what the network overhead is for one round trip to your database. When you perform batches that overhead becomes a constant. But when you perform RBAR operations over the network that overhead is added for EVERY row. If you are working with that much data then it shouldn't even be a question of wither or not to batch update. Use BULK INSERT or SSIS/DTS but don't do it RBAR.
|
|
|
|
|
I have a stored procedure in oracle that looks like this:
<br />
PROCEDURE validate_master_account_id (v_master_account_id IN NUMBER, v_is_valid OUT NUMBER); <br />
And a procedure that looks like this:
<br />
public static bool TestProcedure()<br />
{<br />
string procedureName = "SMT.CPR_MASTER_ACCOUNT_PKG.validate_master_account_id";<br />
using (OracleConnection connection = new OracleConnection(AppSettings.ConnectionString))<br />
{<br />
connection.Open();<br />
<br />
using (OracleCommand command = new OracleCommand(procedureName, connection))<br />
{<br />
command.Parameters.Add("v_master_account_id", OracleType.Number).Value = 16793;<br />
command.Parameters.Add("v_is_valid", OracleType.Number).Direction = ParameterDirection.Output;<br />
<br />
command.ExecuteNonQuery();<br />
bool result = ((int) command.Parameters["v_is_valid"].Value) == 1;<br />
return result;<br />
}<br />
}<br />
}<br />
I get an error message that says "ORA-01036: illegal variable name/number" and I can't figure out why?
Thanks in advance.
|
|
|
|
|
|
Remember the error is coming from Oracle, not from C#.
Set the commandtype to stored procedure.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
