Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01747 (merged 2 threads)
ORA-01747 (merged 2 threads) [message #313956] |
Tue, 15 April 2008 06:09  |
rsss
Messages: 8 Registered: April 2008 Location: TX
|
Junior Member |
|
|
I am getting ORA-01747 error when I run the following query
select a.*, (select count(*) from MyTable where MyId = a.MyId and Type = a.Type
and rownum <= a.rownum) x from MyTable a
Thanks for your help.
|
|
|
|
|
|
|
|
Re: ORA-01747 [message #313973 is a reply to message #313969] |
Tue, 15 April 2008 06:36   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You need to move the SELECT a.* FROM mytable a into a subquery in order to get at the rownum.
Something like this should do it:select c.*, (select count(*)
from MyTable b
where b.MyId = c.MyId
and b.Type = c.Type
and b.rownum <= c.rnum) x
from (SELECT rownum rnum, a.*
FROM MyTable a) c
|
|
|
|
Re: ORA-01747 [message #313994 is a reply to message #313975] |
Tue, 15 April 2008 07:46   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
SQL> select t.rownum from cat t;
select t.rownum from cat t
*
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
SQL> select rownum from cat;
ROWNUM
----------
1
2
3
4
5
6
7
8
Rownum is a pseudo column. It is not associated with any table. Hence you get that error.
Quote: | select count(*) from MyTable where MyId = a.MyId and Type = a.Type
and rownum <= a.rownum
|
But I am not able to understand what you are trying to do with this query. If you don't mind could you please explain in plain english words what you are trying to achieve.
Regards
Raj
[Updated on: Tue, 15 April 2008 07:47] Report message to a moderator
|
|
|
Re: ORA-01747 [message #313999 is a reply to message #313956] |
Tue, 15 April 2008 08:03   |
rsss
Messages: 8 Registered: April 2008 Location: TX
|
Junior Member |
|
|
I have a table
CREATE TABLE MyTable (
MyID VARCHAR2(64) NOT NULL,
Type VARCHAR2(64) NOT NULL,
MyDESCRIPTION VARCHAR2(80) NOT NULL,
Ref VARCHAR2(64),
MyAMOUNT NUMBER(11),
TAX1 NUMBER(11),
TAX2 NUMBER(11))
INSERT MyTable
SELECT '1','Type1','Type1','ABC1',20,0,0
UNION ALL SELECT '1','Type1','Type1','ABC2',22,0,0
UNION ALL SELECT '1','Type1','Type1','ABC3',30,0,0
UNION ALL SELECT '1','Type2','Type2','123_1',25,0,0
UNION ALL SELECT '1','Type2','Type2','123_2',45,0,0
UNION ALL SELECT '1','Type2','Type2','123_3',35,0,0
UNION ALL SELECT '1','Type3','Type3','',0,1,2
UNION ALL SELECT '1','Type3','Type3','',0,5,6
UNION ALL SELECT '1','Type3','Type3','',0,8,0
UNION ALL SELECT '2','Type1','Type1','DEF1',40,0,0
UNION ALL SELECT '2','Type1','Type1','DEF2',42,0,0
UNION ALL SELECT '2','Type1','Type1','DEF3',40,0,0
UNION ALL SELECT '2','Type2','Type2','456_1',65,0,0
UNION ALL SELECT '2','Type2','Type2','456_2',75,0,0
UNION ALL SELECT '2','Type2','Type2','456_3',15,0,0
UNION ALL SELECT '2','Type2','Type2','456_4',95,0,0
UNION ALL SELECT '2','Type3','Type3','',0,10,0
UNION ALL SELECT '2','Type3','Type3','',0,13,12
UNION ALL SELECT '2','Type3','Type3','',0,0,50
UNION ALL SELECT '2','Type3','Type3','',0,3,0
I need a report like this
Ref Type1_MyAmount Type2_MyAmount Type3_TAX1 Type3_TAX2
ABC1 20 25 1 2
ABC2 22 45 5 6
ABC3 30 35 8 0
DEF1 40 65 10 0
DEF2 42 75 13 12
DEF3 40 15 0 50
NULL NULL 95 3 0
|
|
|
Re: ORA-01747 [message #314001 is a reply to message #313999] |
Tue, 15 April 2008 08:19   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: | If you don't mind could you please explain in plain english words what you are trying to achieve.
|
I can't find any explanation in your post and also while posting your script could you please take some time to read the forum guidelines and follow it.
Regards
Raj
|
|
|
Re: ORA-01747 [message #314006 is a reply to message #313999] |
Tue, 15 April 2008 08:49   |
rsss
Messages: 8 Registered: April 2008 Location: TX
|
Junior Member |
|
|
Hey Raj,
Thanks for your suggestion, I hope this time you can read the code clearly. I have a table and trying to generate a report with a query (stored procedure is not an option), I have attached a report with this message. Please help me to write query to generate that report.
Thanks for your help.
CREATE TABLE MyTable (
MyID VARCHAR2(64) NOT NULL,
Type VARCHAR2(64) NOT NULL,
MyDESCRIPTION VARCHAR2(80) NOT NULL,
Ref VARCHAR2(64),
MyAMOUNT NUMBER(11),
TAX1 NUMBER(11),
TAX2 NUMBER(11))
INSERT MyTable
SELECT '1','Type1','Type1','ABC1',20,0,0
UNION ALL SELECT '1','Type1','Type1','ABC2',22,0,0
UNION ALL SELECT '1','Type1','Type1','ABC3',30,0,0
UNION ALL SELECT '1','Type2','Type2','123_1',25,0,0
UNION ALL SELECT '1','Type2','Type2','123_2',45,0,0
UNION ALL SELECT '1','Type2','Type2','123_3',35,0,0
UNION ALL SELECT '1','Type3','Type3','',0,1,2
UNION ALL SELECT '1','Type3','Type3','',0,5,6
UNION ALL SELECT '1','Type3','Type3','',0,8,0
UNION ALL SELECT '2','Type1','Type1','DEF1',40,0,0
UNION ALL SELECT '2','Type1','Type1','DEF2',42,0,0
UNION ALL SELECT '2','Type1','Type1','DEF3',40,0,0
UNION ALL SELECT '2','Type2','Type2','456_1',65,0,0
UNION ALL SELECT '2','Type2','Type2','456_2',75,0,0
UNION ALL SELECT '2','Type2','Type2','456_3',15,0,0
UNION ALL SELECT '2','Type2','Type2','456_4',95,0,0
UNION ALL SELECT '2','Type3','Type3','',0,10,0
UNION ALL SELECT '2','Type3','Type3','',0,13,12
UNION ALL SELECT '2','Type3','Type3','',0,0,50
UNION ALL SELECT '2','Type3','Type3','',0,3,0
-
Attachment: Book2.csv
(Size: 7.99KB, Downloaded 1051 times)
|
|
|
Re: ORA-01747 [message #314018 is a reply to message #314006] |
Tue, 15 April 2008 09:46   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Thanks for formatting the code and following the guidelines but
still you have not explained how the data hangs together. Also if you don't mind either put the expected output inline or attach as a .txt file rather than any other type of files.
Regards
Raj
|
|
|
|
Re: ORA-01747 (merged 2 threads) [message #314022 is a reply to message #314020] |
Tue, 15 April 2008 10:04   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Ref Type1_ Type2_ Type3_ Type3_
MyAmount MyAmount TAX1 TAX2
----------------------------------------
ABC1 20 25 1 2
ABC2 22 45 5 6
ABC3 30 35 8 0
DEF1 40 65 10 0
DEF2 42 75 13 12
DEF3 40 15 0 50
NULL NULL 95 3 0
You have given us your expected output but you haven't explained (business rules) you used to produce the expected output.
Regards
Raj
|
|
|
Re: ORA-01747 (merged 2 threads) [message #314025 is a reply to message #313956] |
Tue, 15 April 2008 10:15   |
rsss
Messages: 8 Registered: April 2008 Location: TX
|
Junior Member |
|
|
As you see MyID is common for bunch of rows then divided by type (type1, type2, type3)
if you take myid = 1
the first row of type2 MyAMOUNT is belongs to first row of type1. and same rule for type3.
Some times I may miss type1 or type2 or type3.
I hope the expected out is expalining it. Please let me know if still its not clear.
BTW, how did you format the expected output inline (I couldn't find the guide lines for that).
Thanks for your help.
|
|
|
Re: ORA-01747 (merged 2 threads) [message #314028 is a reply to message #314025] |
Tue, 15 April 2008 10:27  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: |
BTW, how did you format the expected output inline (I couldn't find the guide lines for that).
|
All I did is downloaded and opened your .txt file and formatted it with spaces and before I click on submit, checked it with preview button to see if it is of the expected format. It's simple as that.
Now coming to your problem I will give you clues how to solve your issue. It is called pivoting (Transposing rows to columns). Infact you have given me the solution how to do it. I will give you clues how to solve it by yourself.
First clue/step : You have to logically group the rows so that you can aggregate the results. For how to logically group it check the analytic functions row_number in oracle sql reference manual.
Second Clue/step : Search in google/ in this forum for the keyword pivoting.
You will find loads of examples how to do it.
Happy learning Oracle.
Regards
Raj
[Updated on: Tue, 15 April 2008 10:28] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Fri Feb 14 16:41:04 CST 2025
|