Posting this in case if somebody stumbles into same problem. Googled for Retrieve unsaved Scripts and found a solution. Run the following select script. It provides a list of scripts and its time of execution in the last 24 hours. This will be helpful to retrieve the scripts, if we close our query window in … Read more
INSERT INTO #TempTable (ID, Date, Name) SELECT id, date, name FROM physical_table
When you want to replace a possibly null column with something else, use IsNull. SELECT ISNULL(myColumn, 0 ) FROM myTable This will put a 0 in myColumn if it is null in the first place.
(Strictly speaking, IN and ANY are Postgres “constructs” or “syntax elements”, rather than “operators”.) Logically, quoting the manual: IN is equivalent to = ANY. But there are two syntax variants of IN and two variants of ANY. Details: How to use ANY instead of IN in a WHERE clause? IN taking a set is equivalent … Read more
You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly. The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the ‘comma’ joins, where the join condition is specified in the WHERE clause). … Read more
COALESCE is more modern function that is a part of ANSI-92 standard. NVL is Oracle specific, it was introduced in 80‘s before there were any standards. In case of two values, they are synonyms. However, they are implemented differently. NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL … Read more
Yes, use INSERT … ON DUPLICATE KEY UPDATE id=id (it won’t trigger row update even though id is assigned to itself). If you don’t care about errors (conversion errors, foreign key errors) and autoincrement field exhaustion (it’s incremented even if the row is not inserted due to duplicate key), then use INSERT IGNORE like this: … Read more
There are basically 4 techniques for this task, all of them standard SQL. NOT EXISTS Often fastest in Postgres. SELECT ip FROM login_log l WHERE NOT EXISTS ( SELECT — SELECT list mostly irrelevant; can just be empty in Postgres FROM ip_location WHERE ip = l.ip ); Also consider: What is easier to read in … Read more
Sample DDL create table #Temp ( EventID int, EventTitle Varchar(50), EventStartDate DateTime, EventEndDate DatetIme, EventEnumDays int, EventStartTime Datetime, EventEndTime DateTime, EventRecurring Bit, EventType int ) ;WITH Calendar AS (SELECT /*…*/) Insert Into #Temp Select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle … Read more
I believe the answer is that Oracle is very, very old. Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has … Read more