You should use CASE clause:

    CASE
        WHEN FieldA &gt; FieldB THEN 1
        ELSE 0
    END AS [BIT]



    Select Convert(Bit, Case When FieldA &gt; FieldB Then 1 Else 0 End) As YourBitColumn

If you want to return a BIT, then you need the convert (or cast) to a bit data type, otherwise, SQL would interpret the hard coded constant (1 or 0) as an integer.


You can use `IIF` function. For example:

    SELECT IIF(FieldA &gt; FieldB, 1, 0) AS YourBitColumn FROM t

`IFF` Function&#39;s syntax like as below:

    IIF( boolean_expression, true_value, false_value )

`IFF` returns, If `boolean_expression` is true, then `true_value`, otherwise `false_value`

For more information about `IFF` function : [Logical Functions - IIF (Transact-SQL)][1]


  [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-ver15

I have a question about the return value of operator overloading in C++. Generally, I found two cases, one is return-by-value, and one is return-by-reference. So what&#39;s the underneath rule of that? Especially at the case when you can use the operator continuously, such as `cout&lt;&lt;x&lt;&lt;y`.

For example, when implementing a + operation &quot;string + (string)&quot;. how would you return the return value, by ref or by val.

return value of operator overloading in C++

I&#39;ve come into ownership of several thousand lines of Matlab code, some as &gt;900 line functions and a few directories full of `function_name.m` files.  It&#39;s hard to figure out what everything is doing (or relating to) or figure out the dependencies.  What would you suggest to visualize the functions structure, such as what functions are called from which, and in what sequence?

How to visualize a project structure in MATLAB?

How to select a result of comparison operator as a field with type BIT?

How it does work in `C#`:

    bool isGreater = FieldA &gt; FieldB;

How it doesn&#39;t work in `T-SQL`:

    SELECT (FieldA &gt; FieldB) AS BIT FROM t

How to write such task properly?

Using comparison operators in SELECT clause of T-SQL query

How to select a result of comparison operator as a field with type BIT?
How it does work in <code>C#</code>:
<pre><code class="hljs language-ini">bool isGreater = FieldA > FieldB;
</code></pre>
How it doesn't work in <code>T-SQL</code>:
<pre><code class="hljs language-vbnet">SELECT (FieldA > FieldB) AS BIT FROM t
</code></pre>
How to write such task properly?

I am using MySQL. Here is my schema:

Suppliers(**sid: integer**, sname: string, address string)

Parts(**pid: integer**, pname: string, color: string)

Catalog(**sid: integer, pid: integer**, cost: real)

(primary keys are bolded)

I am trying to write a query to select all parts that are made by at least two suppliers:

    -- Find the pids of parts supplied by at least two different suppliers.
    SELECT c1.pid                      -- select the pid
    FROM Catalog AS c1                 -- from the Catalog table
    WHERE c1.pid IN (                  -- where that pid is in the set:
	    SELECT c2.pid                  -- of pids
	    FROM Catalog AS c2             -- from catalog
	    WHERE c2.pid = c1.pid AND COUNT(c2.sid) &gt;= 2 -- where there are at least two corresponding sids
    );

First off, am I even going about this the right way?

Secondly, I get this error: 

&gt; 1111 - Invalid use of group function

What am I doing wrong?

MySQL: Invalid use of group function

With linq I have to check if a value of a row is present in an array.  
The equivalent of the sql query:

    WHERE ID IN (2,3,4,5)

How can I do it?

What is the linq equivalent to the SQL IN operator

In **SQL Server**, it is possible to insert rows into a table with an `INSERT.. SELECT` statement:

    INSERT INTO Table (col1, col2, col3)
    SELECT col1, col2, col3 
    FROM other_table 
    WHERE sql = &#39;cool&#39;

Is it also possible to _update_ a table with `SELECT`? I have a temporary table containing the values and would like to update another table using those values. Perhaps something like this:

    UPDATE Table SET col1, col2
    SELECT col1, col2 
    FROM other_table 
    WHERE sql = &#39;cool&#39;
    WHERE Table.id = other_table.id


How do I UPDATE from a SELECT in SQL Server?

I have been trying to find out what the difference is between the `IS` and `AS` keywords in PL/SQL when creating an Oracle function or procedure. 

I have searched and have been unable to find any information on this. Does anyone know the difference?

IS vs AS keywords for PL/SQL Oracle Function or Procedure Creation

You often see database fields set to have a magnitude of 255 characters, what is the traditional / historic reason why? I assume it&#39;s something to do with paging / memory limits, and performance but the distinction between 255 and 256 has always confused me.

    varchar(255)

Considering this is a capacity or magnitude, *not an indexer*, **why is 255 preferred over 256?** Is a byte reserved for some purpose (terminator or null or something)?

Presumably varchar(0) is a nonsense (has zero capacity)? In which case 2^8 of space should be 256 surely?

Are there other magnitudes that provide performance benefits? For example is varchar(512) less performant than varchar(511) or varchar(510)?

Is this value the same for all relations databases, old and new?

**disclaimer** - I&#39;m a developer not a DBA, I use field sizes and types that suit my business logic where that is known, but I&#39;d like to know the *historic* reason for this preference, even if it&#39;s no longer relevant (but even more if it still is relevant).

Edit:
-----

Thanks for the answers, there seems to be some concensus that a byte is used to store size, but this doesn&#39;t settle the matter definitively in my mind.

If the meta data (string length) is stored in the same contiguous memory/disk, it makes some sense. 1 byte of metadata and 255 bytes of string data, would suit each other very nicely, and fit into 256 contiguous bytes of storage, which presumably is neat and tidy.

But...If the metadata (string length) is stored separately from the actual string data (in a master table perhaps), then to constrain the length of string&#39;s data by one byte, just because it&#39;s easier to store only a 1 byte integer of metadata seems a bit odd.

In both cases, it would seem to be a subtlety that probably depends on the DB implementation. The practice of using 255 seems pretty widespread, so someone somewhere must have argued a good case for it in the beginning, can anyone remember what that case was/is? Programmers won&#39;t adopt any new practice without a reason, and this must have been new once.

Why historically do people use 255 not 256 for database field magnitudes?

I am using SQL Server 2008 developer edition. I was trying to attach the AdventureWorks2008 database.  

When I tried to attach, I received an &quot;access is denied&quot; error. According to the event log, it came from the O/S: 

&gt;Open failed: Could not open file D:\ProjectData\AdventureWorks\AdventureWorksLT2008_Data.mdf for file number 0.  OS error: 5(Access is denied.).

I thought &quot;NTFS problem&quot;, but System (and I) have modify access to both files.

I found that I can successfully attach the database if I log in as sa, but my user account won&#39;t work.

I am a member of the local administrators group on my machine, and I am in the sysadmins role in SQL Server instance.

Any idea why I had to be logged in as sa?

Access is denied when attaching a database

I have a table that contains the following:

    DataDate                 Value
    2010-03-01 08:31:32.000  100
    2010-03-01 08:31:40.000  110
    2010-03-01 08:31:42.000  95
    2010-03-01 08:31:45.000  101
    .                        .
    .                        .
    .                        .

I need to multiply the value column by the difference in time between the current and previous rows and sum that for the entire day.

I currently have the data set up to come in every 10 seconds which makes for a simple conversion in the query:

    SELECT Sum((Value/6) FROM History WHERE DataDate BETWEEN @startDate and @endDate

Where @startDate and @endDate are today&#39;s date at 00:00:00 and 11:59:59.

Before I set the data to be collected every 10 seconds it was collected whenever the Value changed.  There aren&#39;t any duplicate entries in terms of time, the minimum time difference is 1 second.  

How can I set up a query to get the elapsed time between rows for the case when I don&#39;t know the time interval between readings?

I am using SQL Server 2005.

Calculate Time Difference Between Two Rows

I have a table with the following columns:
EntityId, EntityName, EntityProfile, .................

I want to select the Id and Name and true/false column based on the value of entity profile,
for example a returned result set like below, would mean that entities 1&amp;2 have profiles while 3 not.

    1 Name1 True
    2 Name2 True
    3 Name3 False
    etc.....

I know I can do it using a function that return true/false based on the profile value like this:
SELECT EntityId, EntityName, dbo.EntityHasProfile(EntityId) AS HasProfile FROM Entities

but I&#39;m returning a large no. of records and with this function call for each record, the query is very slow, and when I remove the function call the query execution time drops significantly.

So is there another way of doing this?
Thanks

How to select true/false based on column value?

I need to convert int datafield to nvarchar with leading zeros

example:

1 convert to &#39;001&#39;

867 convert to &#39;000867&#39;, etc.

thx.

-------------

This is my response 4 Hours later ...



I tested this T-SQL Script and work fine for me !

    DECLARE @number1 INT, @number2 INT

    SET @number1 = 1

    SET @number2 = 867

    SELECT RIGHT(&#39;000&#39; + CAST(@number1 AS NCHAR(3)), 3 ) AS NUMBER_CONVERTED

    SELECT RIGHT(&#39;000000&#39; + CAST(@number2 AS NCHAR(6)), 6 ) AS NUMBER_CONVERTED

---------


I created this user function

T-SQL Code :


    CREATE FUNCTION CIntToChar(@intVal BIGINT, @intLen Int) RETURNS nvarchar(20)
    AS
    BEGIN
    
        -- BIGINT = 2^63-1 (9,223,372,036,854,775,807) Max size number

        -- @intlen contains the string size to return
        IF @intlen &gt; 20
           SET @intlen = 20
    	
        RETURN REPLICATE(&#39;0&#39;,@intLen-LEN(RTRIM(CONVERT(nvarchar(20),@intVal)))) 
            + CONVERT(nvarchar(20),@intVal)
    
    END


Example :

SELECT dbo.CIntToChar(  867,  6  ) AS COD_ID


OUTPUT

000867





How to convert int to char with leading zeros?

I have a computed column created with the following line:

    alter table tbPedidos 
    add restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 then 1 else 0 end as bit))

But, now I need to change this column for something like:

    alter table tbPedidos 
    alter column restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1 then 1 else 0 end as bit))

But it&#39;s not working. I&#39;m trying to input another condition to the case statement, but it&#39;s not working.

Thanks a lot!

How can I alter this computed column in SQL Server 2008?

I have `SQL Server 2008 Ent` and `OLTP database` with two big tables. How I can move these tables to another `filegroup` without service interrupting? Now, about 100-130 records inserted and 30-50 records updated each second in these tables. Each table have about 100M records and six `field`s (including one field `geography`).

I am looking for a solution in Google, but all solutions contain 

&gt; create second table, insert rows from first table, drop first table,
&gt; etc

Can I use partitioning functions for solving this problem?

How can I move a table to another filegroup in MS SQL Server?

Is there a way to specify, for example 4 distinct values for a varchar column in MS SQL Server 2008?

For example, I need a column called Frequency (varchar) that only accepts &#39;Daily&#39;, &#39;Weekly&#39;, &#39;Monthly&#39;, &#39;Yearly&#39; as possible values

Is this possible to set within the SQL Server Management Studio when creating the table?

Restrict varchar() column to specific values?

I have MSSQL 2008 installed on my local PC, and my Java application needs to connect to a MSSQL database. I am a new to MSSQL and I would like get some help on creating user login for my Java application and getting connection via JDBC. So far I tried to create a user login for my app and used following connection string, but I doesn&#39;t work at all. Any help and hint will be appreciated.

    jdbc:jtds:sqlserver://127.0.0.1:1433/dotcms 
    username=&quot;shuxer&quot;  password=&quot;itarator&quot;



How do I connect to a SQL Server 2008 database using JDBC?

I have created login account on my localhost\sql2008 Server (Eg. User123)

Mapped to Database (default)

Authentication Mode on SQL Server is set to both (Windows and SQL)

But login to SQL Server fails with following message (for User123)

Note:  Have checked multiple time that UserName / Password are typed in correctly

Error Details:

&gt; Login failed for user &#39;User123&#39;
&gt; (Net.SqlClient Data Provider)
&gt; 
&gt; Server Name : localhost\sql2008 Error
&gt; Number: 18456 Severity : 14 State : 1
&gt; Line Number : 65536

any help on this please.


Unable to login to SQL Server + SQL Server Authentication + Error: 18456

I am trying to pass array parameter to SQL commnd in C# like below, but it does not work. Does anyone meet it before? 

    string sqlCommand = &quot;SELECT * from TableA WHERE Age IN (@Age)&quot;;
    SqlConnection sqlCon = new SqlConnection(connectString);
    SqlCommand sqlComm = new SqlCommand();
    sqlComm.Connection = sqlCon;
    sqlComm.CommandType = System.Data.CommandType.Text;
    sqlComm.CommandText = sqlCommand;
    sqlComm.CommandTimeout = 300;
    sqlComm.Parameters.Add(&quot;@Age&quot;, SqlDbType.NVarChar);
    StringBuilder sb = new StringBuilder();
    foreach (ListItem item in ddlAge.Items)
    {
         if (item.Selected)
         {
             sb.Append(item.Text + &quot;,&quot;);
         }
    }

    sqlComm.Parameters[&quot;@Age&quot;].Value = sb.ToString().TrimEnd(&#39;,&#39;);



Pass Array Parameter in SqlCommand

Maybe I am having a moment of &#39;afternoon&#39;, but can anyone explain why I get 

&gt; Msg 102, Level 15, State 1, Line 2  
&gt; Incorrect syntax near &#39;)&#39;.

When running

    CREATE PROC DisplayDate 
        (@DateVar DATETIME) 
    AS 
    BEGIN
        SELECT @DateVar
    END
    GO
    
    EXEC DisplayDate GETDATE();



Incorrect syntax near &#39;)&#39; calling stored procedure with GETDATE

I&#39;ve been using `GROUP BY` for all types of aggregate queries over the years.  Recently, I&#39;ve been reverse-engineering some code that uses `PARTITION BY` to perform aggregations.  In reading through all the documentation I can find about `PARTITION BY`, it sounds a lot like `GROUP BY`, maybe with a little extra functionality added in?  Are they two versions of the same general functionality, or are they something different entirely?

SQL Server: Difference between PARTITION BY and GROUP BY

I need to ensure that a given field does not have more than one space (I am not concerned about all white space, just space) between characters.

So

    &#39;single    spaces   only&#39;


needs to be turned into 

    &#39;single spaces only&#39;


The below will not work

    select replace(&#39;single    spaces   only&#39;,&#39;  &#39;,&#39; &#39;)

as it would result in 

    &#39;single  spaces  only&#39;

I would really prefer to stick with native T-SQL rather than a CLR based solution.

Thoughts?


Replace duplicate spaces with a single space in T-SQL

Why is === faster than == in PHP?

I&#39;m trying to compare two numbers in R as a part of a if-statement condition:

`(a-b) &gt;= 0.5`

In this particular instance, a = 0.58 and b = 0.08... and yet `(a-b) &gt;= 0.5` is false.  I&#39;m aware of the dangers of using `==` for exact number comparisons, and this seems related:

`(a - b) == 0.5)` is false, while 

`all.equal((a - b), 0.5)` is true.

The only solution I can think of is to have two conditions: `(a-b) &gt; 0.5 | all.equal((a-b), 0.5)`.  This works, but is that really the only solution?  Should I just swear off of the `=` family of comparison operators forever?


**Edit for clarity:** I know that this is a floating point problem.  More fundamentally, what I&#39;m asking is: what should I do about it?  What&#39;s a sensible way to deal with greater-than-or-equal-to comparisons in R, since the `&gt;=` can&#39;t really be trusted?

Numeric comparison difficulty in R

Comparing two instances of the following struct, I receive an error:

    struct MyStruct1 {
        MyStruct1(const MyStruct2 &amp;_my_struct_2, const int _an_int = -1) :
            my_struct_2(_my_struct_2),
            an_int(_an_int)
        {}
    
        std::string toString() const;
    
        MyStruct2 my_struct_2;
        int an_int;
    };

The error is:

&gt; error C2678: binary &#39;==&#39; : no operator
&gt; found which takes a left-hand operand
&gt; of type &#39;myproj::MyStruct1&#39; (or there
&gt; is no acceptable conversion)

Why?


No == operator found while comparing structs in C++

Looking into Queue.py in Python 2.6, I found this construct that I found a bit strange:

    def full(self):
        &quot;&quot;&quot;Return True if the queue is full, False otherwise
        (not reliable!).&quot;&quot;&quot;
        self.mutex.acquire()
        n = 0 &lt; self.maxsize == self._qsize()
        self.mutex.release()
        return n

If `maxsize` is 0 the queue is never full.

My question is how does it work for this case? How `0 &lt; 0 == 0` is considered False?

    &gt;&gt;&gt; 0 &lt; 0 == 0
    False
    &gt;&gt;&gt; (0) &lt; (0 == 0)
    True
    &gt;&gt;&gt; (0 &lt; 0) == 0
    True
    &gt;&gt;&gt; 0 &lt; (0 == 0)
    True


Why does the expression 0 &lt; 0 == 0 return False in Python?

I was wondering if there is any way to detect if a number is negative in PHP?

I have the following code:

```php
$profitloss = $result-&gt;date_sold_price - $result-&gt;date_bought_price;
```

I need to find out if `$profitloss` is negative and if it is, I need to echo out that it is.

Content Type	Original Author	Original Content on Stackoverflow
Question	abatishchev	View Question on Stackoverflow
Solution 1 - Sql	Rockcoder	View Answer on Stackoverflow
Solution 2 - Sql	George Mastros	View Answer on Stackoverflow
Solution 3 - Sql	Ramil Aliyev	View Answer on Stackoverflow

Using comparison operators in SELECT clause of T-SQL query

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

How to visualize a project structure in MATLAB?

return value of operator overloading in C++

Attributions