| Author | Message | ||
     Saint Megastar Username: Saint Post Number: 24696 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
why is it working? in the other case why is it not working? | ||
| Saint Megastar Username: Saint Post Number: 24690 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
eti gagu wats happening? | ||
| Gandhiguevara Legend Username: Gandhiguevara Post Number: 42383 Registered: 10-2009 Posted From: 142.136.137.10 Rating: N/A |
 | ||
| Saint Megastar Username: Saint Post Number: 24689 Registered: 01-2011 Posted From: 68.13.100.193 Rating: N/A |
Sachin, Yes its working.. Inko catch emiti ante substr expr teseai just column name itchina .. Its working... So diff ento jeppale | ||
| Botsa_fan Junior Artist Username: Botsa_fan Post Number: 178 Registered: 07-2013 Posted From: 158.48.133.2 Rating: N/A |
kiki ..ilage..as it is ga vuntadhi.  | ||
| Sachin Legend Username: Sachin Post Number: 35946 Registered: 04-2008 Posted From: 151.151.109.7 Rating: N/A |
mari 2 sqls working annaaav | ||
| Saint Megastar Username: Saint Post Number: 24686 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
vaakey...got it... || '","' || concatenate chesi single column lekka jesthundi ....so adi teesesthe work out ayyidi....may be grp by lo kooda concatenate jesi same to same ettali emo... | ||
| Saint Megastar Username: Saint Post Number: 24684 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
select b.1 || '","' || NVL(SUBSTR(a.1,1,6)|| REGEXP_REPLACE(SUBSTR(a.1,7,LENGTH(a.1)-10),'[0123456789]',' X')|| SUBSTR(a.1,LENGTH(a.1)-3,4), SUBSTR(a.2,1,6)|| REGEXP_REPLACE(SUBSTR(a.2,7,LENGTH(a.2)-10),'[0123456789]',' X')|| SUBSTR(a.2,LENGTH(a.2)-3,4)) || '","' || count( a.3)||'"' from a, b where b.3=a.4 group by NVL(SUBSTR(a.1,1,6)|| REGEXP_REPLACE(SUBSTR(a.1,7,LENGTH(a.1)-10),'[0123456789]',' X')|| SUBSTR(a.1,LENGTH(a.1)-3,4), SUBSTR(a.2,1,6)|| REGEXP_REPLACE(SUBSTR(a.2,7,LENGTH(a.2)-10),'[0123456789]',' X')|| SUBSTR(a.2,LENGTH(a.2)-3,4)), b.1 union select b.1 || '","' || NVL(SUBSTR(a.1,1,6)|| REGEXP_REPLACE(SUBSTR(a.1,7,LENGTH(a.1)-10),'[0123456789]',' X')|| SUBSTR(a.1,LENGTH(a.1)-3,4), SUBSTR(a.2,1,6)|| REGEXP_REPLACE(SUBSTR(a.2,7,LENGTH(a.2)-10),'[0123456789]',' X')|| SUBSTR(a.2,LENGTH(a.2)-3,4)) || '","' || count( a.3)||'"' from a, b where a.3=745747 and b.3=a.4 group by NVL(SUBSTR(a.1,1,6)|| REGEXP_REPLACE(SUBSTR(a.1,7,LENGTH(a.1)-10),'[0123456789]',' X')|| SUBSTR(a.1,LENGTH(a.1)-3,4), SUBSTR(a.2,1,6)|| REGEXP_REPLACE(SUBSTR(a.2,7,LENGTH(a.2)-10),'[0123456789]',' X')|| SUBSTR(a.2,LENGTH(a.2)-3,4)), b.1 | ||
| Sachin Legend Username: Sachin Post Number: 35944 Registered: 04-2008 Posted From: 151.151.109.7 Rating: N/A |
sql paste cheyyi doraaaa...change table and column names... | ||
| Saint Megastar Username: Saint Post Number: 24676 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
C/C++ developer oracle pl/sql developer ithe inthe vuntada? maa DBA jump ante ...naaku koddiga vachu anu work antha teesukunna....expectations peddaga levu but I am enjoying this new work... oka side JPA/eclipselink..inko side pl/sql dev.... hire chesina skill set C/C++ dev ....c/C++ meeda work chesi almost 2 months paine avuthundi ... ki ki ki.... | ||
| Saint Megastar Username: Saint Post Number: 24674 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
yes..its taken care...all select list was present in group by except the aggregate fn call one.. | ||
| Saint Megastar Username: Saint Post Number: 24673 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
u can..we were doing it since long time.. | ||
| Casino Side Hero Username: Casino Post Number: 3047 Registered: 01-2012 Posted From: 198.46.98.226 Rating: N/A |
kaadhu anukunta, atleast in ANSI SQL | ||
| Casino Side Hero Username: Casino Post Number: 3046 Registered: 01-2012 Posted From: 198.46.98.226 Rating: N/A |
The presence of a group by clause introduces certain restrictions with regards to the fields that can be referenced in the select list. In particular, a field or expression that does not appear in the group by clause cannot appear in the select list, unless it is used in an aggregate function call. ** See where it says expression ** | ||
| Andhrawala Megastar Username: Andhrawala Post Number: 26644 Registered: 03-2008 Posted From: 152.51.56.1 Rating: N/A |
with Union u cant do Group by No Signature | ||
| Dreamcatcher Side Hero Username: Dreamcatcher Post Number: 4886 Registered: 11-2009 Posted From: 208.86.145.68 Rating: N/A |
you are right, my bad..somehow I thought there was a restriction like that. come to think of it, it should work. | ||
| Saint Megastar Username: Saint Post Number: 24672 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
actual column name yee vaduthunna....the entire expression was copied to group by...(expression in select qry) if I remove the expression and just put the column, that is also giving error... | ||
| Saint Megastar Username: Saint Post Number: 24671 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
each qry has grp by before union. it was working fine... now I modified one column to mask the values. the same is copied into group by ... | ||
| Legend Username: Twitter Post Number: 31043 Registered: 10-2009 Posted From: 192.193.171.149 Rating: N/A |
group by in both sqls works in union anukunta .. i think saint kurrod group by actual column badhulu aliased column vaduthunnadu.. | ||
| Botsa_fan Junior Artist Username: Botsa_fan Post Number: 177 Registered: 07-2013 Posted From: 158.48.133.2 Rating: N/A |
you can do the same thing in oracle too | ||
| Casino Side Hero Username: Casino Post Number: 3045 Registered: 01-2012 Posted From: 198.46.98.226 Rating: N/A |
agree with you on logically, however I don't think there should be any restriction like that... as an example, this is the query I just ran in sql server and it works like a charm... select object_id, name from sys.columns group by object_id, name union select object_id, name from sys.columns group by object_id, name | ||
| Botsa_fan Junior Artist Username: Botsa_fan Post Number: 176 Registered: 07-2013 Posted From: 158.48.133.2 Rating: N/A |
nuvvu substr chesey column included in group by | ||
| Casino Side Hero Username: Casino Post Number: 3044 Registered: 01-2012 Posted From: 198.46.98.226 Rating: N/A |
can't you do group by in individual queries in Oracle? | ||
| Dreamcatcher Side Hero Username: Dreamcatcher Post Number: 4885 Registered: 11-2009 Posted From: 208.86.145.68 Rating: N/A |
do you have group by twice, once before union and after union?? that would be not allowed. group by is applied only once for the entire query at the end. so your query should be select X,Y,Z from a,b,c,d where codnitions union select x,y,z from a,b,c,d where diff condtions group by x,y,z | ||
| Andhrawala Megastar Username: Andhrawala Post Number: 26642 Registered: 03-2008 Posted From: 152.51.56.1 Rating: N/A |
select x,y,z from ( select X,Y,Z from a,b,c,d where codnitions union union select x,y,z from a,b,c,d where diff condtions) group by x,y,z No Signature | ||
| Casino Side Hero Username: Casino Post Number: 3043 Registered: 01-2012 Posted From: 198.46.98.226 Rating: N/A |
just see how many columns are in the output after you run each query.. # of columns mismatch or column types mismatch. | ||
| Saint Megastar Username: Saint Post Number: 24670 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
Union mundu query / union tarvatha qry invidiual ra run chesthe ..I am getting o/p...union add chesthe pelathandi.. | ||
| Legend Username: Twitter Post Number: 31041 Registered: 10-2009 Posted From: 192.193.171.149 Rating: N/A |
group by aliases pedithe theesesi original column name pettu work auddhi | ||
| Casino Side Hero Username: Casino Post Number: 3042 Registered: 01-2012 Posted From: 198.46.98.226 Rating: N/A |
full query paste cheyyi.. probably some syntax error | ||
| Saint Megastar Username: Saint Post Number: 24668 Registered: 01-2011 Posted From: 12.22.163.132 Rating: N/A |
nenu oka pl/sql prog change chesanu....1 clm has to be masked so used substr..etc to replace part of the string with ZZZZZZZ... execute chesetappudu... select X,Y,Z from a,b,c,d where codnitions group by x,y,z union select x,y,z from a,b,c,d where diff condtions group by x,y,z ante ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" ee error vasthundi... ade union mundu qry ni ..union tarvtha qry ni individual ga run chese ..o/p is good...union is causing some error...emitoo idea vunda? |
