Home » SQL & PL/SQL » SQL & PL/SQL » select query failing (oracle 10g)
select query failing [message #421384] Tue, 08 September 2009 01:28 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member

SELECT MED_NAME AS MEDICATION,
  MAX(ETC),
  MAX(generic_drug_name_override)
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS            = 'A'
GROUP BY MED_NAME
ORDER BY MED_MEDID_DESC ASC,
  med_routed_med_id_desc ASC



Error at Command Line:8 Column:9
Error report:
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:

Re: select query failing [message #421387 is a reply to message #421384] Tue, 08 September 2009 01:40 Go to previous messageGo to next message
narasak1
Messages: 2
Registered: September 2009
Junior Member
Hi,

Put the Alias name in Group by Class. like



SELECT MED_NAME AS MEDICATION,
MAX(ETC),
MAX(generic_drug_name_override)
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A'
GROUP BY MEDICATION
ORDER BY MED_MEDID_DESC ASC,
med_routed_med_id_desc ASC
Re: select query failing [message #421388 is a reply to message #421384] Tue, 08 September 2009 01:40 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You are trying to order resultset by MED_MEDID_DESC ASC, med_routed_med_id_desc ASC, you cannot do that without grouping on them as well.

[Updated on: Tue, 08 September 2009 01:42]

Report message to a moderator

Re: select query failing [message #421389 is a reply to message #421388] Tue, 08 September 2009 01:51 Go to previous messageGo to next message
narasak1
Messages: 2
Registered: September 2009
Junior Member
Hi,

when ever your using the group by that time u can order by only on selecting column names. like

SELECT MED_NAME AS MEDICATION,
MAX(ETC),
MAX(generic_drug_name_override)
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A'
GROUP BY MEDICATION
ORDER BY MED_NAME
Re: select query failing [message #421390 is a reply to message #421388] Tue, 08 September 2009 01:56 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
query is working fine.but it is retrieving lot of values as need only one value which is max.

SELECT MED_NAME AS MEDICATION,
MAX(ETC),
MAX(generic_drug_name_override),MAX(MEDICATIONS_ID)
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A'
GROUP BY MED_NAME,MED_MEDID_DESC,med_routed_med_id_desc
ORDER BY MED_MEDID_DESC ASC,
med_routed_med_id_desc ASC
Re: select query failing [message #421393 is a reply to message #421384] Tue, 08 September 2009 02:04 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
In addition to bonker: this is rather a logical problem than Oracle one. If your data would be like
MED_NAME ETC MED_MEDID_DESC
-------- --- ----
Aspirin    5 aaa
Aspirin    8 xxx
Aspirin   10 zzz
ASPIRIN    1 bbb
ASPIRIN    6 ccc
Then the query result (without ORDER BY clause) would be
MED_NAME MAX(ETC)
-------- --------
Aspirin        10
ASPIRIN         6

How would you sort these rows based on MED_MEDID_DESC column?

@nasarak1: How did you come to your suggestion. Because, the original GROUP BY clause was correct, but your modification fails, which may be easily demonstrated:
SQL> create table t1( c1 integer, c2 integer );

Table created.

SQL> select c1 as medication
  2  from t1
  3  group by medication;
group by medication
         *
ERROR at line 3:
ORA-00904: "MEDICATION": invalid identifier


SQL> select c1 as medication
  2  from t1
  3  group by c1;

no rows selected

SQL> 
Re: select query failing [message #421394 is a reply to message #421390] Tue, 08 September 2009 02:04 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
SELECT MED_NAME AS MEDICATION,
MAX(ETC),
MAX(generic_drug_name_override),MAX(MEDICATIONS_ID)
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A'
AND ROWNUM = 1
GROUP BY MED_NAME,MED_MEDID_DESC,med_routed_med_id_desc
ORDER BY MED_MEDID_DESC ASC,
med_routed_med_id_desc ASC



TRY LIKE THIS IT WILL HELP U I THINK
Re: select query failing [message #421395 is a reply to message #421390] Tue, 08 September 2009 02:11 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
rajasekhar857 wrote on Tue, 08 September 2009 08:56
query is working fine.but it is retrieving lot of values as need only one value which is max.

So maybe
ORDER BY MAX(MED_MEDID_DESC) ASC,
MAX(med_routed_med_id_desc) ASC
? In my previous example, the order would be
MED_NAME MAX(ETC)
-------- --------
ASPIRIN         6
Aspirin        10
, as 'ccc' (greatest MED_MEDID_DESC for ASPIRIN) is lower than 'zzz' (greatest MED_MEDID_DESC for Aspirin).

Anyway, this is just a guess. But, without a clear specification of the rule(s), this is everything I can say.
Re: select query failing [message #421396 is a reply to message #421394] Tue, 08 September 2009 02:13 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Halfprin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspergum
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Stanback Headache Powder
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	AsperDrink
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Aspirin
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Bayer 8-Hour
Aspirin	Salicylates,  Platelet Aggregation Inhibitors - Salicylates	Zorprin




my values are coming like this i want only first row.
Re: select query failing [message #421397 is a reply to message #421396] Tue, 08 September 2009 02:15 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
HEY MAN ADD ROWNUM = 1 IN WHERE CONDITION AS I POSTED EARLIER 
Re: select query failing [message #421398 is a reply to message #421397] Tue, 08 September 2009 02:18 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
sen_sam86 wrote on Tue, 08 September 2009 09:15
HEY MAN ADD ROWNUM = 1 IN WHERE CONDITION AS I POSTED EARLIER 



But only if you LIKE it when your application returns different wrong data randomly each time you run it.
Re: select query failing [message #421399 is a reply to message #421398] Tue, 08 September 2009 02:23 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
exactly iam including rownum then iam getting o/p as this value
Aspirin Salicylates, Platelet Aggregation Inhibitors - Salicylates Stanback Headache Powder


i dont need this value i need vale as
Aspirin Salicylates, Platelet Aggregation Inhibitors - Salicylates Halfprin
Re: select query failing [message #421402 is a reply to message #421399] Tue, 08 September 2009 02:29 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think it would be best if you posted a test-case (create table and insert statements with test data) and the result you want.

I Still haven't figured out what it is you want as a result exactly. I have a feeling that it might be solvable with a sub-query with the analytical rank, though.

Re: select query failing [message #421404 is a reply to message #421402] Tue, 08 September 2009 02:34 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
tried out like this

SELECT MED_NAME AS MEDICATION,
ETC,
generic_drug_name_override,RANK() OVER (PARTITION BY MED_NAME
ORDER BY MED_MEDID_DESC ,
med_routed_med_id_desc )" Rank"
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A'

same result
Re: select query failing [message #421406 is a reply to message #421404] Tue, 08 September 2009 02:43 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And is the row with the rank "1" the one you need? Then put the entire thing in a sub-select.

More help can most likely only be given when we have the test case.
Re: select query failing [message #421407 is a reply to message #421404] Tue, 08 September 2009 02:50 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
You are still not able to provide your requirement clearly. Your latest query
SELECT MED_NAME AS MEDICATION,
ETC,
generic_drug_name_override,RANK() OVER (PARTITION BY MED_NAME
ORDER BY MED_MEDID_DESC ,
med_routed_med_id_desc )" Rank"
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A' 


is completely & radically different than your first query that you posted.

However you are just looking first row for every change in med_name then you have to change your last query like this

Select * from (
SELECT MED_NAME AS MEDICATION,
ETC,
generic_drug_name_override,Row_number() OVER (PARTITION BY MED_NAME
ORDER BY MED_MEDID_DESC ,
med_routed_med_id_desc ) rno
FROM EMRMedicationsTPLkup
WHERE UPPER(MED_NAME) = UPPER('Aspirin')
AND STATUS = 'A' )
where rno = 1







Previous Topic: new line sign
Next Topic: How to check procedure is running more than stipulate time frame
Goto Forum:
  


Current Time: Fri Sep 30 08:59:14 CDT 2016

Total time taken to generate the page: 0.10646 seconds