sql

Need multiple copies of one resultset in sql without using loop

Following is the sample data. I need to make 3 copies of this data in t sql without using loop and return as one resultset. This is sample data not real. 42 South Yorkshire 43 Lancashire 44 Norfolk Edit: I need multiple copies and I have no idea in advance that how many copies I need I have to decide this on the basis of dates. Dat...

SQL natural join POSTGRES

I'm not sure what kind of join I need as I'm not familiar with trying to overlap data in such a way or if it's even feasible. I have two tables which both share a similar set of data and are both related to a 3rd parent table thru Room_id. I have a table called Room_rates which stores average prices for each room (room_id) +-------+--...

LINQ or DataTable when number of fields unknown.

Hi, I have a dynamic query which returns a DataTable. This DataTable will contain a varying number of columns depending on the selection used in the query. I want an efficient way of filtering the records in the DataTable. For Example The DataTable contains columns : A, B, C and D I need to query this data at serveral points in my ...

What is the linq equivalent to the SQL IN operator

With linq i have to check if a value of a row is present in a array. The equivalent of the sql query: WHERE ID IN (2,3,4,5) How can i do? thanks ...

Oracle trigger failed -ORA-04098

I have a table for which i have written a trigger: CREATE OR REPLACE TRIGGER ac01_control_trigg AFTER INSERT ON ac1_control_test FOR EACH ROW DECLARE BEGIN IF :NEW.cur_pgm_name = 'LSN' AND :NEW.nxt_pgm_name ='MD' AND :NEW.file_status='RD' THEN INSERT INTO ac1_control_test (FILE_NAME, FILE_PATH,FILE_STATUS,CUR_P...

LEFT JOIN or REGULAR JOIN, and how to compare MySql table to an array? Need performance!

I have six tables: t1, t2, t3, t4, t5, t6. And I also have one main table: main_table. TOTAL 7 TABLES! Now, I am using SOLR for the searching of classifieds, and the results from solr are put into an array. The array results are ID:nrs which I use to match agains the same ID:s in MySql. The first column of ALL tables in MySql is called...

When should I nest PL/SQL BEGIN...END blocks?

I've been somewhat haphazardly grouping subsections of code in BEGIN...END blocks when it seems right. Mostly when I'm working on a longer stored procedure and there's a need for a temporary variable in one spot I'll declare it just for that portion of the code. I also do this when I want to identify and handle exceptions thrown for a ...

SQL Server UPDATE from SELECT

Thanks in advance guys. Been searching quite a bit for this and I'm either searching badly, or there isn't much out there to explain it. In SQL server you can insert into a table using a select statement. INSERT INTO table(col,col2,col3) SELECT col,col2,col3 FROM other_table (in this case is a temp table) WHERE sql = 'cool' Would be...

Year to date per month

Hi all, I have this table called Sales: ID Date DepartmentID Amount 1 10-12-2009 12 10 2 18-01-2010 3 23 3 08-02-2010 4 7 ... Now I need to retrieve YTD values from the Amount column for each month and department. First I tried this query: SELECT MonthSales.[Month], MonthSale...

Consolidating subsets in a table

I have a table in SqlServer 2008 with data of the form UserID StartWeek EndWeek Type 1 1 3 A 1 4 5 A 1 6 10 A 1 11 13 B 1 14 16 A 2 1 5 A 2 6 9 A 2 10 16 B ...

SQL 2008 Reporting Services "HTTP Error 503. The service is unavailable."

SQl 2008 Reporting Services (SP1, CU4) installed on Windows 2008 R2. Service account is configured to use Network Service. Port 80 is open in the Firewall. Continue to get "HTTP Error 503. The service is unavailable." error no matter what i try. Need advice on what else I can check or where I can look for any errors with more detail besi...

TSQLConnection connection editor dialog at run time ?

If I double click a TSQLConnection at design time I get a dbExpress connections dialog allowing me to select connections or create new ones. Is there anyway to use that connections dialog at run time ? I would love to present it to my users so they can select a connection. That particular dialog looks more useful to me than PromptDataS...

php time function

what is wrong with this sql query? he is crying on NOW() :( ("INSERT INTO " . PREFIX . "messages (from, to, title, message, date) VALUES (" . $from . ", " . $to . ", " . $subject . ", " . $message . ", NOW())"); ...

NHibernate Subquery Linq - How to select groups that contain a certain item by id

Short Version This query works in the database but fails with Linq To NHibernate. Why? var items = (from g in db.Find<DataGroupInfo>() where (from d in g.data where d.Id == dataID select d).Count() > 0 select g).ToList(); Detailed Long Version I have two objects mapped by NHibernate Automapper with a Ma...

Transfer permissions from one domain to another in SQL Server

At the bottom of most of our stored procedures we have a grant similar to GRANT EXECUTE ON [dbo].[uspFOO] TO [DOMAIN\SQLServerUsers] Luckily for me, our domain is changing and we now need to go through and change the permissions. Does anyone know of an easy way to do this using the DB metadata so I can pull out all the places where ...

TSQL: query with optional join

Hi I have a search ui with 3 all optional search criteria. 2 of them are simple criteria for a where statement, that I should be able to solve with this: http://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters. The last criterion is using full text search where I join the result from ContainsTable. Is ...

Mysql query problem

I have tables: users with the fields id, premium votes with the fields userid, date. How do I select ALL THE VOTES from ALL USERS FROM TODAY only when premium = 'yes' ? ...

Details of impact of different locking schemes in databases?

I'm testing different locking schemes for a big table, more than 4 million rows and expected to grow up to 10 million. My experience with SyBase: ALLPAGES, extremely slow BCP (in) and update with a field that belongs to the Primary Key. DATAROWS, fast BCP, fast update for fields in the PK, fastest select. DATAPAGES, fastest BCP and upda...

SQL: Select SUM of all children records recursively

I have a table that has a one to many relationship with itself. Each record can have n number of children from that same table. For example create table folder ID: Number 20 PK PARENT_ID: Number 20 FK references folder.ID SIZE: NUMBER 20 ... Given an ID, I want to select the SUM(SIZE) of all folder records recursively. The target d...

Using rank to select top 10 tuples in Oracle SQL

I have the relation instructor(ID, name, dept name, salary). The question in our assignment asks us to: Use the rank function in SQL to write a query to nd the id and name of those instructors in the top 10 most highly paid. I'm able to rank the instructors through using select id, name, rank() over(order by(salary) desc) as sal from i...