by joe.pesch
4. June 2010 18:16
SSIS package that’s developed in Microsoft Visual Studio 2005 may have a script task inside that doesn’t open when you click on the "Design Script" button. In my case this was after I installed SQL Server 2008 locally. I had to re-install SQL Server 2005 sp2 (after installing SQL Server 2008) and then the script tasks are ok again.
by joe.pesch
3. March 2010 16:23
Common table expression allows "with" statement to assign a temporary table name to a select statement (similar to SQL server "using" statement). Also, the below example shows how to use the Oracle "partition" function to group results (this sample shows how to get the first row of each group of data).
with MaxStatus as
(
select * from
(
select
h.*
, row_number() over (partition by h.ap_num order by h.change_date) as RowNumber
from history h
) where RowNumber = 1
)
select * from MaxStatus
eef27683-6e33-4d32-97e1-ae0ccd49bd50|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags:
JavaScript
Oracle
by joe.pesch
12. March 2009 18:58
Date formatting in Oracle
select to_date('02/17/2009 12:22:01 PM', 'mm/dd/yyyy HH:MI:SS AM') from dual
149be4bd-d522-4d91-83b2-7f1d89da0e76|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags:
C#, Windows
Oracle
by joe.pesch
22. October 2008 15:36
Tables: all_tables Columns: all_tab_columns
7ca067f5-8918-40c0-9c1b-b165d972453a|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags:
HTML
Oracle | SQL Server
by joe.pesch
12. August 2008 21:28
In SQL server you create an identity column like the sample below which creates an identity column with seed value of 1 and increment value of 1. Then when you insert records the column value is incremented and set automatically.
SQL Server Sample:
create table #MyTable(RecID int identity(1,1), MyData varchar(50))
insert into #MyTable(MyData) values('This is a test')
insert into #MyTable(MyData) values('This is another test')
select * from #MyTable
drop table #MyTable
Not so in Oracle. There you create the integer column; however, you must manually increment and set the column value as part of your insert statement. You can use a Sequence object (after you create one) as shown below.
create sequence MySequence minvalue 1 maxvalue 999999999999 start with 1 increment by 1 cache 20
create table imdw.MyTable(RecID int, MyData varchar2(50))
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is a test')
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is another test')
select * from imdw.MyTable
drop table imdw.MyTable
drop sequence MySequence
In both cases you should get the following result set:
RecID |
MyData |
1 |
This is a test |
2 |
This is another test |
f122b121-12e6-498b-8b87-3410b7eb0359|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags:
[None]
Oracle | SQL Server