I wanted to know if its possible to set the values of declared variables by using a case statement. example:
CASE
WHEN fieldValue ='stringValue1' THEN SET @DeclaredVar1 = 100
END
Can someone explain to me how this statement is an exclude?
I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...
left join table, find both null and match value
Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...
Add 1 to datediff result if time pass 14:30 or 2:30 PM
Could be: SELECT reservations.customerid, DateDiff("d",reservations.checkin_date, Date()) + Abs(DateDiff("s", #14:30#, Time()) > 0)AS Due_nights FROM reservations ...
How to use OFFSET and Fetch without Order by in SQL Server
sql-server,sql-server-2012,sql-order-by,fetch,offset
By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...
Sql injected code is inserted to my database . How to remove it
You can use the fact that html code starts with symbol <. Then: UPDATE TableName SET SomeColumn = CASE WHEN CHARINDEX('<', SomeColumn) > 0 THEN SUBSTRING(SomeColumn, 1, CHARINDEX('<', SomeColumn) - 1) ELSE SomeColumn END If this is not true then we will need more information about data. May be it...
Default the year based on month value
SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...
MySQL: Select several rows based on several keys on a given column
If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...
INSERT INTO fails due to incorrect conversion T-SQL
The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done: cast('FooBar' as sql_variant) ...
Foreign key in C#
You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...
SQL Group By multiple categories
Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...
Setting time limit in SQL Query
Assuming that you want between 10:00 AM and 5:00 PM, you can use this SELECT CASE WHEN CAST(GETDATE() AS TIME) BETWEEN '10:00:00' AND '17:00:00' THEN 1 ELSE 0 END In this context, Select * from table makes no sense, unless you have a time column and want to evaluate that....
T-SQL Ordering a Recursive Query - Parent/Child Structure
sql,tsql,recursion,order,hierarchy
The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....
oracle sql error Case When Then Else
Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...
SQL Server / C# : Filter for System.Date - results only entries at 00:00:00
c#,asp.net,sql-server,date,gridview-sorting
What happens if you change all of the filters to use 'LIKE': if (DropDownList1.SelectedValue.ToString().Equals("Start")) { FilterExpression = string.Format("Start LIKE '{0}%'", TextBox1.Text); } Then, you're not matching against an exact date (at midnight), but matching any date-times which start with that date. Update Or perhaps you could try this... if (DropDownList1.SelectedValue.ToString().Equals("Start"))...
How to select next row after select in SQL Server?
The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...
Get unique row by single column where duplicates exist
SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...
Query how often an event occurred at a given time
This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...
SQL varchar variable inserts question mark
there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...
Title search in SQL With replacement of noice words [on hold]
sql,sql-server,sql-server-2008
I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...
The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument
sql,sql-server,sql-server-2008
You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...
Pull information from SQL database and getting login errors
change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...
Implement reference key in SQL Server
It's called a "one-to-zero-or-one" relationship, as one Line might be associated to zero or one TestPacks. You can implement it by using a FK that allows NULL values. CREATE TABLE TestPack (id INT, PRIMARY KEY (id)) CREATE TABLE Line (id INT, TestPackId INT NULL, FOREIGN KEY (TestPackId) REFERENCES TestPack(id)) By...
Convert AWK command to sqlite query
SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal...
TSQL - Error in stored procedure due to conversion failure
sql-server,sql-server-2008,tsql
I'd suggest doing it like that: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE INNER JOIN...
Take thousand value in SQL
SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...
Matplotlib: Plot the result of an SQL query
Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...
mysql_real_escape_string creates \ in server only not in local
Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you...
Retrieve Values As Column
If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...
SQL Customized search with special characters
sql,sql-server,sql-server-2008
Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...
ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables
If I understood correctly this should be what you're looking for SELECT A.UserName, A.[Email ID], A.[Supervisor Email ID] FROM A INNER JOIN B ON A.UserCode = B.UserCode WHERE B.ACTIVE_FLAG = 'Y' ...
Fastest way to add a grouping column which divides the result per 4 rows
sql,sql-server,tsql,sql-server-2012
Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...
SQL Multiple LIKE Statements
sql,sql-server,tsql,variables,like
WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...
SQL: overcoming no ORDER BY in nested query
Use a join instead: SELECT a, b FROM t JOIN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt on t.date = tt.date; ...
Error connecting to MSSQL using PHP
php,sql-server,pdo,odbc,sqlsrv
Change it to: $this->link = new PDO( "sqlsrv:Server={$this->serverName},{$this->port};Database={$this->db};", $this->uid, $this->pwd ); The default SQL Server port is 1433. Note the curly brackets, they allow for class variables....
Using Sum in If in Mysql
Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...
Select Statement on Two different views
Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other. Just treat two different views as like two different tables when using in SELECT Clause. SELECT vw1.a, vw2.b FROM View1 vw1 INNER JOIN View2 vw2 ON vw1.id...
Join SQL query Results and Get-ChildItem Results
sql-server,sql-server-2008,powershell
OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...
Cannot Browse in sql to backup bak file
Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...