|
Hi all i hav a query for which i got the result but i am not able to understand the query....
here's the query...
select sal,to_char((to_date(sal,'j')),'jsp') from emp;
Result: 3250 Three Thousand Two Hundred and Fifty.
I just need the explanation of to_date and to_char command formats.....
if possible can any one giv one example each on those two commands..
Thanks in Advance...
Byee
|
|
|
|
|
TO_CHAR and TO_DATE are built-in functions in Oracle.
TO_CHAR = Convert number to string
TO_DATE = Convert string to date
|
|
|
|
|
i got ur point but how the given value is getting converted into words....
|
|
|
|
|
|
Hi, I'm new to SSIS and am trying to create a project that uses a Flat File as the source file and extract data from a column (containing ReportDate) and use this parameter to set the Where clause in a stored proc to delete records from a SQL Server table. I'm sure this can be done, just don't know how.
Thanks in advance for any help you might provide.
|
|
|
|
|
Delete From TableName Where CoulmnName = @Parameter
|
|
|
|
|
Hi,
I have a stored proc which retrieves a value from my database. If I select the value I then want to do a case statement and return a string. This is my code but I can't get it to work:
create procedure procGetEventType
(@EventID int, @EventHeading varchar(12))
as
declare@desc varchar(50)
select @desc = 'Unknown event type'
begin
select [EventID], [EventHeading]
from tblEvents
where [EventID] = @EventID
if ([EventHeading] = 'Info') desc = 'Information'
if ([EventHeading] = 'Warn') desc = 'Warning'
if ([EventHeading] = 'Error') desc = 'Error'
return @desc
end
I can't even create the stored procedure because I get a syntax error. It complains about EventHeading being an invalid column heading and when I comment out the "if" lines it's OK. If I have done a select on a column can I not then refer to it later on?
Any ideas? 
|
|
|
|
|
Few issues:
- define variables where you place the result from SELECT
- don't use parenthesis on parameters if creating a procedure but do use them if creating a function
- procedure cannot return a value, use output parameter or create a function.
Most likely you want to use a function for the logic you described. In that case it would be something like:
create function GetEventType (@EventID int)
returns varchar(12)
AS
declare @desc varchar(50)
declare @eventHeading varchar(12)
begin
select @eventHeading = [EventHeading]
from tblEvents
where [EventID] = @EventID
--
set @desc = 'Unknown event type'
if (@eventHeading = 'Info') set @desc = 'Information'
if (@eventHeading = 'Warn') set @desc = 'Warning'
if (@eventHeading = 'Error') set @desc = 'Error'
---
return (@desc)
end
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks for such a carefully created answer - that's solved my problem perfectly and more importantly, helped me understand the issue I had.
Thanks again 
|
|
|
|
|
You're welcome 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
I'm stuck getting info from all files in een directory using the filesystemobject.
Can someone help me out ?
here's my code so far :
DECLARE @fileobj INT , @fsobj INT
DECLARE @folderobj INT
DECLARE @exists INT, @error INT
DECLARE @Property varchar(255)
DECLARE @src VARCHAR(255), @desc VARCHAR(255)
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
EXEC @error = sp_OACreate 'Scripting.FileSystemObject', @fsobj OUT
EXEC sp_OAGetErrorInfo @fsobj, @src OUT, @desc OUT
SELECT CONVERT(varbinary(4),@error), Source=@src, Description=@desc
-- EXEC sp_OAGetProperty @folderobj, 'Name', @Property OUT
-- print @Property
EXEC sp_OAMethod @fsobj, 'GetFolder', @folderobj OUT,'C:\'
EXEC sp_OAGetErrorInfo @folderobj, @src OUT, @desc OUT
SELECT CONVERT(varbinary(4),@error), Source=@src, Description=@desc
/* RETURN NOTHING */
--EXEC sp_OAGetProperty @folderobj, 'Name', @Property OUT
--print @Property
/* CREATE A TEMP TABLE */
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
/* FILL TEMP TABLE WITH FILES COLLECTION */
INSERT INTO #dirlist (FName)
EXEC sp_OAGetProperty @folderobj , 'Files', @fileobj OUTPUT
EXEC sp_OAGetErrorInfo @fileobj, @src OUT, @desc OUT
SELECT error=CONVERT(varbinary(4),@error), Source=@src, Description=@desc
/* SHOW FILES */
select * from #dirlist
Question is how can i fill my temp table? (it's still empty)
Thx,
Kurt
|
|
|
|
|
Hi All,,
I want to get a Max value from a table and increment it by 1,, such as
if the max value is 1/50, It will be 2/50, but the problem how to get it from a db.
thanks all,,
|
|
|
|
|
I'm not sure if I understood you correctly, but querying the maximum value for a column would be something like:
SELECT MAX(ColumnName) FROM TableName
Is that what you're looking for?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i din get ur problem in right way but i hope my answer will benefit u in some way.....
ex. if u hav a salary column in emp table and suppose if u want to select the maximum sal from table and increment it by 3000 rs. then try the following procedure for this...
create or replace procedure sal_increment as
v_maxsal number(20);
begin
select max(salary) into v_maxsal from emp;
update emp set salary=salary+3000 where salary=v_maxsal;
end;
Hope u understood the problem and the solution given above....
and i hope u had query in pl/sql.....
|
|
|
|
|
Hi all,
I made one procedure in which i use sp_cursoropen procedure and it has one parameter for rowcount. When i execute the procedure, it does not return rowcount, i mean it returns -1 even there is 20 records in my table.
Can anybody help me...
Thanks
Krishnraj
|
|
|
|
|
Hi
Put return at the end of you're stored procedure
declare @counter int
select @counter = count(*) from mytable
return @counter
or
set rowcount as output parameter and print it.
or
set nocount off
greetz
|
|
|
|
|
Hi topcatalpha,
Thanks for reply,
but sp_cursoropen has one parameter which returns the total count of table. so no need to fire extra query as u said.
sp_cursoropen is sql server 2000's in built system procedure
i think there may be some other settings.
any way thanks buddy...
Krishnraj
|
|
|
|
|
With what parameters are you callin this sp. It would depend on the cursor type if the rowcount can be returned. For example if the cursor is forward only, AFAIK the actual rowcount cannot be determined.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika,
Below is my query where u find variable @mainQuery. which is my table fetch query with Inner join with 3 tables.
Declare @cursor int, @rowcount int
Exec sp_cursoropen @cursor output,@mainQuery,16,8196,@rowcount output
Select @rowcount
Exec sp_cursorfetch @cursor,32,@RowNumber,@PageSize
Exec sp_cursorclose @cursor
Do u think that there may be a problem with inner join query?
Thanks for replying...
Krishnraj
|
|
|
|
|
No I don't believe that the inner join is the problem. Now you're using fast forward-only cursor.
What happens if you change to keyset-driven cursor:
Exec sp_cursoropen @cursor output, @mainQuery, <code>1,</code> 8196, @rowcount output
or dynamic cursor
Exec sp_cursoropen @cursor output, @mainQuery, <code>2,</code> 8196, @rowcount output
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika,
Yes your answer is correct, inner join is not problem here.
And as per u said, i used keyset-driven cursor then it gives me count.. 
So its briliant suggesion...
Thank you boss....![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Krishnraj
|
|
|
|
|
You're welcome
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi All,
I accidently updated some data in Staff table on a production database, and i can't just simply install the back-up database as there are quite a few real time transaction occuring, is there a way that i can get the original staff data back? My database is M/S Sql Server 2000.
Many thanks.
|
|
|
|
|
You could restore the backup to another server and update the data back from the Staff-table on that restored database. This way you don't destroy other data in the production database.
For updating the data back from restored database, you can use for example SQL statements using linked server.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Oops it happens all the time, even to people who are very carefull,Everyone has work with SQL, has done something like that and you know what, If your Staff table was updated after the Back.There is no way you can recover those lost Updates. It happens to everyone remember that
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
