Hello, I want to write a stored procedure that queries XML files after I have input a certain string pattern to look for. I'm already stuck at the input parameters, consider the following XML-document. <root> <container> <element>A</element> <option>1</option> </container> <container> <element>B</element> ...
During my job, I usually have to copy rows while changing their primary key and giving them a new stamp and maybe changing the foreign key. The problem is I don't want to type all the column names while doing; insert into table_name select pk_seq.nextval, 'foreign-key', col3, col4...col51 from table_name wher...
There is a composite primary key stored in a DB that consists of a date field and a foreign key ID. Normally this would create duplicates however the date field (although it only displays the day, month, year appears to have timestamp information stored as well) My question is how to extract the timestamp information (I think using the ...
Let's assume i have three table - list,a and b table list |a_id|--|b_id|--'name' |4 |--|3 |--|foo| |1 |--|2 |--|foo| table a |id|--|name|--|order| |1 |--|a_1 |--|1 | |2 |--|a_2 |--|2 | ..................... |n |--|a_n |--|n | table b |id|--|name|--|order| |1 |--|b_1 |--|1 | |2 |--|b_2 |--|2 | ......................
Hi, I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with w...
Hello all. here is how my tables are currently setup: Dataset | - Dataset_Id - Int | - Timestamp - Timestamp Flowrate | -Flowrate_id - int | -Dataset_id - ALL NULL (INT) | -TimeStamp - TimeStamp | -FlowRate - FLoat I want to update the flowrate dataset_id column so that its ids corespond to the dataset dataset_ids. The D...
I seem to be facing a strange issue in SQL 2008. I have a query which runs fine and fast from query analyser, but times out if run through a stored procedure! The SP just starts with this query and has no other code before this query SELECT col1,col2 FROM TBL1 (nolock) INNER JOIN TBL2 (nolock) ON tbl1.col=LEFT(tbl2.col1,LEN(tbl2.col1)...
Hello everyone, I have a complex SQL query with nested 'subqueries' that I want to optimize SELECT 'likes' `type`, entry_date item_date, CASE `type` WHEN 'entry_id' THEN entry_id WHEN 'member_id' THEN f.member_id WHEN 'topic_id' THEN entry_id END AS item_id, CASE `type` WHEN 'entry_id' THEN (SELECT title FROM ex...
I am trying to export data into into a text file using bcp utility? I could n't figure out what i am doing wrong here:- Declare @Cmd nvarchar(1000) Declare @Query nvarchar(1000) Declare @Path nvarchar(500) Declare @ServerName nvarchar(500) Declare @UserName nvarchar(50) Declare @Password nvarchar(50) Declare @Delimiter char(1) SET @Pat...
Let's say I have this SQL query: declare @input varchar(20) select * from myTable where CONTAINS (myColumn, ISNULL(@input, 'replacement text')) If I try to execute this, I get an error that says "Incorrect syntax near 'ISNULL'." I know that this is the correct syntax for ISNULL. Is there a reason why the ISNULL cannot be called inside...
I need this to return an exact match while still being able to use the wild card. So when searching for 'carb' i want to return a value such as 'Racing Carb' but i do not want to return a value such as 'Racing Carburetor' This is what I have now... SELECT I.SKU, I.[Description] FROM Inventory AS I INNER JOIN MisspelledWords AS ...
I have two "date" fields that I need to join on. The first is a normal datetime in the format yyyy-mm-dd hh:mm:ss The second is a varchar(8) in the red-headed step child format mmddyyyy Now this gets painful because there is no easy way to convert to the corresponding type. There is a built-in format that is yyyymmdd but that does...
How to select a value from a table and store in a variable in my sql++ e.g select name from employee; write this query in c++ and then store the name in variable e_name ...
I need help in creating a query to get info for year selected and previous year of the year selected. Needs to be by quarter(QTR) for both years. QTR1 will always be the month 03, QTR2 month 06, QTR3 month 09, & QTR4 month 12. No computations are required or averaging. Fields/columns are YEARMONTH stored as mm/dd/yyyy with dd always bei...
I'll rephrase this. How do i execute a mysql query were i can pass an array in instead of write my own semi complex loop generating a very long sql query. I'd like to select docs.id where the doc has each of the tags in a string list. Additional note is doc_tag has id, docid, tagid and tag_name has id, tagname. This probably makes no...
I have this query SELECT id, timeOrdered, order_desc, firstname, lastname FROM `db`.`myTable` WHERE `myTable`.`customer_number` IN (100, 101, 102, 103, 104, 105) I am trying to find which are duplicate records. If the timeOrdered, order_desc, firstname, lastname are same then its dupe. What would be the query for that Thanks ...
I've been given a stack of data where a particular value has been collected sometimes as a date (YYYY-MM-DD) and sometimes as just a year. Depending on how you look at it, this is either a variance in type or margin of error. This is a subprime situation, but I can't afford to recover or discard any data. What's the optimal (eg. least...
Table A Id Name 1 Apple 2 Mango 3 Banana Table B Id Locale Name_In_Lang 1 es-ES Apple[Spanish] 1 it-IT Apple[Italian] 2 it-IT Mango[Italian] Let us say if the user requested spanish versions then the query should return all the spanish [es-ES] verions from Table B for every record in T...
I took over someones job and need to find out from where certain tables are being read. Is there a way to do that? The more information I could get about the caller the better. I am using MS SQL Server 2000. ...
I have a table with some data that looks like: id | type | timestamp | 1 | 1 | 0001 | 2 | 2 | 0002 | 3 | 3 | 0003 | 4 | 2 | 0004 | 5 | 2 | 0005 | 6 | 2 | 0006 | 7 | 2 | 0007 | 8 | 2 | 0008 | 9 | 2 | 0009 | 10 | 3 | 0010 | What I need is the 5 mos...