Home » SQL & PL/SQL » SQL & PL/SQL » Facing problem with a Query (Oracle 10g)
Facing problem with a Query [message #574759] Tue, 15 January 2013 11:45 Go to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Hi Experts!

I'm having truble by querying a column in Oracle 10g. This is my situation I wrote a standard Oracle Query to show only the domain name of user's registration. The column's alias is called MAILS so far so good, the main problem is when I tried to shortener the result to show only Gmail, Hotmail and Yahoo.

Can I query that field ?

Also I had the same issue when I tried to use GROUP BY the field "MAILS", but when I use the order by clause with that field it run perfectly. I think a test case is not necesarry because there are common mails address.

Here is the code:

SELECT 
SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) "MAILS",
COUNT(SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))) "TOTAL"
FROM USER_REGISTRATION
WHERE TRUNC(FECHAALTA) BETWEEN TRUNC(TO_DATE('01/01/2012','DD/MM/YYYY')) AND TRUNC(TO_DATE('31/12/2012','DD/MM/YYYY'))

GROUP BY SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))
ORDER BY MAILS DESC;


Thanks in advance!
Re: Facing problem with a Query [message #574760 is a reply to message #574759] Tue, 15 January 2013 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
>Can I query that field ?
apparently not.
Re: Facing problem with a Query [message #574762 is a reply to message #574760] Tue, 15 January 2013 11:54 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Thank you very much BlackSwan.

I solved the problem by adding again the filed mail with the functions I used before to extract only the domain name.

Does anyone know whether there's a way to re-query this kind of fields or not ?


SELECT 
SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) "MAILS",
COUNT(SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))) "TOTAL"
FROM USER_REGISTRATION
WHERE TRUNC(FECHAALTA) BETWEEN TRUNC(TO_DATE('01/01/2012','DD/MM/YYYY')) AND TRUNC(TO_DATE('31/12/2012','DD/MM/YYYY'))
AND SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) IN ('gmail','hotmail','yahoo') 
GROUP BY SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))
ORDER BY MAILS DESC;

[Updated on: Tue, 15 January 2013 11:55]

Report message to a moderator

Re: Facing problem with a Query [message #574780 is a reply to message #574759] Tue, 15 January 2013 15:44 Go to previous message
joy_division
Messages: 4546
Registered: February 2005
Location: East Coast USA
Senior Member
akull wrote on Tue, 15 January 2013 12:45
I think a test case is not necesarry because there are common mails address.


But if I run your code it will fail because I do not have your table or data.

What do you mean by re-query, as I do not know the problem you are trying to solve?
Previous Topic: Native Dynamic Sql
Next Topic: XML : Union of 2 Select queries
Goto Forum:
  


Current Time: Fri Nov 28 05:07:28 CST 2014

Total time taken to generate the page: 0.09957 seconds