oracle

[Solved] How to write a query, to produce the desired result?

by

You can use DENSE_RANK(). For example: select id, name, quantity from ( select id, name, quantity, dense_rank() over(order by quantity desc) as rk from t ) x where rk <= 2 DENSE_RANK() computes a number for each row according to an ordering of your choosing. Identical values get the same number, and no numbers are … Read more

[Solved] Double the current time [closed]

by

If you really what to work only with the time element, the easiest way to so is to employ the ‘SSSSS’ date mask, which returns the number of seconds after midnight. So this query will return double the number of seconds between the two columns: select ( to_number(to_char(atn_inn, ‘sssss’)) – to_number(to_char(acn_inn, ‘sssss’)) ) * 2 … Read more

[Solved] Condition statement in a select

by

Select X, Y, NVL(Z, showThis) as Z will return showThis if Z is null in ORACLE Select X, Y, ISNULL(Z, showThis) as Z will return showThis if Z is null in SQL-SERVER 0 solved Condition statement in a select

[Solved] ORDER A TABLE BY TWO COLUMNS

by

A partial solution might be SELECT . . . customer.customer_corporate_name, isnull(customer.siren_corp ,Replace(customer.comm_regnum_cust, ‘ ‘, ”)) AS SiretSiren . . . FROM customer JOIN customer_status . . . ON customer_status.status_id = status.status_id ORDER by customer.customer_corporate_name,SiretSiren solved ORDER A TABLE BY TWO COLUMNS

[Solved] How to select sub string in oracle?

by

Using substr: declare l_start number := DBMS_UTILITY.get_cpu_time; begin for i in ( with t as ( select ‘Chapter ‘ || level || ‘ Unit ‘ || level || ‘ Sect ‘ || level d from dual connect by rownum < 100000 ) select substr(d, 1, instr(d, ‘ ‘, 1, 2) – 1) chapter , substr(d, … Read more

[Solved] FOR LOOP IN PL/SQL BLOCK [closed]

by

“Now I am getting 9 rows” The outer loop steps through an array of three elements. The nested loop steps through an array of three elements for every element in the outer loop. 3 * 3 = 9. “how to get ‘AA’ ‘BB’ ‘CC’ from below query” Apply Occam’s razor and discard the inner loop: … Read more

[Solved] Raise statement

by

No. The block as a whole will get rolled back on failure, but the raise statement on its own does not perform a rollback. For example, this block fails and is implicitly rolled back (exactly as if it was an SQL insert etc): begin insert into demo(id) values(1); dbms_output.put_line(sql%rowcount || ‘ row inserted’); raise program_error; … Read more

[Solved] Show columns of current year and previous year in oracle

by

SELECT grade, COUNT( DISTINCT CASE WHEN DATE ‘2015-01-01’ >= date_column AND date_column < DATE ‘2016-01-01’ THEN customer_id END ) AS number_of_unique_customers_in_2015, COUNT( DISTINCT CASE WHEN DATE ‘2016-01-01’ >= date_column AND date_column < DATE ‘2017-01-01’ THEN customer_id END ) AS number_of_unique_customers_in_2016 FROM Customers WHERE Date_Column >= DATE ‘2015-01-01’ AND Date_Column < DATE ‘2017-01-01’ GROUP BY grade; … Read more

[Solved] SQL BEGINNERS QUERY

by

This will give you all the subs where the Mem and Sub are not the same. SELECT SUB FROM TABLE WHERE MEM <> SUB This will give you a distinct list of Sub’s that, at one point or another, do not have a matching MEM. SELECT DISTINCT SUB FROM TABLE WHERE MEM <> SUB EDIT: … Read more