What is the best way to store a money value in the database?
Decimal and money ought to be pretty reliable. What i can assure you (from painful personal experience from inherited applications) is DO NOT use float!
sql
Efficient way of getting @@rowcount from a query using row_number
Over the years a pile of developer sweat has gone into efficiently paging result sets. Yet, there is no one answer–it depends on your use case. Part of the use case is getting your page efficiently, part is figuring out how many rows are in a complete result set. So sorry if i stray a … Read more
Oracle “ORA-01008: not all variables bound” Error w/ Parameters
The ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name. Set property BindByName to true. Than you can dismiss the double definition of parameters. using(OracleCommand cmd = con.CreateCommand()) { … cmd.BindByName = true; … }
Insert into from CTE
This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;
PostgreSQL ORDER BY issue – natural sort
Since Postgres 9.6, it is possible to specify a collation which will sort columns with numbers naturally. https://www.postgresql.org/docs/10/collation.html — First create a collation with numeric sorting CREATE COLLATION numeric (provider = icu, locale=”en@colNumeric=yes”); — Alter table to use the collation ALTER TABLE “employees” ALTER COLUMN “em_code” type TEXT COLLATE numeric; Now just query as you … Read more
Are there any limits on length of string in mysql?
CHAR A fixed-length string that is always right-padded with spaces to the specified length when stored The range of Length is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set unless the BINARY keyword is given. … Read more
Oracle, Make date time’s first day of its month
According to http://psoug.org/reference/date_func.html, this should work a dandy… SELECT TRUNC(yourDateField, ‘MONTH’) FROM yourTable
TSQL – Is it possible to define the sort order?
It’s incredibly clunky, but you can use a CASE statement for ordering: SELECT * FROM Blah ORDER BY CASE MyColumn WHEN ‘orange’ THEN 1 WHEN ‘apple’ THEN 2 WHEN ‘strawberry’ THEN 3 END Alternately, you can create a secondary table which contains the sort field and a sort order. TargetValue SortOrder orange 1 apple 2 … Read more
How to insert one column into other column within the same table in SQL Server
UPDATE table SET col_2 = col_1
Using Spring JdbcTemplate to extract one string
It would help a lot to know what your SQL query looks like, but assuming it’s something like SELECT STREET_NAME FROM table WHERE ID=1; CODE: public String getStreetNameById(int id) { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); String sql = “SELECT STREET_NAME FROM table WHERE ID=?”; String streetName = (String) jdbcTemplate.queryForObject( sql, new Object[] { id }, … Read more