Okay, so this one may be difficult but I will do my best to explain. Bear with me... I have a table with multiple columns as follows. ITEM_ID - GROUP_ID 1 - null 2 - null 3 - null 4 - 3 5 - 3 6 - 3 7 - 21 8 - 6 9 - ...
I have a list of timestamped logs and I'd like a query to return 12 booleans say whether a certain month contains any logs, for each month of the year (starting from January), i.e.: (True, False, False, True, False ..., True) I currently have the following query, which will show me all months containing data: SELECT DISTINCT(EXTRACT(...
I access several tables remotely via DB Link. They are very normalized and the data in each is effective-dated. Of the millions of records in each table, only a subset of ~50k are current records. The tables are internally managed by a commercial product that will throw a huge fit if I add indexes or make alterations to its tables in ...
I've got an SQL-script executed by SQL*Plus, that needs to run with Oracle 10g and Oracle 11g. That script gives grants on a package that does not exist before 11g: GRANT EXECUTE ON sys.dbms_result_cache TO my_user; I would like to avoid the exception on 10g, since I want to react to other exceptions in the script. One way is to u...
String sqlCheckPass = "Select * from Login where Username like @Username and Password like @Password"; SqlCommand SqlCom = new SqlCommand(sqlCheckPass, myConnection); SqlCom.Parameters.Add(new SqlParameter("@Username", sUserName)); SqlCom.Parameters.Add(new SqlParameter("@Password", sPassword)); ...
I have the following query: SELECT DISTINCT w.name, count(*) FROM widgets AS w JOIN w.entity AS e JOIN e.article AS a JOIN a.document AS d WHERE d.id IN (<document ids>) GROUP BY w.name The problem is that count(*) returns the number of widget-entity associations. Instead, what I'm looking for is the number of unique widget names, per...
Hi, I have a stored procedure which executes another procedure inside it. The second one sometimes takes a while to run but the first seems to finish before waiting for the second. This has resulted in missing data, which should have been updated by the second procedure. Is there a timeout limit within the first procedure and can this ...
Background: I have about 170 SSIS packages. A new requirement is that users from other workstations can run them from their command lines using dtexec. Question: To make this possible I'd like to set change the protection level to encrypt sensitive with password, and change the password in each package. Is there a way to automate th...
Hi, I have a parent report that generates a series of subreports. After clicking the preview button, I can see the final report built with the actual data, and I may print it or save it to file. File may be a MS Word doc, a PDF or others. Perfect. I can do that individually to each report I need. My problem: I have a base report that wil...
I'm running SQL query for MySQL server with ... where name in ("term1","term2","term3") I would like to get the list of terms from a text file with one term per line. How can I do this? What about if this list of terms will get large? 10K, for example. Will this be efficient or I should use another approach? May be temporary table an...
I've an old table like this: user> id | name | address | comments And now I've to create an "alias" table to allow some users to have an alias name for some reasons. I've created a new table 'user_alias' like this: user_alias> name | user But now I have a problem due my poor SQL level... How to join both tables to generate somethin...
How would I store the result of a select statement so I can reuse the results with an in clause for other queries? Here's some pseudo code: declare ids <type?>; begin ids := select id from table_with_ids; select * from table1 where id in (ids); select * from table2 where id in (ids); end; ... or will the optimizer do this for ...
Checking few RDBMS I find that things like SELECT COUNT (a), SUM (b) FROM TABLE are allowed (notice space between aggregate functions and parenthesis). Could anyone provide a pointer to SQL standard itself where this is defined (any version will do)? EDIT: The above works in postgres, mysql needs set sql_mode = "IGNORE_SPACE"; as ...
Hello, I kind of stuck on how to implement this query - this is pretty similar to the query I posted earlier but I'm not able to crack it. I have a shopping table where everytime a user buys anything, a record is inserted. Some of the fields are * shopping_id (primary key) * store_id * user_id Now what I need is to pull only the li...
I wanted to see if there is a cleaner and more effective way of writing the SQL statement below. (MySQL) UPDATE login SET is_admin=1 WHERE memberid = 1 OR memberid = 6 OR memberid = 10 OR memberid = 12 OR memberid = 7 OR memberid = 3; Simply want a nicer way of solving it. Optimize :) ...
Hi, I'm trying to update a field in the database to the sum of its joined values: UPDATE P SET extrasPrice = SUM(E.price) FROM dbo.BookingPitchExtras AS E INNER JOIN dbo.BookingPitches AS P ON E.pitchID = P.ID AND P.bookingID = 1 WHERE E.[required] = 1 When I run this I get the following error: "An aggregate may not appear in the...
I am trying to build an Oracle stored procedure which will accept a table name as a parameter. The procedure will then rebuild all indexes on the table. My problem is I get an error while using the ALTER command from a stored procedure, as if PLSQL does not allow that command. ...
Hi again all, I'm getting the error below for this SQL statement in VB.Net 'Fill in the datagrid with the info needed from the accdb file 'to make it simple to access the db connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data " connstring += "Source=" & Application.StartupPath & "\AuctioneerSystem.accdb" 'make the new...
I am wondering about the performance of this index: I have an "Invalid" varchar(1) column that has 2 values: NULL or 'Y' I have an index on (invalid), as well as (invalid, last_validated) Last_validated is a datetime (this is used for a unrelated SELECT query) I am flagging a small amount of items (1-5%) of rows in the table with thi...
In a MySQL query I am using the timediff/time_to_sec functions to calculate the total minutes between two date-times. For example: 2010-03-23 10:00:00 - 2010-03-23 08:00:00 = 120 minutes What I would like to do is exclude any breaks that occur during the selected time range. For example: 2010-03-23 10:00:00 - 2010-03-23 08:00:00 - ...