Tag: sql-server,prolog
I am trying to query the MS SQL Server database using Prolog.
Prolog Code
lemmas:-
odbc_query('my_db',
'SELECT * ,case
when ActualCost<EstimatedCost then 'true'
else 'false'
end as Value
from Work_Order '
).
It gives an error that operator is expected after 'true' .
Note: The Query works in SQL-Server but it doesn't work in Prolog
Try:
lemmas:-
odbc_query('my_db',
'SELECT * ,case \
when ActualCost<EstimatedCost then \'true\' \
else \'false\' \
end as Value \
from Work_Order '
).
It's quite simple. A table type just can be used inside it's database. You try to access it like that: [test].[dbo].[tvf_id] Instead try this: [dbo].[tvf_id] As your error already stated, you exceed the maximum of allowed prefixes. 1 is allowed (dbo) and you use two (test + dbo). For further...
this will work. you have to provide separate case statement to each condition SQLFIDDLE for the same SQLFIDDLE SELECT EMP_NO, sum(CASE WHEN Emp_Shift = 'AL' THEN 1 ELSE 0 END) AS COUNT_AL, sum(CASE WHEN Emp_Shift = 'S' THEN 1 ELSE 0 END) AS COUNT_S, sum(CASE WHEN Emp_Shift = 'H' THEN...
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 ...
That should give you the latest start_date to every entry which has the same value in element like the defined value in update statement. So you need to change it only at one place. UPDATE [table] t SET t.end_date = NULL WHERE t.element = 1 AND t.start_date = (select max(sub.start_date)...
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...
Building on the second half of Gordon's answer, you also need to add the condition of your WHERE clause to the join between Days and VendorBillUVBLog. with days as ( select cast(getdate() as date) as dd union all select cast(getdate() - 1 as date) as dd union all select cast(getdate()...
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...
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-server,hadoop,sql-server-2012
Are you sure Polybase is installed and enabled? You should have installed it during the SQL Server installation process and enable the according services....
Since you are using your subquery in JOIN, your column needs to have a name: SELECT rollno, classid, t_class.classname FROM t_class LEFT JOIN(SELECT Count(classname) as CountOfClasses, classname FROM t_class GROUP BY groupname HAVING Count(classname) > 1)tbl ON tbl.classname = t_class.classname But since you are not using this aggregate field in...
Try this query select u.ID, u.Name, case when p.value>0 then 'True' else '' end as IsPermissionEnabled from Users u left join permission p on p.UserID = u.ID and p.key='CanEdit' ...
INSERT INTO tbl2 ( Name ,parentId ) SELECT DISTINCT manager ,0 FROM tbl1 WHERE manager NOT IN ( SELECT employee FROM tbl1 ) INSERT INTO tbl2 SELECT DISTINCT employee ,0 FROM tbl1 UPDATE tbl2 SET parentid = parent.id FROM tbl2 INNER JOIN tbl1 ON tbl2.Name = tbl1.employee INNER JOIN tbl2...
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' ...
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...
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';...
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,...
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"))...
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....
sql,sql-server,tsql,while-loop
So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...
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-server,asp.net-mvc-4,razor
Note : As per your requirement you need to show country name when user selects the state then why you need dropdownlist for country ?? it is better to use a label for that. For you requirement first you have to maintain a table which stores country and it's state...
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...
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...
Better to add ID in you Calender table so your schema should be something like this Note : in EmployeeDate table you dont need to add FirstName or LastName because you have EmployeeID which references to Employee table where you can easy get those fields. And your query will be...
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) ...
sql,sql-server,sql-server-2008,tsql,stored-procedures
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl2')) BEGIN -- tbl2 exists, so just copy rows INSERT INTO tbl2 SELECT * FROM tbl1; END ELSE BEGIN -- tbl2 doesn't exist, so create new table tbl2 and copy rows SELECT * INTO tbl2 FROM tbl1; DROP tbl1; END This...
sql-server,vb.net,authentication,connection-string
You need to add Integrated Security=SSPI and remove username and password from the connection string. Dim ConnectionString As String = "Data Source=Server;Initial Catalog=m2mdata02;Integrated Security=SSPI;" ...
Try this query RESTORE DATABASE Databasename FROM DISK = 'Z:\SQLServerBackups\tms.bak' ; ...
You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some...
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...
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...
SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...
sql,sql-server,sql-server-2008,sql-server-2008-r2
You can solve this for example using the DENSE_RANK() function. See the example below: CREATE TABLE #yourTable(col1 int, col2 int) INSERT INTO #yourTable(col1,col2) VALUES(null, 72),(null, 72),(null, 72),(null, 33),(null, 33),(null, 12),(null, 12),(null, 55),(null, 72) SELECT * FROM #yourTable -- Your part: UPDATE yt SET col1 = numbering.number FROM #yourTable yt INNER...
Check for constraints or triggers that would attempt to insert a value too large for a given column. This can happen when over time schema changes occur, and constraints or triggers escaped the scope of impact review. In this case (varchar(3)) column status had a default constraint that was attempting...
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...
Edit In hindsight, this problem is a running partitioned maximum over Column1 * 2. It can be done as simply as SELECT Id, Column1, Model, Product, MAX(Column1 * 2) OVER (Partition BY Model, Product Order BY ID ASC) AS Column2 FROM Table1; Fiddle Original Answer Here's a way to do...
You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....
mysql,sql,sql-server,database,stored-procedures
The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...
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....
you need to use TOP: Update tblTempChek Set TmpCheckIn='15:50:03' Where TempID in ( Select TOP 1 TempID From tblTempChek Where Convert(date, TmpDate)='2015-06-23' AND UserID='1' Order By TempID Desc ) ...
The error is simply occurring as your destination column can only have one data type. The first part of your CASE statement is effictively setting the column type to expect an integer, so when you hit the ELSE section and try to insert Not Same, you're getting the error. Sample:...
Type of expression in SUM determines return type. Try the following: SELECT pa.[type], (SUM(CAST(pa.Actions_Logged as BIGINT ))/SUM(CAST(pi.Impressions_Served as BIGINT ))) AS ActionRates from Performance_Actions pa INNER JOIN Performance_Impressions pi ON pa.Alternative = Pi.Alternative GROUP BY pa.[type]; ...
SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...
You have two solutions for this: As a first solution you can simply use an INSERT EXEC. This will work if you have a specified result set. This could be used if your procedure just returns one result set with a fixed result design. Simply create your temporary table with...
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...
I understand your problem. You have some column-rule. Which is globally used in all app. For example in my case there was 'status' column, and there were some logical meaning called 'important', which worked if column had one of the certain values in the set. So, everywhere, to check if...
sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
Excel Formulas support as ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services). No Formula Support in Excel In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel,...
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 (...
I think there is opportunity to rewrite your query, but for information purposes I rewrote your sql into linq verbatim. If you explain what you are trying to achieve we can provide alternative sql / linq var eqnums = new[] { "M0435", "Z0843" }; var testdate = "2008-06-01"; var query...
sql,sql-server,sql-server-2008-r2,sum
You need a table of all the statuses. If you don't already have a table, you can do this in the query itself: SELECT ClientDeliveryStatus, COUNT(t.ClientDeliveryStatus) AS Total FROM (SELECT 'Past Due' as cds UNION ALL SELECT 'Due Tomorrow' UNION ALL SELECT 'Due Today' UNION ALL SELECT 'Due Beyond' )...