I am trying to convert a working MS Access query to run on an Oracle database being accessed via VB Script (.asp). This is the last section of the WHERE clause: sql = sql & "WHERE (UAT.HB.MB_MODE = 'A' AND UAT.HB.PRINT_DATE >= '" & SD & "' AND UAT.HB.PRINT_DATE <= '" & ED &"' )" The variable "SD" (i.e. "start date") is a text str...
I have 2 tables, an active table and an inactive table. I want to move rows from the active to the inactive table. My first thought was insert into inactive select * from active where ... delete from active active where ... However about .42 seconds later I noticed this will drop/duplicate rows if updates alter what the where clause s...
Is it possible to create a parameterized SQL statement that will taken an arbitrary number of parameters? I'm trying to allow users to filter a list based on multiple keywords, each separated by a semicolon. So the input would be something like "Oakland;City;Planning" and the WHERE clause would come out something equivalent to the belo...
I've been attempting to write embedded SQL statements for DB2 that ultimately gets compiled in C. I couldn't find a tutorial or manual on the embedded SQL syntax for C for reference. One case I would like to do is to insert data into a table. I know most embedded sql statements need the initalizer EXEC SQL, but thats the extent of my k...
Hi, We were using stringstream to prepare select queries in C++. But we were strongly advised to use QUERY PARAMETERS to submit db2 sql queries to avoid using of stringstream. Can anyone share what exactly meant by query parameter in C++? Also, share some practical sample code snippets. Appreciate the help in advance. Edit: It is stri...
say I want to find the latest added rows (UPDATE by any user, not necessarily the one which is executing UPDATE) in XX table. ...
I have this SQL query: SELECT * FROM IMAGES WHERE IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6') ORDER BY CASE IMAGENAME WHEN 'IMG1' THEN 1 WHEN 'IMG2' THEN 2 WHEN 'IMG3' THEN 3 WHEN 'IMG4' THEN 4 WHEN 'IMG5' THEN 5 WHEN 'IMG6' THEN 6 ELSE 7 END I cannot guarantee that the list of IMAGENAMEs will be in alp...
This is an SQL problem I can't wrap my head around in a simple query Is it possible? The data set is (letters added for ease of understanding): Start End 10:01 10:12 (A) 10:03 10:06 (B) 10:05 10:25 (C) 10:14 10:42 (D) 10:32 10:36 (E) The desired output is: PeriodStart New Act...
I have some data of the form Key ID Link 1 MASTER 123 2 AA 123 3 AA 123 4 BB 123 5 MASTER 456 6 CC 456 I would like to be able to select in the same select all linked items matching the selection criteria, plus the linked master. For example, if I have an ID of 'AA', I...
I have a table of transactions which will occasionally have duplicate entries. If/When an admin finds these duplicate entries, they will reverse the transactions, therefore creating a negative value (but the original duplicate still remains due to regulatory requirements). I'd like to create a SQL query (and use Crystal Reports) to make...
In your workplace, where do you store your common, non-database specific scripts that you use in SQL Server? Do you keep them in .SQL scripts on the file server, do you store them in the Master database, or do you keep them in a database you defined specifically for these kinds of things? ...
Whenever I write a stored procedure for selecting data based on string variable (varchar, nvarchar, char) I would have something like: procedure dbo.p_get_user_by_username( @username nvarchar(256) as begin select u.username ,u.email --,etc from sampleUserTable u where u.username = @username...
What's the most efficient way to calculate the last day of the prior quarter? Example: given the date 11/19/2008, I want to return 9/30/2008. Platform is SQL Server ...
I was previously getting the next available autonumber used in Access by doing a simple query like so: SELECT RecordNumber, Info FROM myTABLE WHERE 0=1 This way I could create a variable to hold the currentRecord and it will use the same autonumber that Access was going to use when I was updating the row Example rs.AddNew currentRec...
I want to use a case statement in my user defined functions because I need to match on a number of terms. I could use a table for the matches but then I wouldn't be able to put it inside the Computed Column definition. This works with IF statements: CREATE FUNCTION MaraSizeNumber ( @ms varchar ) RETURNS varchar AS BEGIN IF ms ...
Parameterized Queries in .Net always look like this in the examples: SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID=@categoryid", conn); comm.Parameters.Add("@categoryid", SqlDbType.Int); comm.Parameters["@categoryid"].Value = CategoryID; But I'm running into a brick wall trying to do the following: Sql...
I have an object in SQL (A) that has a many to many relation ship with another object (B). I'm currently building an API layer dll that will allow the user to assign objects of type B into type A. Right now the user would retrieve a list of entries of type A and a list of entries of type B using different LINQ data contexts. The problem ...
When designing a lookup table (enum) in SqlServer 2005, if you know the number of entries will never get very high, should you use tinyint instead of int? I'm most concerned about performance, particularly efficiency of indexes. Let's say you have these representative tables: Person ------ PersonId int (PK) PersonTypeId tinyint (FK ...
If I have records: Row Date, LocationID, Account 1 Jan 1, 2008 1 1000 2 Jan 2, 2008 1 1000 3 Jan 3, 2008 2 1001 4 Jan 3, 2008 1 1001 5 Jan 3, 2008 3 1001 6 Jan 4, 2008 3 1002 I need to get the row (date, locatinid, account) where the row has t...
I'm trying to generate pairwise combinations of rows on based on their ids. SQLite version is 3.5.9. The table contents are the following: id|name|val 1|A|20 2|B|21 3|C|22 with table schema being: CREATE TABLE mytable ( id INTEGER NOT NULL, name VARCHAR, val INTEGER, PRIMARY KEY (id) ); Then there's the self-jo...