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 read here on these boards that an ATI 4870 would work in an early model (in my case, 2006) Intel Mac Pro. I jumped in hastily and bought the Asus branded card: EAH4870 I connected it, and nothing... did not work. I do not think it is a Mac version [More]
Update process is not workSee: * [[Software Update Failed]] * http://kb.mozillazine.org/Software_Update (Software Update not working properly) Remove the files in the updates and updates\0 folder.<br /> You may need to delete active-update.xml and u [More]
Hi EHS Goeroes, I'm struggling with the Generation of the MSDS. I have done the following procedure and settings: a) Create MSDS report (CG42) b) Edit Generation Variant (CG2B) Settings: (I have assigned my template to the Generation Variant) Generat [More]
Using MS Word to create an RTF template for BI Publisher. I've created what I want, and I looks great in PDF format and also as a PPT. In HTML and Excel formats, I get odd results: (1) Any centered line of text is cast toward the right of the page in [More]
How do I sync my blackberry torch 9800 with Outlook 2013?  I have updated my desktop software, but it does not seem to work with Outlook 2013. Any thoughts? ThanksWell, I cannot change phones right now.  So, what do I do to keep my blackberry up to d [More]
Hi , In the sales order nuber of line item are , i will have to take MRP RUN for selected line item . So plz suggest what development will require? Abhay Edited by: abhay patil on May 30, 2008 8:52 AMHI, U can write separate prog. Which will ask sale [More]
Hi All, I need your help regarding Photo Display in Portal ESS applications like who's who and change on data. For the same Authorization at ECC side required.If i use sap_all authorization, the pheto displayed at the portal side. But only using ess [More]
Hello Friends, I am new to Database administration, can any one give me a sample document how to do Replication between one Master site and one node, where data updation will be once in a day(evening).need step by step guide. my mail id is [email pro [More]
How do I change my email address for my profile ? Its no longer valid and I don't to create another OTN acct. Jeff jgreedIt's currently not possible to change your email address (although we're working on getting this fixed), so the best approach is [More]
Hi There.. I'm running the follows: 1. OS : XP SP3 2. VS 2010 3. ODP Net 11.2.0.2.0 When I set the ASP NET target framework to .NET 4 all running normally. However, if the set target to .NET 2 appears error "Could not load file or assembly Oracle.Dat [More]