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 can't transfer video from iTunes to iPod. DVD movies converted to MP4 using Cucusoft software and a TV show purchased from iTunes Music Store won't transfer. The only video that has tranferred successfully is a video podcast called "Tiki Bar". [More]
Hi, i'm trying to write a script which would allows me to transfer data automatically from diadem to excel and i need some help. i want to copy the last of my selected channels, open an excel file and paste data into it. My script look like this Dim [More]
I am trying to create a simple decision table with an input(ValidateInput) and a output(ValidateOutput). The input and output schemas have unbounded and nested elements(Parent-child relationship). When I create a condition(specific condition on input [More]
Hi All, The following code is using ABAP OO and I get the following error E:The addition "FOR ALL ENTRIES" excludes all aggregate functions with the exception of "COUNT( * )", as the single element of the SELECT clause. Here is the Cod [More]
Ive tried downloading the latest version but then always brings up CS4 and says not the right kind of document...? ive tried going to Updstes in help and says all updated but I have version 5.0.0.178 of Camera RawAdobe Camera Raw did not support the [More]
Hi, I am querying a function through the query-database() command in a certain xslt within a BPEL Process as below: <xsl:variable name="Corporation">                 <xsl:value-of select="string('CORPORATION')"/>   </xsl [More]
Hello, Can anyone help me on FF68. I have posted down payment from customer in TC: F-29 for USD 1,000. We have raised an invoice for USD 5,000 against this customer sends remain payment for USD4,000 via check. Now when we enter FF68 we are able to gi [More]
Dear Gurus, I have a problem in visual composer. I have created a model that has a selection list (sales area) and 2 charts. Initially the charts shows the total sales per month. But my requirement is to select (double click) different sales area fro [More]
Hi, I have looked through the documentation and some examples, but am still wondering whether the URL used for a HTTP data source has to have a file name in it or it can be just a URL with no file name (a true video streaming server). Also, if a stre [More]
Hi Guys, I'm hoping someone can help out, I'm quite desperate here. I am about to head overseas for three weeks, and need to leave my Mac running, so that I can log into it remotely using a PC. My Mac, is a MacBook Pro, and the PC I will be using is [More]