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 Go to next message
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 #313959 is a reply to message #313956] Tue, 15 April 2008 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel
Re: ORA-01747 [message #313964 is a reply to message #313956] Tue, 15 April 2008 06:19 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
$ oerr ora 1747
01747, 00000, "invalid user.table.column, table.column, or column specification"
// *Cause:
// *Action:

Re: ORA-01747 [message #313965 is a reply to message #313956] Tue, 15 April 2008 06:22 Go to previous messageGo to next message
rsss
Messages: 8
Registered: April 2008
Location: TX
Junior Member
I am getting the error at "a.ROWNUM"
Re: ORA-01747 [message #313967 is a reply to message #313965] Tue, 15 April 2008 06:26 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You will have to restructure the SQL. The inner query executes first, when "a" is not yet defined.
Re: ORA-01747 [message #313969 is a reply to message #313956] Tue, 15 April 2008 06:30 Go to previous messageGo to next message
rsss
Messages: 8
Registered: April 2008
Location: TX
Junior Member
Thanks for reply, would you please give me some tips?
Re: ORA-01747 [message #313973 is a reply to message #313969] Tue, 15 April 2008 06:36 Go to previous messageGo to next message
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 #313975 is a reply to message #313956] Tue, 15 April 2008 06:41 Go to previous messageGo to next message
rsss
Messages: 8
Registered: April 2008
Location: TX
Junior Member
Thanks for reply, still I get the same error at
b.rownum
Re: ORA-01747 [message #313994 is a reply to message #313975] Tue, 15 April 2008 07:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 617 times)
Re: ORA-01747 [message #314018 is a reply to message #314006] Tue, 15 April 2008 09:46 Go to previous messageGo to next message
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 #314020 is a reply to message #313956] Tue, 15 April 2008 09:52 Go to previous messageGo to next message
rsss
Messages: 8
Registered: April 2008
Location: TX
Junior Member
still you have not explained how the data hangs together

I think I explained what I needed, Please check the attachment, I need to generate that kind of report with a query 9not stored procedure). Thanks for your help.

  • Attachment: output.txt
    (Size: 0.17KB, Downloaded 250 times)
Re: ORA-01747 (merged 2 threads) [message #314022 is a reply to message #314020] Tue, 15 April 2008 10:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: RAW datatype to sysdate (merged)
Next Topic: how to execute the a long query step by step
Goto Forum:
  


Current Time: Wed Dec 07 08:38:35 CST 2016

Total time taken to generate the page: 0.12010 seconds