How to add a new column with foreign key constraint in a single statement in oracle
alter table tab1 add c1 number(20) constraint tab1_c1_fk references tab2(c2); c1 new column in the table tab1 with the FK tab1_c1_fk on the table tab2 column c2.
sql
Getting all parent rows in one SQL query
Adapted from here: SELECT T2.id, T2.name FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars, table1 h WHERE @r <> 0) T1 JOIN table1 T2 ON T1._id = T2.id ORDER … Read more
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator
You can’t use a Join between a SQL source and a local source. You’ll need to bring the SQL data into memory before you can join them. In this case, you’re not really doing a join since you only take the elements from the first collection, what you want is a select…where…selectid in query, which … Read more
SQL 0 if negative value
An alternate approach you could use is the GREATEST() function. SUM(GREATEST(ordered_item.amount, 0)) as purchases
How can I use a PostgreSQL triggers to store changes (SQL statements and row changes)
example of an audit trigger from https://www.postgresql.org/docs/current/static/plpgsql-trigger.html CREATE TABLE emp ( empname text NOT NULL, salary integer ); CREATE TABLE emp_audit( operation char(1) NOT NULL, stamp timestamp NOT NULL, userid text NOT NULL, empname text NOT NULL, salary integer ); CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN — — Create a row … Read more
IF() statement alternative in SQLite
For generic SQL you can use CASE: CASE is used to provide if-then-else type of logic to SQL. Its syntax is: SELECT CASE (“column_name”) WHEN “condition1” THEN “result1” WHEN “condition2” THEN “result2” … [ELSE “resultN”] END FROM “table_name” From http://www.sqlite.org/lang_expr.html section “The CASE expression” E.g. UPDATE pages SET rkey = rkey + 2, lkey = … Read more
Creating a composite foreign key in SQL Server 2008
A foreign key MUST refer to columns that compose a unique index (PK or UK) with the same number of column, their types and order. E.g.: CREATE TABLE PrimaryTable ( Key1 varchar(20), Key2 date) GO ALTER TABLE PrimaryTable ADD CONSTRAINT PK PRIMARY KEY (Key1, Key2) GO CREATE TABLE SecondaryTable ( AutoID int IDENTITY, Key1 varchar(20), … Read more
How to convert List into comma separated String
Using Java 8 you could do it in one line: String addressID = addresses .stream() .map(a -> String.valueOf(a.addressId)) .collect(Collectors.joining(“,”));
select from one table, insert into another table oracle sql query
From the oracle documentation, the below query explains it better INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100; You can read this link Your query would be as follows //just the concept INSERT INTO quotedb (COLUMN_NAMES) //seperated by comma SELECT COLUMN_NAMES FROM tickerdb,quotedb WHERE quotedb.ticker = tickerdb.ticker Note: Make sure the … Read more
How can I join two tables but only return rows that don’t match?
SELECT T1.* FROM T1 WHERE NOT EXISTS(SELECT NULL FROM T2 WHERE T1.ID = T2.ID AND T1.Date = T2.Date AND T1.Hour = T2.Hour) It could also be done with a LEFT JOIN: SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID AND T1.Date = T2.Date AND T1.Hour = T2.Hour WHERE T2.ID IS NULL