Hi All, I have Created a Procedure for one of our .Net Devs where they can pass in values that will then build and output a result set. Part of the params they pass in will determine which table gets called. My question is how can I ensure that the dynamic sql statement I am building will get cached for faster execution? None of the c...
I'll illustrate what I would like to get in the following example: '2010-09-01 03:00:00' - '2010-09-01 00:10:00' Using TIMEDIFF(), we get 2 as a result. This means, it's not considering the 50 minutes left. In this case, what I'd like to get is: 50 (minutes) / 60 = 0.83 period. Therefore, the result should be 2.83 and not 2. ...
Given a the following table: Index | Element --------------- 1 | A 2 | B 3 | C 4 | D We want to generate all the possible permutations (without repetitions) using the elements. the final result (skipping some rows) will look like this: Results ---------- ABCD ABDC ACBD ACDB ADAC ADCA ...
Hey, I'm working with third party user data that may or may not fit into our database. The data needs to be truncated if it is too long. We are using IBatis with Connector/J. If the data is too long a SQL exception is thrown. I have had two choices: either truncate the strings in Java or truncate the strings in sql using substring. I...
For examples I don't know how many rows in each table are and I try to do like this: SELECT * FROM members UNION SELECT * FROM inventory What can I put to the second SELECT instead of * to remove this error without adding NULL's? ...
i have sql that uses FOR XM RAW to generate xml for my asp.net application to use to bind grids to and things like this. the problem is that the date data is not taking the gridView formatting of the date because (and im taking a crack at this) the date value in the xml is a string and the formatting is not taking. any thoughts on how ...
Let's say that I have three tables "Person", "Area", and "Person_Area". Each person can work in many areas and each area can have many people in them. "person_Area" is a bridge table that contains person_id and area_id In my code I have two asp list boxes located on a person form. List box one contains all available areas and list b...
First up, apologies for the awful title I couldn't think of a better way to articulate my issue. (Feel free to suggest better altnernatives) Basically I have a table with a "count" column. I want to reset all counts to zero except for the 10 rows with the top values. I want them to be reset to 0. How do I achieve this without writing ...
Suppose I have two tables: CREATE TABLE A( id INT PRIMARY KEY, x INT, y INT ) CREATE TABLE B( id INT PRIMARY KEY, x INT, y INT, ) Table A contains data brought in from another vendor while table B is our data. For simplicity, I've made these tables be exactly the same in terms of schema, but table B would lik...
Hello, I need to store an retrieve a vector of an unknown number of objects in an android sqlite database. Essentially, the setup is this: I am developing a task management app, where the user can add as many notes as they like to their tasks. My current setup uses one database, with one row per task. This presents a problem when I need...
This seems so simple, but I just can't figure it out. I want to simply join 2 tables together. I don't care which values are paired with which. Using TSQL, here is an example: declare @tbl1 table(id int) declare @tbl2 table(id int) insert @tbl1 values(1) insert @tbl1 values(2) insert @tbl2 values(3) insert @tbl2 values(4...
Basically I am trying to make a simple form that allows the user to edit data in a table. It pulls the data from the database and uses those variables to prefill the form. Then the user can just edit the info in the form and resubmit the data. The problem is... the state selection is a function that auto creates the drop down box in t...
I'm looking to find out what kind of 'create table' statement I would need to create a given table from scratch. I'm especially interested in primary key constraints, unique constraints, foreign key constraints and column names. How would I do this in Oracle and/or SQL Server? Oddly enough, the only connection I have to the database i...
Lets say I have a table that has a bit column named Active. By default, the column will contain a value of false for every row except one. When I choose to use a gridview to update a new row and have its 'Active' column change from false to true... How can I modify the following update command to update all previous rows to false when ...
The DB resides in SQL Server 2008 ... I have a db that resides in a different country, i want the tables DateCreated and DateUpdated to have Date and Time of e.g. Canada/ON/Toronto time zone instead of the time zone of US where db is located. DateCreated has a default value GetDate() and DateUpdated has the following trigger: CREATE T...
I have a db with many tables. Some tables have unit price column, no tax, gst and such columns. What should i do now? Should i create GST table, HST table and PST table separately. In other words, what is the standard schema of designing the tax table? ...
I have an MPTT tree of over 100,000 records stored in MySQL using lft, rght and parent_id columns. Now the left/right values became corrupted, while the parent ids are still intact. It would require tons of queries to repair it in the application layer. Is there a good way to put the burden on the database and have it recalculate the lef...
dear all..i have some data at DB. name Range bla 123x9901-123x0000 //it means range 9901 until 10000 = 100 bla 123v0001-123v0100 // 10001-10100 = 100 i want the result like: name Qty bla 200 i counting them use: SELECT................ IF(RIGHT(Range,4) = "0000",10000,RIGHT(Ra...
I have a procedure, where I have to check a certain view for some specified entries and delete them accordingly. I have used the following approach for this purpose - SELECT id_1, id_2, id_3, id_4 INTO v_id_1, v_id_2, v_id_3, v_id_4 FROM v_doc...
Hi! I started to use SQL for a few days, so i'm just a beginner. I want to do the following: I want to run a query in everey month, which gives back the data i need, then store it in an .xls file or something else, and then send it to some recipients. Can i do this fully automatically somehow in SQL Server 2005? Could someone give me an...