To change the database’s collation ALTER DATABASE MyDataBase COLLATE [NewCollation] To change the collation of a column ALTER TABLE MyTable ALTER COLUMN Column1 [TYPE] COLLATE [NewCollation] But there are a number of limitations on when you can do this, very notably that this is denied if the column is used in any index. You can … Read more
The key word here is FIRST. You can use analytic function FIRST_VALUE or aggregate construct FIRST. For FIRST or LAST the performance is never worse and frequently better than the equivalent FIRST_VALUE or LAST_VALUE construct because we don’t have a superfluous window sort and as a consequence a lower execution cost: select table_A.id, table_A.name, firstFromB.city … Read more
If you really do have some pre-existing column in your data set that already does uniquely identify your row – then no, there’s no need for an extra ID column. The primary key however must be unique (in ALL circumstances) and cannot be empty (must be NOT NULL). In my 20+ years of experience in … Read more
As the statement executed is not actually DML (eg UPDATE, INSERT or EXECUTE), but a piece of T-SQL which contains DML, I suspect it is not treated as an update-query. Section 13.1.2.3 of the JDBC 4.1 specification states something (rather hard to interpret btw): When the method execute returns true, the method getResultSet is called … Read more
Looks like you don’t need nested joins here. Try to use something like Event.joins(:store).where(stores: {retailer_id: 2}) Nested join should also work using stores Event.joins(:store => :retailer).where(stores: {retailer: {id: 2}})
You’re actually over-thinking this. I first run this query in one window (to set things up): create table X(ID int not null) create table Y(ID int not null) go create trigger T_X on X after insert as insert into Y(ID) select inserted.ID go I can then discard that window. I open a new query window, … Read more
Anything that you do is going to require reading and writing 38m rows, so nothing is going to be real fast. Probably the fastest method is to put the data into a new table: create table newTable as select id1, id2 from oldTable; Or, if you want to be sure that you preserve types and … Read more
Try using a format file since your data file only has 4 columns. Otherwise, try OPENROWSET or use a staging table. myTestFormatFiles.Fmt may look like: 9.0 4 1 SQLINT 0 3 “,” 1 StudentNo “” 2 SQLCHAR 0 100 “,” 2 FirstName SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 “,” 3 LastName SQL_Latin1_General_CP1_CI_AS 4 SQLINT 0 4 … Read more
You should place parenthesis around the selects: coalesce( (SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER) , (select StmtSeq.nextval from dual) )
You could do it in two ways. Method 1 Using LPAD. For example, SQL> WITH DATA(num) AS( 2 SELECT 540 FROM dual UNION ALL 3 SELECT 60 FROM dual UNION ALL 4 SELECT 2 FROM dual 5 ) 6 SELECT num, lpad(num, 5, ‘0’) num_pad FROM DATA; NUM NUM_P ———- —– 540 00540 60 00060 … Read more