New answers tagged

0

Assuming cliente is connected to conta via depositante, then it sounds like you want: SELECT nome_cliente, saldo FROM cliente INNER JOIN depositante ON client.nome_cliente = depositante.nome_cliente INNER JOIN conta ON depositante.numero_conta = conta.numero_conta WHERE conta.saldo >= 200 AND conta.saldo <= 1000;


0

You have three cursors here, but one is implicit in the for loop. Your code indicates that for each row in the testnirmal table you want a column name from the table and then a row from the testnirmal table that has a literal value of that column name. It is unclear what you are trying to accomplish, but here is one interpretation: CREATE OR REPLACE ...


0

SELECT name FROM world WHERE gdp > ( SELECT Max(gdp) FROM world WHERE continent = 'Europe' )


-2

Unsure why you'd be using ALL rather than SUM. I'd be inclined to reword as follows select name from world where gdp > sum(case when continent='Europe' then gdp else 0 end)


1

I figured it out, had to group by trucn(invdate,'mm') and count the separate columns SELECT TO_CHAR(TRUNC(inv.invdate,'MM'),'MON-RR') "Month", COUNT(DISTINCT(inv.order_no)) "Orders", COUNT(inv.order_no) "Order Lines", COUNT(DISTINCT(inv.invoice_no)) "Invoices", COUNT(inv.invoice_no) "Invoice Lines", ...


1

In such case you don't have to use MERGE. Assuming data1 column can be used to check whether the record exists, the following should do the job : INSERT INTO A (data1, data2, dataX) SELECT B.data1, B.data2, B.data3 FROM B b WHERE NOT EXISTS ( SELECT NULL FROM A a WHERE a.data1=b.data1 );


1

Table1: A (DATA1, DATA2, DATA3) Table2: B (DATA1, DATA2, DATA3) Assumption: First fieldName of both table is primary key MERGE INTO A TA USING (SELECT * FROM B) TB ON (TA.DATA1 = TB.DATA1) WHEN NOT MATCHED THEN INSERT (TA.DATA1, TA.DATA2, TA.DATA3) VALUES (TB.DATA1, TB.DATA2, TB.DATA3);


0

Most probably table row interval must be within interval defined by user input ('$start_date', '$end_date'). So SELECT category FROM tablename t WHERE t.start_date BETWEEN '$start_date' AND '$end_date' AND t.end_date BETWEEN '$start_date' AND '$end_date';


0

You have to use below query for your output. SELECT category FROM table_1 WHERE start_date BETWEEN '2016-05-16' AND '2016-05-31' OR end_date BETWEEN '2016-05-16' AND '2016-05-31'


2

Simple answer: If you want both date columns to be between the input dates: SELECT category FROM tablename WHERE 'start_date' BETWEEN start_date AND end_date AND 'end_date' BETWEEN start_date AND end_date ; Or if you want any of the date columns to be between the input dates: SELECT category FROM tablename WHERE 'start_date' BETWEEN start_date AND ...


0

Here is a query that should give you what you are looking for. This is Oracle SQL syntax, but may work in other databases as well. SELECT dau.datetime, count(*) FROM DAU WHERE EXISTS (select 1 from Payer WHERE Payer.user_id=DAU.user_id AND Payer.payment_timestamp BETWEEN trunc(dau.datetime)-90 AND trunc(DAU.datetime)) GROUP BY dau.datetime ORDER BY ...


3

About 4.5 years ago, I wrote a rather aggressive stored procedure to traverse data stored in a hierarchy and bring back all descendants within that table : Find highest level of a hierarchical field: with vs without CTEs I took the code from the GetFamilyTree function and wrote it as a procedure for you STORED PROCEDURE DELIMITER $$ DROP PROCEDURE IF ...


1

Instead of 1424, perhaps you should use t4.t2id. Instead of 1046, if the parent exists in table4 or any of the others, use that link instead of the constant. You could also use a single IN, and a UNION of both queries. Finally, if you use a filter condition in WHERE on a LEFT JOINed table, that defeats the purpose of a LEFT JOIN and might result in bugs. ...


2

You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out. Drawing in all the tables as follows would work: SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2 , ct.specialAttributeA , ...


2

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...


-1

I hope I am not reading this wrong but if you want the top 10 most recent posts of a single user wouldn't it be better to run your query on the relations table and join the posts to that query? You would restrict your search to only the posts for the signle user you are attempting to look up then sort the data. So it would look like this: SELECT p.id, ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...


0

To execute dynamic sql and return the value use sp_executesql https://msdn.microsoft.com/en-us/library/ms188001.aspx


0

Try to use max(datalength(Column_name)): SELECT Column_name, table_name, max(datalength(Column_name)) as MaxLength FROM YourTable;


0

You don't need a return statement, because it is using OPEN and FOR... It is the same like RETURN... The error is in the name of the table, you put it in "" double quotes. Remove that, use simply the name of the table, and use the type SYS_REFCURSOR like this: CREATE OR REPLACE PROCEDURE ProcSelectEveryThing(cursor_ OUT SYS_REFCURSOR) AS BEGIN OPEN ...


0

SELECT * FROM animals WHERE FIND_IN_SET(name,(SELECT @animal_names)); See the FIND_IN_SET documentation


0

Build a Stored Routine; in it use CONCAT to construct the SELECT with the @variable stitched in; prepare and execute. Alternatively, if you are using an application language such as PHP, do the stitching in that language.


3

You could use some kind of row generation technique, for example: select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet) This generates as many rows as the largest range requires, with data 0, 1, 2, ..., starting with 0, so no correction needed, when we add these values to controlnofrom. Now ...


0

You could do: SELECT * FROM animals WHERE CONCAT(',', @animal_names, ',') LIKE CONCAT('%,\'', name, '\',%'); It won't perform fast, but it'll work.



Top 50 recent answers are included