sql

Create View using Linked Server db in SQL Server

How can I create View on Linked Server db. For Example I have a linked server [0.0.0.0] on [1.1.1.1]. Both db servers are SQL Sserver 2005. I want to create View on [1.1.1.1] using table on linked server. EDIT: On creating using full name, [0.0.0.0].db.dbo.table, I am getting this error. SQL Execution Error. Executed SQL statement: S...

SQL Update query for select query

i have the following query to list the employees of two table. i need to update the a.staffdiscountstartdate to '20100428' how to rewrite the following query for this? select a.employeeid, b.employeeid from tblEmployees a left join tblCards b on a.employeeid=b.employeeid where Group...

SQL Server 2008 pivot without aggregate

I have table to test score data that I need to pivot and I am stuck on how to do it. I have the data as this: gradelistening speaking reading writing 0 0.0 0.0 0.0 0.0 1 399.4 423.8 0.0 0.0 2 461.6 508.4 424.2 431.5 3 501.0 525.9 492.8 491.3 4 521.9 517.4 488.7 486.7 5 555.1 581.1 547.2 538.2 6 562.7 545.5 498.2 530.2 7 560...

Getting next row using SQL

I have 1 table with data thus: Col1 Col2 ------- -------- Admin001 A Admin001 B Admin002 C Admin002 C Admin003 A Admin003 C I need to find all instances of Col2 values with 'A' immediately followed by 'B'. 'A' followed by any other symbol does not count. Is there a way to use SQL to accomplish this? Environme...

T SQL WHERE Clause

Hiya Guys, I'm trying to develop some code which pulls latest information only. What it is when a user goes into a form there a subtable and everytime they change something it creates a new row in the colum called type12_OriginalNoteID which puts its own unique number in. Another field called type12_OriginalNoteID keeps the same number...

Counting records in C# using LINQ

I have a simple SQL query: Select ID, COUNT(ID) as Selections, OptionName, SUM(Units) as Units FROM tbl_Results GROUP BY ID, OptionName The results I got were: '1' '4' 'Approved' '40' '2' '1' 'Rejected' '19' '3' '2' 'Not Decided' '12' I have to encrypt the data in the database, and as such am unable to sum the data in relati...

Separating date ranges into separate segments in SQL

I have a table containing 2 date fields and an identifier (id, fromdate and todate) These dates overlap in any and every possible way. I need to produce a list of segments each with a start and end date describing the separate segments in that list. For example: id, FromDate ToDate 1, 1944-12-11, 1944-12-31 2, 1945-01-01, 1945-...

concatenating strings from two different rows in a table

Hello, We are attempting to rework the SQL in a product. The product stores XML in a table as follows: XML_STORAGE - UID IDENTITY - PARENT_ID INTEGER - SEQ INTEGER - XML VARCHAR(3800) The current way of doing this is as follows: Retrieve all ROWS for PARENT_ID = n. Then go over the fetched rows in the code and concatenate the X...

Why would I do an inner join on a non-distinct field?

I just came across a query that does an inner join on a non-distinct field. I've never seen this before and I'm a little confused about this usage. Something like: SELECT distinct all, my, stuff FROM myTable INNER JOIN myOtherTable ON myTable.nonDistinctField = myOtherTable.nonDistinctField (WHERE some filters here...) I'm not quit...

testing database application remotely

With advice from users here I was able to deploy an application that connects with sql server 2008 database on to a server. I have the connection string with data source pointing to my machine since the database is stored on my machine temporarily. I do not have access to another machine and wanted to test the application so I remotely c...

How can I update a column in a table with the result of a select statement that uses row being updated as part of the where clause?

This SQL statement example is very close to what I think I need... update table1 set value1 = x.value1 from (select value1, code from table2 where code = something) as x However, what I need to do is change the "something" in the above example to a value from the row that is being updated. For example, I tried this ...

T-SQL query and group by reporting help

So I have some data that looks like this. `USERID1 USERID2` 1 10 2 20 2 30 3 40 3 50 1 10 2 20 2 30 3 50 I want a query that produces the following `USERID1 COUNT` 2 2 3 2 It's a group by query that shows me ...

(Oracle Performance) Will a query based on a view limit the view using the where clause?

In Oracle (10g), when I use a View (not Materialized View), does Oracle take into account the where clause when it executes the view? Let's say I have: MY_VIEW = SELECT * FROM PERSON P, ORDERS O WHERE P.P_ID = O.P_ID And I then execute the following: SELECT * FROM MY_VIEW WHERE MY_VIEW.P_ID = '1234' When this executes, does ora...

Group and Concatenation aggregate to produce binary string SQL

Scenario: I have a table that is used for storing a user's security on different PO series. And currently selecting a user's security produces the following record set. each permission column has a boolean value and i would like to group each set on Job, SubJobNum and get a binary result (string if need be) that would result in the fo...

Delete from empty table taking forver

Hello, I have an empty table that previously had a large amount of rows. The table has about 10 columns and indexes on many of them, as well as indexes on multiple columns. DELETE FROM item WHERE 1=1 This takes approximately 40 seconds to complete SELECT * FROM item this takes 4 seconds. The execution plan of SELECT * FROM ITEM ...

Where can I find an introduction to using PDO?

I know my SQL safety isn't up to par. I've read on this site that PHP PDO would be a good first step, and while I did take a look at the PDO Manual it's a bit daunting. Is there somewhere I can find a tutorial on the basics of using PDO rather then straight MySQL calls? ...

A query for date within a year

My table is like this on Postgres, note that all days start by 01, there is only 1 entry a month+year SELECT * FROM "fis_historico_receita" +----+------------+---------------+ | id | data | receita_bruta | +----+------------+---------------+ | 1 | 2010-02-01 | 100000.0 | | 2 | 2010-01-01 | 100000.0 | | 3 | 2009-12-01...

Can I do this with just SQL?

At the moment I have two tables, products and options. Products contains id title description Options contains id product_id sku title Sample data may be: Products id: 1 title: 'test' description: 'my description' Options id: 1 product_id: 1 sku: 1001 title: 'red' id: 2 product_id: 1 sku: 1002 title: 'blue' I need to disp...

Number Sequence in MySQL

In Python if I wanted a sequence from 0 - 9 (inclusive) I would use xrange(0,10) . Is there a way I can do this in MySQL? ...

How can I turn a column name into a result value in SQL Server?

I have a table which has essentially boolean values in a legacy database. The column names are stored as string values in another table so I need to match the column names of one table to a string value in another table. I know there has to be a way to do this directly with SQL in SQL Server but it is beyond me. My initial thought was t...