sql

what is this SQL select construct called?

What is the term for the select construct in the following select statement that is in bold? SELECT a.t1 as a, (SELECT b.n as b FROM b WHERE b.x = a.t1), c.t2 as c FROM a,c WHERE a.x = c.x I was explaining that this can be done in oracle but when asked what it was called, I couldn't think of any term. Is there a term for this? Or is i...

Commands out of sync when using mysqli and prepared statements

Hello, I am trying to do some calls, but the 2nd query fails with command 'Commands out of sync; you can't run this command now' error. The code looks like this: $sql="call listReport();"; $results = mysqli_query($link,$sql); $arr=array(); while($row=mysqli_fetch_array($results)) { array_push($arr,$row); } mysqli_free_result($res...

Copy rows from the same table and update the ID column

I have the following table I have inserted Product B to it and it gives me an ID of 15 Then I have the definition table which is as follows. I want to select the ProductDefinition rows where ProdID = 14 and replicate the same and insert it for ProdID = 15 like the following How to achieve this using SQL code? ...

COUNT results from SQL Query with a HAVING clause

Are you able to use COUNT in a query with a HAVING clause so that the COUNT returns the number of rows? When I try, Im getting the count of the number of times the ID shows up in the table. Here is the query: SELECT col_appid, min(col_payment_issued_date) as PayDate FROM tbl_ui_paymentstubs WHERE isnull(col_payment_amount,0) > 0...

Ordering of columns in where clause when using SQL::Abstract

I have been reading some recipes in the Perl Hacks book. Recipe #24 "Query Databases Dynamically without SQL" looked interesting. The idea is to use SQL-Abstract to generate the SQL statement for you. The syntax to generate a select statement looks something like this: my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order...

Logging data transfer time in SQL Server Profiler

I've been using SQL Server profiler quite frequently to check for redundant or badly performing queries. But is there an event (among the giant list) that allows you to log the total amount of time it takes to transfer the data from the data base to the application? This would be a very good indicator for queries that return way more d...

Why does select statement influence query execution and performance in MySQL?

I'm encountering a strange behavior of MySQL. Query execution (i.e. the usage of indexes as shown by explain [QUERY]) and time needed for execution are dependent on the elements of the where clause. Here is a query where the problem occurs: select distinct e1.idx, el1.idx, r1.fk_cat, r2.fk_cat from ent e1, ent_leng el1, rel_c r1, _tax_...

Catching Error Message from XP_CMDSHELL

I am running the following command: EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline On the Results tab I get 2 lines Could not find a part of the path '\server\directory\filename'. NULL How do I capture the first line in an error message? I tried using a Try Catch block with "SELECT @ErrorMessage = ERROR_MESSAGE()" and it doesn...

Cursors in SQL Server 2005 database

I am working with cursors and successfully executed in T-SQL and the database I use is Microsoft SQL Server 2005. My query is after I execute the cursor, the output is shown in message area. When I deallocate the cursor using deallocate <cursor name> it gets deallocated. Again I execute my cursor. Now in the message area I get this: ...

sql time functions

how can I do the analogous to SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40') in PostgreSQL, but in JDBC? thanks ...

Connecting to Oracle on Windows 7

Hi everyonoe, I have installed Oracle Client 10g 32 bit and ODAC 11g R2 on my Windows 7 machine, but I cannot see any Oracle Providers in MS ODBC Administration or when I try to created a linked server to Oralce in SQL Server Management Studio or in Visual Studio 2010. Can anyone please help me out as what to do? I can connect to Oracle...

SQL 2005 Format Question

Hi Guys I have a fully functioning query, but need to do a little formatting. One of my fields is called a route name. An example of the data in that field is "PRN L5 L7 S LAM C" Now what I need to do is firstly remove the PRN, secondly split the route into seperate columns, so column 1 would have L5, column 2 would have L7 ect.... Now...

Trying to fix SQL query with two tables.

I have the following tables: Entry EntryID - int EntryDate - datetime Hour EntryID - int InHour - datetime OutHour - datetime For each registry in the Entry table, there should be at least one (could be many) registries on the Hour table, like so: Entry EntryID: 8 EntryDate: 9/9/2010 12:31:25 Hour EntryID: 8 InHour: 9/9/2010 1...

Is combining (result sets) of "two queries with aggregate and group by" possible ?

1 Select id,count(*) as totalX FROM my_table WHERE x_factor = 1 GROUP BY id ResultSet: id totalX --------- -------------- 9 34 10 6 11 21 12 3 2 Select id,count(*) as totalY FROM my_table WHERE y_factor = 1 GROUP BY id ResultSet 2: id...

Custom Column on my select statement

Hi Guys, I'm still new to SQL (Oracle).. Basically on a table I'm selecting from, there is a time stamp, and some are valid stamps and some are not (stored on a time stamp table).. I have a function I can use in 'where' clauses to return only the valid or invalid ones. But sometimes I need to see all the records with an additional colu...

SQL Picking up a string with two spaces in a row in a LIKE clause

My database contains the following strings along with others that are similar Old Type: New Type: IRP User: dls0835 Old Type: BASE PLATE New Type: IRP User: ter2344 I am trying to not return the first string type but to still return the second string type. Notice how there is no text after the "Old Type:" in the first string and tha...

SQL database views

Hello all, I have just created a db with 8 tables that each have over 1000000 rows(data collected for every second over the summer :S) I am planning on creating views so that a week can be selected at a time. I was wondering does it go against any unwritten db rules to create several databases that just contain views (one for each wee...

MySQL select of select ?

Hi all, I'm having problems with a sql query, here's an generalization of what I'm trying to do: select oh.a as a, oh.b as b, oi.c as c, (select h.d from history h where h.id = oh.id and h.d not in ('d1', 'd2') order by h.date limit 1) as d from order_header oh join order_item oi on oh.order_id = oi.order_id where oh.state in (0,...

How to do an outer join with count based column?

Hi, What can be an efficient way for the following problem in SQL 2008? First two are input tables, using which I need to populate the 3rd(DataOut table) Basically, WDATA will have zero or more rows corresponding to each row of DataIn table. I need to populate DataOut table with all the rows, including none matched and multiple matche...

MySQL 3 table join

I have data to get listings according to their location. The locations are mapped in a location map table. The structure basically looks like: listings -id -title etc. locations -id -location_name etc. location_map -listing_id -location_id When I want to list the listings according to their location, I am using th...