Home » SQL & PL/SQL » SQL & PL/SQL » SQL QUERY - URGENT please help
SQL QUERY - URGENT please help [message #304721] Thu, 06 March 2008 06:50 Go to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
i have table A with follow data

name ---- date

A 01-JAN-2005
A 10-MAR-2006
A 20-APR-2007
A 01-MAY-2008
B 10-FEB-2006
B 01-MAR-2007


i want to return the result to have all the dates
for all the names.

A 01-JAN-2005
A 10-MAR-2006
A 20-APR-2007
A 01-MAY-2008
A 10-FEB-2006
A 01-MAR-2007
B 01-JAN-2005
B 10-MAR-2006
B 20-APR-2007
B 01-MAY-2008
B 10-FEB-2006
B 01-MAR-2007

how to acheive this result using query. please help.
Re: SQL QUERY - URGENT please help [message #304723 is a reply to message #304721] Thu, 06 March 2008 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An outer join 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
Follow them.

Regards
Michel
Re: SQL QUERY - URGENT please help [message #304724 is a reply to message #304721] Thu, 06 March 2008 06:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What have you tried, please post your attempt. But before you do, please read the forum guide on how to get a quick answer to your questions and how to format your posts.
Cheers
Jim
Re: SQL QUERY - URGENT please help [message #304725 is a reply to message #304721] Thu, 06 March 2008 06:59 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
sorry for the wrong post.

but i have tried outer join

its not returning the proper data will all the names
with all the existing dats.

please help.
Re: SQL QUERY - URGENT please help [message #304726 is a reply to message #304725] Thu, 06 March 2008 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post the result you want with these data.
Don't forget to format your post.

Post your Oracle version (4 decimals).

All these are explained in guide, please read it.

Regards
Michel

[Updated on: Thu, 06 March 2008 07:01]

Report message to a moderator

Re: SQL QUERY - URGENT please help [message #304727 is a reply to message #304721] Thu, 06 March 2008 07:04 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I don't think an outer join is needed.
Re: SQL QUERY - URGENT please help [message #304728 is a reply to message #304721] Thu, 06 March 2008 07:06 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
sorry i am new to forums. dont know how to format.

this is my request.

CREATE TABLE A(NAME VARCHAR2(1),SDATE DATE);

INSERT INTO A VALUES(A,01-JAN-2005);
INSERT INTO A VALUES(A,10-MAR-2006);
INSERT INTO A VALUES(A,20-APR-2007);
INSERT INTO A VALUES(A,01-MAY-2008);
INSERT INTO A VALUES(B,10-FEB-2006);
INSERT INTO A VALUES(B,01-MAR-2007);

so now in the table A about 6 records with 6 unique dates.
i need all these unique dates should return with each of the names
in the table.

A 01-JAN-2005
A 10-MAR-2006
A 20-APR-2007
A 01-MAY-2008
A 10-FEB-2006
A 01-MAR-2007

B 01-JAN-2005
B 10-MAR-2006
B 20-APR-2007
B 01-MAY-2008
B 10-FEB-2006
B 01-MAR-2007

please help.

thanks
ravi.
Re: SQL QUERY - URGENT please help [message #304729 is a reply to message #304721] Thu, 06 March 2008 07:10 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Gonna take you by the hand and lead you.

Let's suppose you have two tables A1 and A2:

I took the liberty to copy your sample, including the obvious errors:

CREATE TABLE A1 ( NAME VARCHAR2(1) );

INSERT INTO A1 VALUES(A);
INSERT INTO A1 VALUES(B);

CREATE TABLE A2 (SDATE DATE);

INSERT INTO A2 VALUES(01-JAN-2005);
INSERT INTO A2 VALUES(10-MAR-2006);
INSERT INTO A2 VALUES(20-APR-2007);
INSERT INTO A2 VALUES(01-MAY-2008);
INSERT INTO A2 VALUES(10-FEB-2006);
INSERT INTO A2 VALUES(01-MAR-2007);


With these two tables: how would you accomplish what you want?
Re: SQL QUERY - URGENT please help [message #304730 is a reply to message #304728] Thu, 06 March 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> INSERT INTO A VALUES(A,01-JAN-2005);
INSERT INTO A VALUES(A,01-JAN-2005)
                          *
ERROR at line 1:
ORA-00984: column not allowed here

Formatted is what I did and this is explained in forum guide.
Once again read it.

Also post valid statements.
And remember a string is NOT a date.

Regards
Michel
Re: SQL QUERY - URGENT please help [message #304731 is a reply to message #304729] Thu, 06 March 2008 07:15 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
sorry again, i need to organize my self i am sure.

Here there is no 2 tables.

I have only one table A with these data.

and the result set need to return

A -> with all the B dates.

B -> with All the A dates.

Hope i have explained to my knowledge.

thanks.

ravi.
Re: SQL QUERY - URGENT please help [message #304732 is a reply to message #304731] Thu, 06 March 2008 07:18 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
powerls wrote on Thu, 06 March 2008 14:15
sorry again, i need to organize my self i am sure.

Here there is no 2 tables.

I have only one table A with these data.

and the result set need to return

A -> with all the B dates.

B -> with All the A dates.

Hope i have explained to my knowledge.

thanks.

ravi.


You made yourself very clear, no problem.

BUT: I just trying to help you to find the solution yourself.
I'm sure you can find the solution yourself, but you have to think & try.

Just try to figure out a solution when you have two tables: that's far more easier to comprehend.
If you have your two-table solution, the step towards only one table is a piece of cake.
Re: SQL QUERY - URGENT please help [message #304735 is a reply to message #304721] Thu, 06 March 2008 07:37 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
when i make 2 subqueries of the same table.
using cartesian product its working to return all the rows.

now one more addition of problem is

Now A table has 3 columns

NAME ---- POINTER ---- DATE.
A 1 ..
A 2 ..
A 3
A 4
B 5
B 6

now how to use return. u may think i am a dump. Smile

ravi.

Re: SQL QUERY - URGENT please help [message #304739 is a reply to message #304735] Thu, 06 March 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the solution.
Post the new test case.
Post it correct.
Post it formatted.
Post your Oracle version.
Read the guide.

Regards
Michel
Re: SQL QUERY - URGENT please help [message #304783 is a reply to message #304735] Thu, 06 March 2008 10:22 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
powerls wrote on Thu, 06 March 2008 14:37

Now A table has 3 columns (...) How to use return.


What does it mean: "How to use return"? I usually press it with my baby finger; when I'm mad, I do it with the index finger (and it usually doesn't help at all).
Re: SQL QUERY - URGENT please help [message #304842 is a reply to message #304721] Thu, 06 March 2008 21:52 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
I will be more organized from the next post onwards, Confused

Sorry to piss u off guys.

Anyhow, i got the solution myself.(You guys have opened my eyes clearly here!).

thanks for the expertise.


ravi.
Re: SQL QUERY - URGENT please help [message #304857 is a reply to message #304842] Thu, 06 March 2008 23:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post the solution, for others, for yourself as we may improve your query.

Regards
Michel
Re: SQL QUERY - URGENT please help [message #304896 is a reply to message #304857] Fri, 07 March 2008 01:52 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
this is the solution i got from the simple table A.

select * from
(select distinct name from A) a1
,(select distinct date from A) b1

-----

my doubt will be what if i have a table like this.

CREATE TABLE ABC(NAME VARCHAR2(1),POINTER NUMBER,SDATE DATE);

INSERT INTO ABC VALUES('A',10,'01-JAN-2005');
INSERT INTO ABC VALUES('A',11,'02-FEB-2005');
INSERT INTO ABC VALUES('A',12,'10-MAR-2006');
INSERT INTO ABC VALUES('A',13,'15-APR-2006');
INSERT INTO ABC VALUES('A',14,'11-APR-2007');
INSERT INTO ABC VALUES('B',15,'10-MAR-2006');
INSERT INTO ABC VALUES('B',16,'14-APR-2006');
INSERT INTO ABC VALUES('B',17,'11-APR-2007');
INSERT INTO ABC VALUES('B',18,'10-DEC-2008');


my needed output will be....


NAME--------POINTER--------DATE
A 10 01-JAN-2005
A 11 02-FEB-2005
A 12 10-MAR-2006 --> A and B's Entry so A entry pointer is default
A NULL 14-APR-2006 --> B Entry's DATE
A 13 15-APR-2006
A 14 11-APR-2007 --> A and B's Entry so A entry pointer is default
A NULL 10-DEC-2008 --> B's Entry DATE

B NULL 01-JAN-2005 --> A's Entry DATE
B NULL 02-FEB-2005 --> A's Entry DATE
B 15 10-MAR-2006 --> A and B's Entry so B entry pointer is default
B 16 14-APR-2006 --> B's Entry DATE
B 17 11-APR-2007 --> A and B's Entry so B entry pointer is default
B 18 10-DEC-2008 --> B's Entry DATE


when there is a common entry's between names, it has choose its pointer and date.
the non existing date of the other entries should be added with null pointer entry.

could u please give me a hand!.

thanks

ravi.

Re: SQL QUERY - URGENT please help [message #304898 is a reply to message #304896] Fri, 07 March 2008 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your first query perfectly answers your first question.

I can't answer the last one as you still don't format your post.
It has been asked you 5 times in this topic.

Regards
Michel
Re: SQL QUERY - URGENT please help [message #304921 is a reply to message #304721] Fri, 07 March 2008 03:16 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member
CREATE TABLE ABC(NAME VARCHAR2(1),POINTER NUMBER,SDATE DATE);

INSERT INTO ABC VALUES('A',10,'01-JAN-2005');
INSERT INTO ABC VALUES('A',11,'02-FEB-2005');
INSERT INTO ABC VALUES('A',12,'10-MAR-2006');
INSERT INTO ABC VALUES('A',13,'15-APR-2006');
INSERT INTO ABC VALUES('A',14,'11-APR-2007');
INSERT INTO ABC VALUES('B',15,'10-MAR-2006');
INSERT INTO ABC VALUES('B',16,'14-APR-2006');
INSERT INTO ABC VALUES('B',17,'11-APR-2007');
INSERT INTO ABC VALUES('B',18,'10-DEC-2008');



my needed output will be....


NAME          POINTER          SDATE

 A              10             01-JAN-2005
 A              11             02-FEB-2005
 A              12             10-MAR-2006 --> A and B's Entry so A entry pointer is default
 A             NULL            14-APR-2006 --> B Entry's DATE
 A              13             15-APR-2006
 A              14             11-APR-2007 --> A and B's Entry so A entry pointer is default
 A             NULL            10-DEC-2008 --> B's Entry DATE

 B             NULL            01-JAN-2005 --> A's Entry DATE
 B             NULL            02-FEB-2005 --> A's Entry DATE
 B              15             10-MAR-2006 --> A and B's Entry so B entry pointer is default
 B              16             14-APR-2006 --> B's Entry DATE
 B             NULL            15-APR-2006 --> A's Entry DATE
 B              17             11-APR-2007 --> A and B's Entry so B entry pointer is default
 B              18             10-DEC-2008 --> B's Entry DATE




when there is a common entry's between names, it has choose its pointer and date.
the non existing date of the other entries should be added with null pointer entry.

could u please give me a hand!.

thanks
ravi.

PS: finally am i formatting my code properly!??

[Updated on: Fri, 07 March 2008 03:18]

Report message to a moderator

Re: SQL QUERY - URGENT please help [message #304929 is a reply to message #304921] Fri, 07 March 2008 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is correctly formatted now.
Now '01-JAN-2005' is a string and not a date, as you can see if I execute your statement I get an error:
SQL> INSERT INTO ABC VALUES('A',10,'01-JAN-2005');
INSERT INTO ABC VALUES('A',10,'01-JAN-2005')
                              *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Finally outer join your previous query with your abc table.

Regards
Michel

[Updated on: Fri, 07 March 2008 04:10]

Report message to a moderator

Re: SQL QUERY - URGENT please help [message #304976 is a reply to message #304721] Fri, 07 March 2008 07:03 Go to previous messageGo to next message
powerls
Messages: 11
Registered: February 2006
Junior Member

Thanks for your reply.

the insert statement is giving error in your instance.
but my instance able to insert the statement successfully.

is it related to nls_format? or some other data setup in
the database settings? please clarify.

thanks for your support.

ravi.
Re: SQL QUERY - URGENT please help [message #304979 is a reply to message #304976] Fri, 07 March 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is related to my nls settings, especially my language and date format.
NEVER rely on implicit conversion.
Use TO_DATE function with the format model corresponding to your data.

Regards
Michel
Re: SQL QUERY - URGENT please help [message #304991 is a reply to message #304721] Fri, 07 March 2008 07:44 Go to previous message
powerls
Messages: 11
Registered: February 2006
Junior Member
great, thanks lot.

ravi.
Previous Topic: Convert CLOB to VARCHAR2 in MV
Next Topic: How to execute a procedure from within a package?
Goto Forum:
  


Current Time: Sat Dec 03 15:56:20 CST 2016

Total time taken to generate the page: 0.12387 seconds