WHERE_IN query with a composite key?
This syntax works for Oracle and PostgreSQL: SELECT * FROM table_name WHERE (key_part_1, key_part_2) IN ( (‘B’,1), (‘C’,2) );
sql
Pivoting rows into columns dynamically in Oracle
Oracle 11g provides a PIVOT operation that does what you want. Oracle 11g solution select * from (select id, k, v from _kv) pivot(max(v) for k in (‘name’, ‘age’, ‘gender’, ‘status’) (Note: I do not have a copy of 11g to test this on so I have not verified its functionality) I obtained this solution … Read more
Count distinct value pairs in multiple columns in SQL
To get a count of the number of unique combinations of id, name and address: SELECT Count(*) FROM ( SELECT DISTINCT id , name , address FROM your_table ) As distinctified
Google Sign-In, Database Side
Create a EXTERNAL_ID column, and use it to store the identifier from the external system. You should add an EXTERNAL_TYPE column and set that to ‘GOOGLE’.. when you go to integrate further authentication-providers, this can be set to different values (eg ‘OPEN_ID’, ‘FACEBOOK’.) When interfacing with external systems, it is always necessary to store the … Read more
How do I write a full outer join query in access
Assuming there are not duplicate rows in AA and BB (i.e. all the same values), a full outer join is the equivalent of the union of a left join and a right join. SELECT * FROM AA LEFT JOIN BB ON AA.C_ID = BB.C_ID UNION SELECT * FROM AA RIGHT JOIN BB ON AA.C_ID = … Read more
ERROR: CASE types character varying and numeric cannot be matched
All the branches of a case expression should return the same datatype. One way to achieve that is to explicitly cast where needed: ,(case when all_loc.country = ‘DE’ then msc_si.buyer_id::varchar else msc_si.buyer_name end) as “purchasing_group_name_buyer_name” — Here ———————————————–^ ,(case when all_loc.country = ‘DE’ then msc_si.planner_code::varchar else mscp.description end) as “mrp_controller_name” — And here ———————————————–^
SQL full text search vs “LIKE”
Full text search is likely to be quicker since it will benefit from an index of words that it will use to look up the records, whereas using LIKE is going to need to full table scan. In some cases LIKE will more accurate since LIKE “%The%” AND LIKE “%Matrix” will pick out “The Matrix” … Read more
How do I drop a column with object dependencies in SQL Server 2008?
Did you try first: ALTER TABLE <tablename> DROP CONSTRAINT defEmptyString; ?
SQL insert with select and hard-coded values
INSERT INTO Movies (Title, Director, Cost, Profits) SELECT ‘Star Wars’, name, 50000, 1000000 FROM Directors WHERE name=”Lucas”
T-SQL INSERT INTO with LEFT JOIN
You want insert into . . . select: INSERT INTO [DB_A].[dbo.a_test](a,b,c,d,e) –ADDED A COLUMN select p.product_info, p.product_date, p.smth, pr.program_name, pr.program_smth FROM [DB_B].dbo.products p LEFT JOIN [DB_B].dbo.program pr ON p.program_name = pr.product_info; I also fixed the query to use table aliases, so it is much easier to read.