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
I have created a unit test for a method in VS 2008. My expected value and actual value are XMLs. Therefore though the output is same as I expect it gives an error as I am doing string comparison now. How can I compare these 2 XMLs in expected output [More]
I have events in a calendar set up to email an alarm for when I need to service certain machines that are under a service agreement. Then the person will log the call using the system number that I have in notes from their email. I am trying to elimi [More]
Good Day SAP expert, I have facing an error message on Material document contains no items (order: 40000004 item: 000010) & Message no. /DBM/COMMON286. This error happen when I want to create Good Issue with action QGIS (Create Goods Issue (DBM)). St [More]
Hello We have 2 seperate ledgers maintained for Leading ledger & Budget rate. (New GL) I am aware that 0FI_GL_14 will extract all line item from leading ledger only. Which Datasource should we use for Budget Rate? SAP suggests that we should use 3FI_ [More]
I am trying to use oracle's proxy connection in order to connect to the Database, for security reasons. Now the problem is that for the purpose of doing so you have to use oracle.jdbc.OracleConnection. The container I am running on is JBoss and when [More]
I have WCS managing 4 controllers.  1 of which has clean air enabled.  WCS gives air quality reports just fine, but it gives me no information on the Device Interferers or the Interferer count.  I can go to the controller, go to monitor>cleanair and [More]
Hi All, I've created a Deployable proxy for my webservice and deployed the EAR. I can see JNDI registry in the visual administrator. I am now writing a simple  java client to call the proxy. 1. try {    Hashtable env = new Hashtable();    env.put(Con [More]
I recently updated the safari software on my macbook. As I live in New Zealand I use google.co.nz however after updating safari, the default country has changed to the Netherlands (I'm guessing as it's hard to tell as everything is written in another [More]
I have installed a dblink to mysql database. Using this dblink I can access to the tables but only to the numeric fields! if you have solution to my problem please help me thanks I use: - orable 9.2 and mysql 5.1Error No 1: using the following sql st [More]
The external part of my elite pro (dont know what its called) is only working somewhat. I can hear music from it with headphones but if i try to adjust volume or any of the settings (crystallizer, CMSS-3D, etc) it wont work. I can use the remote to a [More]