NUMTODSINTERVAL usage in Oracle

I had used this function sometime back and had totally forgotten about it. Used it again in a procedure where i had to subtract minutes value stored in a cursor variable (or local variable) of a procedure from systimestamp and compare it against created_date of a table column.

select count(*) from my_db.my_table 
where created_date > (systimestamp - numdstointerval (cursor_loop_rec.minutes, 'minute'));

In this if count > 0 then instances have been created for that particular composite in the required time frame.

Hope this helps...

Nested Select in SQL Server

Hope this works as expected as i wanted an alternative to creating temp table to get the distinct values.


select top 100 * from (
select distinct a.account_id, 
                                     a.account_type as newapp_at, 
                                     c.account_type as myapp_at,    
                                     c.acct_modified_date 
from dbo.account a (nolock), dbo.account_xref b (nolock), 
                        dbo.account_stage_arch c (nolock)
where a.account_id = b.account_id
    and b.source_key = c.source_key
    and isnull (a.account_type, '') = ''
    and isnull (c.account_type, '') = ''
    and isnull (b.end_date, '') = ''
    and b.source_system = 'MYAPP') x
order by acct_modified_date desc;

Well i can also use the RANK function to get it done.

Hope this helps....

ANALYZE INDEX and INDEX_STATS

ANALYZE INDEX myidx1 VALIDATE STRUCTURE;

SELECT * FROM INDEX_STATS;

INDEX_STATS only stores index analysis of the last performed ANALYZE INDEX statement.

You can find valuable information about any index in this table and can be used for various purposes. 

I used it to determine the fragmentation on an index, it can be determined if the index needs to be rebuilt or not.

1. If del_lf_rows is less than the estimated record count in the table

2. if lf_rows is less than the lf_blks

3. if height value is greater than 4

In all the cases above the index needs to be rebuilt....

Item cannot be found in the collection

SQL Server 2000

Error: Item cannot be found in the collection corresponding to the requested name or ordinal.

Resolution: Check field names and match them against table column names, i had mis-spelled one of them. One other reason might be the field name does not exist in the database.

Server not configured for RPC

Received this error message today when trying to execute an update statement out a linked server:

The RPC Out parameters value was set to False which must be changed to Ture (picture above) and it worked without any errors.

Hope this helps....

sp_delete_jobschedule SQL Server

I had to create a deployment script today which included modifications to an existing job. Ideally the normal process requires taking backup of the existing object and then implementing the modified object.

Could not find a built in function to rename the job, so my approach was to delete schedule of the existing job and create a new job with a different name (appended v2 i.e. version 2) with the same schedule.

sp_delete_jobschedule 
    @job_name = N'my existing job name', 
    @name = N'existing schedule name'

sp_xml_preparedocument and sp_xml_removedocument in SQL Server

Got a chance of using the 2 built in functions below:

sp_xml_preparedocument

sp_xml_removedocument

Implementation: Input to a the procedure is a text datatype which contains XML with account_id's and we just needed the list of account_id's in our procedure.

CREATE PROCEDURE dbo.get_account_info
(account_list text)

AS

BEGIN


DECLARE @ATempTable TABLE    
 (  
  [id] varchar(30) NULL  
 )  


 DECLARE @doc_out int 


 EXEC sp_xml_preparedocument @doc_out OUTPUT, @account_list    
  
 INSERT INTO @ATempTable ([row_id])  
 SELECT [id] FROM OPENXML (@doc_out, '/root/account', 1) WITH (id varchar(30))  
  
 EXEC sp_xml_removedocument @doc_out

SELECT @doc_out

END

Dynamic Sort By in SQL Server

Sort by column is sent as an input to the stored procedure and the sort by field can be different during different web service calls.

Sorting based on different fields at runtime does not need you to generate SQL statements with sort field appended and then execute them dynamically. It can be done as follows:

use mydb
go


set ansi_nulls on
go
set quoted_identifier off
go


create procedure dbo.get_account_info
(@sort_by_column varchar(255))
as
begin

set nocount on

select account_id, name_id, address_id, start_date, end_date, first_name, middle_name, last_name
from dbo.accounts
where status = 'A'

order by
(case @sort_by_column
when 'last_name' then m.last_name
when 'first_name' then first_name
when 'account_id' then account_id
when 'end_date' then end_date
when 'start_date' then start_date
else account_id
end)

end

Hope this helps.