Group By Use Chesi Elaaa

[ChampakDas]

[img]http://lh5.ggpht.com/-vhy7turw1dY/TwKNbRvJ65I/AAAAAAAAF0A/tbJCldCgTGE/s150/Mahi-30.gif[/img] intha complicated ga rastur endhi...

[ChampakDas]

festival!

Endhi nenu image post cheyalekapotunna..

[ChampakDas]

[img]http://s14.postimage.org/3ykhkxylb/Untitsdled.jpg[/img]

[qwerty1204]

[quote name='kiss4amiss' timestamp='1343254948' post='1302200508']
sql and error copy paste cheyi baa...nenu oracle to try chesi paste chesaa...DB2 sandbox ledhu naa dhagara...
[/quote]

QUERY:


SELECT RECORD_ID AS COL1, SUBSTR(SOURCE_RECORD,13,9) AS COL2,
(SELECT Count(SUBSTR(SOURCE_RECORD,13,9))
FROM RAWINPTS."RAW_IMF" a
WHERE a.SUBSTR(SOURCE_RECORD,13,9) = b.SUBSTR(SOURCE_RECORD,13,9)
GROUP BY a.SUBSTR(SOURCE_RECORD,13,9)
) AS COL3
FROM RAWINPTS."RAW_IMF" b ;

ERROR DESC:

------------------------------------------------------------------------------
SELECT RECORD_ID AS COL1, SUBSTR(SOURCE_RECORD,13,9) AS COL2, (SELECT Count(SUBSTR(SOURCE_RECORD,13,9)) FROM RAWINPTS."RAW_IMF" a WHERE a.SUBSTR(SOURCE_RECORD,13,9) = b.SUBSTR(SOURCE_RECORD,13,9) GROUP BY a.SUBSTR(SOURCE_RECORD,13,9) ) AS COL3 FROM RAWINPTS."RAW_IMF" b
SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION " having compatible arguments was found.was found.

[kiss4amiss]

[color=#282828][font=helvetica, arial, sans-serif]SOURCE_RECORD datatype enti??[/font][/color]

[kiss4amiss]

aina query ila undalanukunta baa...okasari idhi kuda try cheyava

SELECT b.record_id AS COL1,
[b]Substr[/b](b.source_record, [color=#000000]13[/color], [color=#000000]9[/color]) AS COL2,
(SELECT [i]Count[/i]([b]Substr[/b](a.source_record, [color=#000000]13[/color], [color=#000000]9[/color]))
FROM rawinpts."raw_imf" a
WHERE [b]Substr[/b](a.source_record, [color=#000000]13[/color], [color=#000000]9[/color]) = [b]Substr[/b](b.source_record, [color=#000000]13[/color], [color=#000000]9[/color])
GROUP BY [b]Substr[/b](a.source_record, [color=#000000]13[/color], [color=#000000]9[/color])) AS COL3
FROM rawinpts."raw_imf" b;

[qwerty1204]

[quote name='kiss4amiss' timestamp='1343255972' post='1302200658']
[color=#282828][font=helvetica, arial, sans-serif]SOURCE_RECORD datatype enti??[/font][/color]
[/quote]

VARCHAR baa

[Cherry25]

[quote name='kiraaaak' timestamp='1343254283' post='1302200447']
select col1
,col2
,count(col2) over (partition by col2) col3
from xyz


analytical function ba .. idi try chey ... TOAD ledu nagara .. execute cheddam ante
[/quote]

SQL> select * from test;

COL1 COL2
---------- ----
1 abc
2 xyz
3 abc

SQL> select col1,col2,count(*) over (partition by col2) col3 from test order by 1;

COL1 COL2 COL3
---------- ---- ----------
1 abc 2
2 xyz 1
3 abc 2

[vikuba]

late ga vachanu...but
self join on col2 with count chesi untey vachedi..

[ChampakDas]

answer chesi image copy chesi ganta ayindhi.. solve ayindho ledho kuda reply ledhu...[img]http://lh5.ggpht.com/-vhy7turw1dY/TwKNbRvJ65I/AAAAAAAAF0A/tbJCldCgTGE/s150/Mahi-30.gif[/img]
[quote name='Aamphat' timestamp='1343257035' post='1302200737']
late ga vachanu...but
self join on col2 with count chesi untey vachedi..
[/quote]

[qwerty1204]

[quote name='kiss4amiss' timestamp='1343256424' post='1302200693']
aina query ila undalanukunta baa...okasari idhi kuda try cheyava

SELECT b.record_id AS COL1,
[b]Substr[/b](b.source_record, [color=#000000]13[/color], [color=#000000]9[/color]) AS COL2,
(SELECT [i]Count[/i]([b]Substr[/b](a.source_record, [color=#000000]13[/color], [color=#000000]9[/color]))
FROM rawinpts."raw_imf" a
WHERE [b]Substr[/b](a.source_record, [color=#000000]13[/color], [color=#000000]9[/color]) = [b]Substr[/b](b.source_record, [color=#000000]13[/color], [color=#000000]9[/color])
GROUP BY [b]Substr[/b](a.source_record, [color=#000000]13[/color], [color=#000000]9[/color])) AS COL3
FROM rawinpts."raw_imf" b;
[/quote]

this one worked baa...

[qwerty1204]

[quote name='ChampakDas' timestamp='1343257223' post='1302200747']
answer chesi image copy chesi ganta ayindhi.. solve ayindho ledho kuda reply ledhu...[img]http://lh5.ggpht.com/-vhy7turw1dY/TwKNbRvJ65I/AAAAAAAAF0A/tbJCldCgTGE/s150/Mahi-30.gif[/img]
[/quote]

chivari solved baa[img]http://lh5.ggpht.com/-vhy7turw1dY/TwKNbRvJ65I/AAAAAAAAF0A/tbJCldCgTGE/s150/Mahi-30.gif[/img]

[ChampakDas]

chivari endhi? aa substr a na solution? aithe [img]http://lh5.ggpht.com/-vhy7turw1dY/TwKNbRvJ65I/AAAAAAAAF0A/tbJCldCgTGE/s150/Mahi-30.gif[/img]
[quote name='arey ooo samba' timestamp='1343257360' post='1302200750']

chivari solved baa[img]http://lh5.ggpht.com/-vhy7turw1dY/TwKNbRvJ65I/AAAAAAAAF0A/tbJCldCgTGE/s150/Mahi-30.gif[/img]
[/quote]

[kiss4amiss]

[quote name='arey ooo samba' timestamp='1343257294' post='1302200748']

this one worked baa...
[/quote]

[taarachowdary]

[quote name='arey ooo samba' timestamp='1343257294' post='1302200748']

this one worked baa...
[/quote]


count(fieldname) but you used count(sustrr(fn,9,13)

after substring it is no more a field name i guess

i dd not go through entire thread but i found some thing fish here