Sitemap

Amicuk Programming Answers

Reorder with just a single Query?

-0001-11-30   Views:0

Advertisement

If I have a table like ID | Desc | Sortorder 1 One 3 4 Two 2 6 Three 1 Is there a way I can use a single query to change the Sortorder to 1,2,3 based on my ORDER BY ID without having to write a script?and a complete example SQL> create table   x as  

If I have a table like
ID | Desc | Sortorder
1 One 3
4 Two 2
6 Three 1
Is there a way I can use a single query to change the Sortorder to 1,2,3 based on my ORDER BY ID without having to write a script?

The replay answer
Advertisement
and a complete example
SQL> create table   x as
  2  (
  3  select 1 id, 'One' descr,  3 sortorder from dual union all
  4  select 4 id, 'Two' descr,  2 sortorder from dual union all
  5  select 6 id, 'Three' descr,  1 sortorder from dual
  6  )
  7  /
Table created.
SQL> select *
  2    from x
  3  /
        ID DESCR  SORTORDER
         1 One            3
         4 Two            2
         6 Three          1
SQL> merge into x
  2  using (
  3  select id
  4       , row_number() over (order by id
  5                           ) rn
  6       , rowid rid
  7    from x) x_orig
  8   on (x.rowid = x_orig.rid)
  9   when matched then
10   update set sortorder = x_orig.rn
11  /
3 rows merged.
SQL> select *
  2    from x
  3  /
        ID DESCR  SORTORDER
         1 One            1
         4 Two            2
         6 Three          3

Go to See the other 5 answers

Reorder with just a single Query?

Category:DefaultRelease time:-0001-11-30Views:130

If I have a table like ID | Desc | Sortorder 1 One 3 4 Two 2 6 Three 1 Is there a way I can use a single query to change the Sortorder to 1,2,3 based on my ORDER BY ID without having to write a script?and a complete example SQL> create table   x as  [More]

How to use multiple hierarchies for a single char in single query

Category:DefaultRelease time:2015-10-11Views:130

Hi, Is there any way that we can use multiple hierarchies for a single char in single query. I tried and it just allows me to select one hierarchy even if I use hierarchy variable. I have a requirement where user wants to see information related to a[More]

Multiple databases in a single query

Category:DefaultRelease time:2015-10-11Views:130

I need to query multiple databases in a single query. e.g. Database d1 contains table t1 with column id Database d2 contains table t2 with column id I need a query like SELECT * FROM t1, t2 WHERE t1.id = t2.id; is this possible in jdbc? if yes can an[More]

Populate Multiple Items bundled in a single query

Category:DefaultRelease time:2015-10-11Views:130

Hello, I am trying to create a report in which I have mulitple items that I want to populate. All of these use the same query but use different rows in the query to populate themselves. My question is:- Is there a way to bundle the item assignment in[More]

How to make the query in one single query? No Union please....

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have sets of around 18 queries which is finding counts in many scenarios. I wanted to have a single query for this. which diplays the result in a single row.Is there a way , I heared analytical functions are good options ,but I am new to Oracle[More]

Multiple conditin on a single column in a single query

Category:DefaultRelease time:2015-10-11Views:130

i want to select data from the table using diff conditions on a single column for eg: i need emp details like whose having empno as 5-15,19-45,50-89 ..etc this how can i give a condition in a single query we can use in or between for 2 or 3 condition[More]

All selected 'n' consecutive rows in retrieved in a single query

Category:DefaultRelease time:2015-10-11Views:130

hello, I have table T with 50,000 rows create table T (student_id number, class_id number, quiz_id number, marks number) some sample rows like INSERT INTO T VALUES (1,1, 1, 50); INSERT INTO T VALUES (2,2, 2, 40); INSERT INTO T VALUES (3,1, 3, 34); IN[More]

Is it possible to combine 2 different reports in a single Query?

Category:DefaultRelease time:2015-10-11Views:130

Dear All, What am I working at? I produced a Query for Debtors Aged Analysis which mimics the Official SAP B1 8.8 Aging Report. It ages the outstanding invoices by Posting Date ( RefDate  in JDT1) I also made another Report which ages outstanding amo[More]

Double aggregation in a single query block doesn't make any sence.

Category:DefaultRelease time:2015-10-11Views:130

How can I argue with something that apparently has been cast in stone by ANSI SQL committee? Well the answer is famous: "Search any park in any city: you'll find no statue of committee". OK, why select count(1) from ( select deptno from emp grou[More]

How to calculate the individual sums of multiple columns in a single query

Category:DefaultRelease time:2015-10-11Views:130

Hello, Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this c[More]

Tricky SQL query... how to get all data in a single query?

Category:DefaultRelease time:2015-10-11Views:130

create table employee_definition (def_id number, def_name varchar(50)); insert into employee_definition values (100, 'EMAIL'); insert into employee_definition values (200, 'MOBILE_PHONE'); insert into employee_definition values (300, 'HOME_PHONE'); S[More]

Hot
Dear All, I'm creating JDBC to RFC Sync scenario. Configured according to below link, http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/12/04/jdbc-to-jdbc-synchronous-scenario-with-rr-beans-and-without-bpm-and-rfc-lookup Sender JDBC struct [More]
I just uploaded CS4, and I noticed some strange things within Dreamweaver (DW). I'm hoping that someone can help me to figure out how to solve them: 1) When I minimize DW, the cursor acts strange when trying to use DW( i.e. scrolling, clicking on fil [More]
I'm trying to use Reminders on my iPhone 4s, iMac running Mountain Lion and iCloud. Although syncing does occur BETWEEN THESE 3 APPS, THE SORT ORDER OF THE TASKS IS COMPLETELY OUT OF SYNC. I need to sort my many to do tasks manually as priorities cha [More]
Run the following test code: public class Test {     public static void main(String[] args) {         ExecutorService executor = Executors.newCachedThreadPool();         executor.submit(new Runnable() {             public void run() {                 [More]
Hi, I have successfully installed RAC on two OEL servers with a NFS server. Surprisingly, after the installation I get the service already started. 1) lsnrctl status service "MY-DB" has 2 instance(s) ... 2) Just take look the listener.ora LLISTE [More]
I need to change our Factsheet in CRM2007. So far, I've managed to get a new view on the Factsheet and have managed to hard-code some data into it from the Get_* method for the field in the CN** class. However, my requirement is to grab some data fro [More]
Hi Friends, We have a customised PCR used in customised schema. Is there any way to find easily in which schema the PCR is used? Thanks in advance, NithyaI got it..Read other 2 answers [More]
whenever i key in my url, the index.html appears... how can i set my own default page instead?? thank u<?xml version="1.0" encoding="ISO-8859-1"?> <!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2 [More]
Hey people. Does anyone know of a way to set the just imaged flag from inside windows via commandline/script or in any automated way? I'm trying to use Microsofts Deployment Toolkit and get ISD (Image Safe Data) to work. The problem is that the proce [More]
I created a slideshow in Photoshop Elements 9.0 of about 12 minutes in length and 150 photos ranging in size from 300K to 3M. Any attempt to output the file to create a WMV immediately crashes Elements, regardless of the output settings or destinatio [More]