sql

SQL table function or means to tell if identity column has been used?

I was curious if there is a way to tell if an IDENTITY column has ever been incremented if there is no data within the table. (i.e. data item was inserted, then deleted) ...

Why is this (non-correlated) subquery causing such problems?

I've got a large query where a simple subquery optimization dropped it from 8 minutes down to 20 seconds. I'm not sure I understand why the optimization had such a drastic effect. In essence, here's the problem part: SELECT (bunch of stuff) FROM a LEFT OUTER JOIN b ON a.ID = b.a LEFT OUTER JOIN c ON b.ID = c.b ... ... I...

Merge rows of almost duplicate data in BIRT?

see the table here: http://www-01.ibm.com/support/docview.wss?uid=swg21376096 what i'm trying to do in birt designer is merge those two rows, and combine the Product column cells, since everything else is the same. end result would be a product cell of "IBM Tivoli Asset Management for IT, IBM Maximo Asset Management Essentials" Would t...

return value from stored procedure

Hello I have a stored procedure like this: Select @totalMoney*@bonusPercent/100 How i can return value from procedure and assign it to the varible? i mean something like: SET @myVarible = EXEC MyStoredProcedure @param1, @param1; ...

Mysql query help with where clause

Community Service Table |student name (id in real table)|hours|year|event name (id in real table)| |Johnny Smith | 5|2010|Beach Clean-up | |Samantha Bee | 3|2011|Daily Show Volunteering | |Samantha Bee | 2|2011|Daily Show Bake Sale | |Bilbo Baggins...

Appending one sql query with another.

Hi, I have a datareader that binds a sql select (with 10 columns from table1) , i want to append another with 5 different cols from table2 to this first sql select, i can't do UNION as it has different number of columns, (one query has 10, another returns 5 cols). Is there any other way of implementing this, via mysql? Also i need to ...

SQL: Need to remove duplicate rows in query containing multiple joins.

Note that I'm a complete SQL noob and in the process of learning. Based on Google searches (including searching here) I've tried using SELECT DISTINCT and GROUP BY but neither works, likely due to all of my joins (if anyone knows why they won't work exactly, that would be helpful to learn). I need data from a variety of tables and belo...

What's a good way to filter data based on values that match the right of the decimal place?

Let's say I have the following data in a decimal (18,9) identity column in a database table: ID ======== 1000.00 1001.23 1002.00 1003.23 I want to write a SQL query that returns only those records that have .230000000 for the right side of the decimal place, such that only the following are returned: 1001.23 1003.23 I know I could ...

MySql: Ordering results by number of matches in a space-delimited value column

I have a MySQL database table that has a "word" column with space-delimited values. For example: one two three four five So given the record above, I want to find other records that contain one or more of those space-delimited values, and order the results by most number of relevant matches. How can I go about achieving this? For e...

Dynamic SQL sp_executesql problem

I have the following query: DECLARE @sync_table_name nvarchar(500) SET @sync_table_name = 'ContactTypes' DECLARE @sync_batch_size bigint SET @sync_batch_size = 2 DECLARE @sync_last_received_anchor timestamp SET @sync_last_received_anchor = 1 DECLARE @sync_max_received_anchor timestamp SET @sync_max_received_anchor = 18732866 DECLARE...

How to move tables from one sql server database to another?

We have a database that has grown to about 50GB and we want to pull out a certain set of tables (about 20 of them) from within that database and move them into a new database. All of this would be on the same SQL Server. The tables that we want to pull out are about 12GB of space (6GB data, 6GB indexes). How can we move the tables fro...

getting data from 3 tables via a recurring id

I have a database with 3 tables in this structure, CREATE TABLE `mailers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mailer_title` varchar(150) NOT NULL, `mailer_header` varchar(60) NOT NULL, `mailer_type` enum('single','multi') NOT NULL, `introduction` varchar(80) NOT NULL, `status` enum('live','dead','draft') NOT NULL, ...

Edit text in columns

Just wondering if someone can assist me with the following issue. I have a database in MySQL and I'm using PHPMyAdmin to interact with the database. So within the table example_table_name I have two columns product_item_short and product_item_long which have URL's in them. Now there is 3000 rows of data and as an example the URL in eac...

How can I create a odbc layer on top of existing business data?

I want to expose our internal bussiness data through odbc driver. One of the propitary product that i found is DataDirect OpenAccess. I want to use SQL to query live data from our data source. Writing my own SQLEngine will take ages but i need something like following diagram. We intend to use it on live data so exporting it to database ...

How can you represent inheritance in a database?

I'm thinking about how to represent a complex structure in a SQL Server database. Consider an application that needs to store details of a family of objects, which share some attributes, but have many others not common. For example, a commercial insurance package may include liability, motor, property and indemnity cover within the same...

TSQL: Get Last Queries Ran

Is there a way to get the SQL text for the last few queries? I am using Microsoft SQL Server 2005 ...

Getting all articles with a GROUP_CONCAT field of their tags

Hi everyone, I have a table articles, another tags, and a third called article_tags. I want to produce a page which lists all of the articles for a specific tag. My query looks like this: SELECT headline, GROUP_CONCAT(tags.tag_name) AS all_tags FROM articles LEFT JOIN articles_tags ON articles.article_id = articles_tags.article_id LEF...

Tsql Union Query is breaking sorting in my asp.net gridview

Hello All, I have a stored procedure that works fine on its own. A recent requirement has made me think a Union query will accomplish what I need. Here is the working version. It's uses the ROW_NUMBER() to accomplish paging and sorting correctly SELECT x.TicketID, x.TicketNumber, x.AccountID, x.SkillID FROM ( SELECT ROW_NUMBER() O...

SSIS sometimes inserts fewer records with the same source data set

I have a table with 63,506 records. After running through a fairly complicated dataflow, the number shown flowing to the SQL Server Destination always matches my initial record count, yet SOMETIMES, not all records are inserted into my destination table. The flow always completes 'successfully', but it's only truly successful maybe half ...

Setting a variable with select inside coalesce

How do I fix up this part of my stored procedure? The select will either return 1, 0, or null. If the select returns 1 or 0, I want @override to be set to that value. If it returns null, then I want @override to be set to 1. Something is wrong with my syntax; I am told "incorrect syntax near 'select'" and "incorrect sytax near ')'". ...