I have this query   =QUERY( 'Monitor'!$A$4:$AB,  "select * WHERE " &amp; IF(C2="Yes","A=1 ",IF(C2="No","A=0 ","(A=0 OR A=1) ")) &amp;  "AND " &amp; IF(ISBLANK(C3),,"D="&amp;C3&amp;" ") &amp; "AND "&amp; IF(ISBLANK(C4),"B IS NOT NULL ", "B="&amp;C4&amp;" ") &amp; "AND " &amp; IF(ISBLANK(C5),"F IS NOT NULL ","F&gt; "&amp;C5&amp;" ") )   The last part refers to dates where   C5 = Date in format YYYY/MM/DD  F = Column with dates in same format  I want to get only the rows where the date is larger than that of cell C5.  I don't get an error back, I just get an empty range. I've checked if the other filters could be the reason but they're not.  Couldn't find answer here and do not want to use FILTER.  Any ideas?
38563
I'm trying to create two cells that will update my query: A >= date"A1" and A &lt;= date "B1".  My goal is to create a search page for the data, where the end user could insert a date or the =today() function and it would update the query. I am currently getting a formula parse error from:  =QUERY(Data!6:1397,"SELECT A,B,C,D,E,F,G,H,I,J,K,L WHERE A &gt;= date"A1"and  A &lt;= date"B1"")
41250
I am trying to create a formula field to count if there are any blank values on Number field.  My formula is :      if(ISBLANK(Number Field),1,0)   However, it is always coming as '0' even if the number field is having some value in the record.  Please advise as to where I am going wrong.
108095
Who can explain why   select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');   yields  ----------- ----------- ----------- ----------- ----------- 1           0           0           0           1   The funny consequence of this is that in   create table test ( val varchar(10) ); insert into test values( '' ); update test set val = ' ' where val = '';   the update will indeed replace the empty string by a blank, but the where clause keeps to be true and repeated executions of the update statement tell  (1 row(s) affected)
91283
I'm writing an integration manual that specifies how to use our WCF service for mobile messages and responses. The part in question needs to specify that the search results will be filtered between the start and end dates.  My aim is to make it clear that the time portion of the two dates will be discarded and the result will include everything from the start date to the end date. (In practice the code truncates the dates, adds one day to the end date, and returns the data that conforms to a query something like:  SELECT * FROM Table WHERE DateCreated >= @StartDate AND DateCreated &lt; @EndDate)  But my paragraph looks verbose and clumsy. It doesn't feel right. I feel kind of like John Cleese telling the kids when to put their clothes onto a lower peg. How can I make this less verbose?  Or maybe I'm over-thinking this?     When searching for responses, the search is performed between the supplied dates, after truncating the time portion thereof. The results will include responses with a date-time greater than equal to the start date, and less than one day after the end date. So passing the same value for start and end date will retrieve all responses received on that date, for messages previously sent by that user.
15035
Can some one correct my soql? I need to export records in data loader like:  WHERE Id IN ('123123123','123123fsdf')   But this following query does not work for me:  SELECT Id, Syndication_Documents__c FROM ContentVersion  WHERE Syndication_Documents__c in : 'a4fa0000000KzbV','a4fa0000000KzbW'
10468
I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL.  I don't remember why this was the case though.  All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).  But what do you do in the case of dates, datetime, and time (SQL Server 2008)?  You'd have to use some historic or bottomed-out date.  Any ideas on this?
118445
I am trying to write a condition into a SQL WHERE clause that forces the column comparison to a variable to be compared in lowercase. However the addExpression function isn't accomplishing this (since that is putting the expression in the field selection, not the where clause.  Here is what I have tried:  $category = 'mobile wifi'; $query = db_select('taxonomy_term_data', 'ttd')     -&gt;fields('ttd', array('tid')); $query-&gt;innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid'); $query-&gt;addExpression("LOWER(ttd.name) = $category"); $result = $query-&gt;condition('machine_name', 'images_cat', '=')     -&gt;execute()     -&gt;fetchAssoc();   And this:  $category = 'mobile wifi'; $query = db_select('taxonomy_term_data', 'ttd')     -&gt;fields('ttd', array('tid')); $query-&gt;innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid'); $result = $query-&gt;condition('machine_name', 'images_cat', '=')     -&gt;condition('LOWER(ttd.name)', $category, '=')     -&gt;execute()     -&gt;fetchAssoc();   The second one becomes invalid and the first one causes the problem I mentioned above. Any thoughts or suggestions?  Thanks, Patrick
799
While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.  The  say that the general logical parsing order is such that SELECT is parsed nearly last (thus resulting in "no such object [alias]" errors when trying to use a column alias in other clauses). There was even  to allow for aliases to be used anywhere, which was shot down by the Microsoft team, citing ANSI standards compliance issues (which suggests that this behavior is part of the ANSI standard).  As a programmer (not a DBA), I found this behavior somewhat confusing, since it seems to me that it largely defeats the purpose of having column aliases (or, at the very least, column aliases could be made significantly more powerful if they were parsed earlier in the query execution), since the only place you can actually use the aliases is in ORDER BY. As a programmer, it seems like it's missing a huge opportunity for making queries more powerful, convenient, and DRY.  It looks like it's such a glaring issue that it stands to reason, then, that there are other reasons for deciding that column aliases shouldn't be allowed in anything other than SELECT and ORDER BY, but what are those reasons?
1105
I have never had the need to do this before today, but it doesn't seem like you can make OR queries with EntityFieldQuery, since db_or is used for select queries.  An example would get to all entities that has a date field where the value is null or after today.  Am I missing something or some trick or is this simply not supported?
82186
I would like to filter a datetime field but I only want to filter MONTH  var requestUri = "xxxx/getByTitle('Contact')/items?$select=CellPhone,mdate,ddate&amp;$filter= Birthday ge '" + today + "'";   The query is working fine and I also tried month(birthday) as it was in the odata specifications but didn't work.  How can I get the month from the Birthday Datetime field.I can get it from my today variable.
121286
I'm trying to create a formula in a Google spreadsheet which looks like this:  if (x &lt; 0, x + 1, x)   It means if x is smaller than 0, return x + 1, else return x.  However, x itself is an expression, e.g. A1 + B1. So I ended up with:  if ((A1 + B1) &lt; 0, (A1 + B1) + 1, (A1 + B1))   How can I save the expression (A1 + B1) into a temporary variable x such that I can do this? :  x = (A1 + B1); if (x &lt; 0, x + 1, x);   The current expression in my spreadsheet looks like this:  if(     timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))     &lt; 0,     1 +     timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))     ,     timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10)))) )   I'm trying to get it to look shorter and more manageable like this:  x = timevalue(Sheet1!$D10) - timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10)))); if(     x     &lt; 0,     1 +     x     ,     x )
21469
I am studying the new database layer, and I am glad it is changed to an OOP layer. I need to implement a query with db_select(). I found I can add a WHERE statement with $query-&gt;condition(), but by default the conditions are AND'ed together.  How can I OR the conditions together?  $query=db_select('users','u')-&gt;fields('u',array('uid','title','created','uid')); $query-&gt;join('flag_content','fc' , 'u.uid = fc.content_id'); $query-&gt;condition('fc.fid', '5' , '='); $query-&gt;condition('fc.uid', $uid , '='); //…
15111
This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already.  From what I could gather, if I want to apply a conditional formatting based on the value of another cell, I have to use a custom formula. However, in the list, there are a couple options that would seem to be much more relevant: "Greater than (or equal to)", "Lesser than (or equal to)", "Is (not) equal to", "Is (not) between". But I'm starting to think that they are not so useful.  Here I have a column of numbers ranging from 10 to 30; I want to highlight them in green if they're greater than or equal to a number (20) set in another cell. From a few other questions here and on SO, it would seem that I should use a custom formula as something like =K7&gt;=K4, but instead I still wanted to try the options in the list that seemed more intuitive.    As you can see though, it does some weird things, where it consider 11 to be under 20 (which is true AFAIK), but everything else above 11 is either equal or greater than 20.  My guess is that putting =K4 in the "Value or formula" field didn't quite link to the actual K4 cell, but I can't figure out why it does what it does here.  If I put "20" in the field, it highlights the right cells (but it's not dynamic anymore).  Edit: putting =K$4 works like a charm. Quite literally, in this case.
78923
I'm dealing with nullable values in a column of a table, i'm wondering why this:  with cte as (     select 'c1'::text as c1,null::text as c2     union     select 'c2'::text as c1,null::text as c2 ) select * from cte where c2 is null   returns a result set with the two "rows" having a null value in c2; comparting to this query:  with cte as (     select 'c1'::text as c1,null::text as c2     union     select 'c2'::text as c1,null::text as c2 ) select * from cte where c2 = null   that returns an empty result set!!  so it looks like that field is null is actually different from field = null? the question is... is there "null-compatible operator" to use? so i can write the comparision like: column_name = $1 and argument $1 can be a not null or null value. IF $1 is a null value then it will semantically equals to write as IS NULL, ELSE (if $1 is not a null value) then it would be semantically equals to = $1.  P.D: This question also applies to the case is NOT NULL versus &lt;&gt; NULL
116653
I have this:  If String.IsNullOrEmpty(editTransactionRow.pay_id.ToString()) = False Then     stTransactionPaymentID = editTransactionRow.pay_id 'Check for null value End If   Now, when editTransactionRow.pay_id is Null Visual Basic throws an exception. Is there something wrong with this code?
109382
How to view the result between 2 dates and time ?  My current Code:  SELECT *  FROM [DB].[dbo].[TABLE]  WHERE [USER_ID] = '005'     AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016' AND [TIME] &gt;= '21:00:00'                   -- for date 03-04-2016 AND [TIME] &lt;= '05:00:00'                   -- for date 04-04-2016  ORDER BY [DATE] ASC, [TIME] ASC   This code does not work and I do not know how to fix it  why doesn't this request work ?
37750
I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use.  Something like:  CREATE TRIGGER chk_dates    BEFORE INSERT ON `job_history`    FOR EACH ROW     BEGIN       IF( NEW.end_date &lt; OLD.start_date ) .../// error, don't insert data       ELSE .../// allow the data to be entered   My table job_history will have a start_date, and when end_date is entered, I want it to check to ensure that it isn't prior to start_date.    How do I accomplish this?