| Author |
Message |
    
Vidhata
Pilla Bewarse
Username: Vidhata
Post Number: 52
Registered: 09-2005
Posted From: 63.87.170.126
| | Posted on Monday, July 17, 2006 - 5:34 pm: |    |
babulu ee tokkalo sql server 2000 query lo em tappu chesano cheppandi pls..
select Vendornumber,Currency,sum(invoiceamount) as TotalAmount from approvedinvoices
group by vendornumber,Currency
having vendornumber in @VendorNumber
order by vendornumber, Currency Desc
incorrect syntax near @VendorNumber ani vasondi...
@vendornumber is the input for the StoredProc..
sample value (123,124,125) |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24640
Registered: 07-2004
Posted From: 71.234.232.47
| | Posted on Monday, July 17, 2006 - 5:29 pm: | |
 thanx nayak,PT,buff maayyas..
deenthalli repatnunchi ika cube,rollups iragadiyyaali 
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
2007
Kurra Bewarse
Username: 2007
Post Number: 678
Registered: 05-2006
Posted From: 129.188.33.222
| | Posted on Monday, July 17, 2006 - 5:25 pm: | |
ounu ala akkarledu anukunta |
Tmbuff
Pilla Bewarse
Username: Tmbuff
Post Number: 151
Registered: 04-2004
Posted From: 159.153.4.51
| | Posted on Monday, July 17, 2006 - 5:24 pm: | |
I haven't tried the SQL penukonda mama gave, but it is a overkill for the requirement I think. |
2007
Kurra Bewarse
Username: 2007
Post Number: 677
Registered: 05-2006
Posted From: 129.188.33.222
| | Posted on Monday, July 17, 2006 - 5:23 pm: | |
denne
select col1, col2, sum(amt)
group by CUBE(col1, col2) ante
results ila vundiddi
India AP 300
NULL AP 300
India UP 500
NULL UP 500
India MP 1000
NULL MP 1000
India NULL 1800
NULL NULL 1800
ala vasthayi results... |
2007
Kurra Bewarse
Username: 2007
Post Number: 676
Registered: 05-2006
Posted From: 129.188.33.222
| | Posted on Monday, July 17, 2006 - 5:22 pm: | |
suppoer table data ila vundi
col1 col2 amt
India AP 100
India AP 200
India UP 200
India UP 300
India MP 300
India MP 600
India MP 100
select col1, col2, sum(amt)
group by rollup(col1, col2) ante
results ila vundiddi
India AP 300
India UP 500
India MP 1000
India NULL 1800
NULL NULL 1800
ade cube aithey all possible combinations tho aggregates vasthayi... |
Bob
Vooriki Bewarse
Username: Bob
Post Number: 4068
Registered: 03-2005
Posted From: 65.64.34.45
| | Posted on Monday, July 17, 2006 - 5:20 pm: | |
VEERABHADRA |
2007
Kurra Bewarse
Username: 2007
Post Number: 675
Registered: 05-2006
Posted From: 129.188.33.222
| | Posted on Monday, July 17, 2006 - 5:19 pm: | |
gochi mama CUBE, ROLLUP operators vunnayi rendu.. DWH lo data aggregates create cheyyataniki use chestharu... oracle 81 nunchi vunnayi.. |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18372
Registered: 03-2004
Posted From: 24.136.161.233
| | Posted on Monday, July 17, 2006 - 5:06 pm: | |
Thanks tmbuff mama, idi pani sesela undi sothunte.
PT mama, cube enti mama. |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24636
Registered: 07-2004
Posted From: 71.234.232.47
| | Posted on Monday, July 17, 2006 - 4:58 pm: | |
CUBE enti PT maama...
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Tmbuff
Pilla Bewarse
Username: Tmbuff
Post Number: 150
Registered: 04-2004
Posted From: 159.153.4.51
| | Posted on Monday, July 17, 2006 - 4:29 pm: | |
Hemanth mama --
You can use this query..
SELECT * FROM fa_locations WHERE COST_CENTER IN (SELECT COST_CENTER FROM fa_locations
HAVING COUNT(*) =1 GROUP BY COST_CENTER)
AND STATE IS NULL |
Penkonda_tiger
Bewarse
Username: Penkonda_tiger
Post Number: 1637
Registered: 06-2005
Posted From: 62.234.167.180
| | Posted on Monday, July 17, 2006 - 4:25 pm: | |
select Count(*) ,COST_CENTRE from (
select DISTINCT COST_CENTRE,STATE, Count(*) from fa_locations group by CUBE(STATE), COST_CENTRE)
where STATE is null group by COST_CENTRE having Count(*) = 1 ;
KIKIKI testing seskoondeeee |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18371
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 3:53 pm: | |
perfect gochi mama.  Thanks mama srama anukokunda antha type chesinanduku, kani PT mama english lo adigadu ga, kani data ichav ga ardamavuthundi le. Thanks mama. |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24630
Registered: 07-2004
Posted From: 162.136.192.1
| | Posted on Monday, July 17, 2006 - 3:50 pm: | |
Cost_centre, State
Chicago, IL
Dallas, TX
Hartford , CT
Hartford , --
NYC , --
Edison, NJ
Austin, TX
Austin , --
San Jose, --
LA , --
LA , --
oka vela paidhi oka sample table aithe manodiki state null ayivundi,repeat kaani cost centres kaavali
so output should be NYC and Sanhose...
kaani mana timbuff maama raasina daani prakaaram mundu state nulls ni filter tharavatha count(*) = 1 ante outputlo NYC,Sanhose tho paatu Hartford,Austin kooda vachesthaayi..
so mundu repeated vi theesesi tharavatha vaatilo state null pull seyyamani septhunnaa...
 hemugaa asalu idenaa neeku kaavalsindhi...
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18368
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 3:44 pm: | |
PT mama, Thanks mama, ippudu busy ga unna, okesari anni meda paddayi, mind block ayyindi. emanukokunda malla discudam mama. |
Penkonda_tiger
Bewarse
Username: Penkonda_tiger
Post Number: 1636
Registered: 06-2005
Posted From: 62.234.167.180
| | Posted on Monday, July 17, 2006 - 3:39 pm: | |
GOCHI .. swaari , i dont think i understood the problem well .. dump a pseudoo table here ,and tell me the result set u need .. |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 251
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Monday, July 17, 2006 - 3:38 pm: | |
 mattadithe maasu porilu makku porilu antavv entehess
naathu anu ne tavaali
mana APA ki pesidentu vi ayyi vundi dicourage settavaa koosantha swet sesi pettu |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24626
Registered: 07-2004
Posted From: 162.136.192.1
| | Posted on Monday, July 17, 2006 - 3:35 pm: | |
 swaary...kaavalante old citykelli evathanna mass pigureni swaari sesuko..
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 249
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Monday, July 17, 2006 - 3:33 pm: | |
edi emaina ee swaari hyd ellinappudu ee pilla ni swaari seyyali |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24624
Registered: 07-2004
Posted From: 162.136.192.1
| | Posted on Monday, July 17, 2006 - 3:32 pm: | |
poo ante nijam poo soopisthaaranukunnaava...oka usiri kaayani sagam koriki long shotlo zoom sesi soopittaaru..daanney nuvvu veredhi anukoni thrupthi padaali 
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 247
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Monday, July 17, 2006 - 3:30 pm: | |
seppa kadaa..poo soopithe saalu carpet cleaning di emundi seppu |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24621
Registered: 07-2004
Posted From: 162.136.192.1
| | Posted on Monday, July 17, 2006 - 3:28 pm: | |
kottanle..porapaatuna kottinanduku neeku oka saari anu carpet cleaning chance bonusgaa isthunnaam...
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Proofdadaa
Pilla Bewarse
Username: Proofdadaa
Post Number: 244
Registered: 06-2006
Posted From: 129.138.18.85
| | Posted on Monday, July 17, 2006 - 3:27 pm: | |
tappu septhee kottettavaa |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24619
Registered: 07-2004
Posted From: 162.136.192.1
| | Posted on Monday, July 17, 2006 - 3:26 pm: | |
PT maama...alaa union sesthey state null unna records + state null leni repeat ayina records anni vasthaayigaa
kaani manodiki state null ayyundi,cost centre repeat kaani records anni kaavali..ejjact opposite
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Penkonda_tiger
Bewarse
Username: Penkonda_tiger
Post Number: 1634
Registered: 06-2005
Posted From: 62.234.167.180
| | Posted on Monday, July 17, 2006 - 3:21 pm: | |
Hemmu mammaa .. pardon my Telugu , i dont know if I understood ur problem properly ..
how ever check this out ...
select COST_CENTRE from fa_locations where STATE is NULL
Union
select COST_CENTRE from fa_locations where STATE is not null group by COST_CENTRE,STATE having count (*) = 1 |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18361
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 2:59 pm: | |
sare sotha aagu. vere pani vachindi.  |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18360
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 2:58 pm: | |
oh reverse seppava. kiki. |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24616
Registered: 07-2004
Posted From: 162.136.192.1
| | Posted on Monday, July 17, 2006 - 2:56 pm: | |
group by lo ante group by mundala ekkadanna ettacchu..
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18359
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 2:55 pm: | |
group by lo where clause pettocha?? |
Gochi
Bewarse ke Bewarse!
Username: Gochi
Post Number: 24614
Registered: 07-2004
Posted From: 162.136.192.1
| | Posted on Monday, July 17, 2006 - 2:54 pm: | |
select cost_center from (select * from fa_locations
having count(*) =1 ) where state is null
group by cost_center
 idhi try seyyu...
yekku toli mettu kondanu kottu deekottu gattiga patte nuvv pattu gamyam cherettu |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18358
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 2:43 pm: | |
tmbuff mama, problem vasthundi. inkokati cheppali ikkada.
aa table lo oka cost center repeat avvochu and oka daniki state undi inkoka daniki state undakapovachu.
appudu manam initial select * from ..where state is null annappudu aa repeated dantlo oka row state null di unte adi vachesthundi, kani count(*) dantlo filter avvadu.
so cost center repeat ayi, state null ayina oka row vachesthundi.
manaki kavalsindi cost center repeat avvakunda, state null unnadi kavali.
eg:cost center 123, 2 rows unnayi, oka daniki state null, inkokadaniki CA undi, indulo ee row rakodadu manaki. kani mana query aa null unna cost center row isthundi.
emi cheyyali ippudu. |
Tyrael
Bewarse
Username: Tyrael
Post Number: 1252
Registered: 05-2006
Posted From: 65.174.40.114
| | Posted on Monday, July 17, 2006 - 1:51 pm: | |
yenna cheta bingole mams |
Bignole
Vooriki Bewarse
Username: Bignole
Post Number: 3955
Registered: 03-2004
Posted From: 161.225.1.12
| | Posted on Monday, July 17, 2006 - 1:46 pm: | |
 |
Penkonda_tiger
Bewarse
Username: Penkonda_tiger
Post Number: 1633
Registered: 06-2005
Posted From: 62.234.167.180
| | Posted on Monday, July 17, 2006 - 1:23 pm: | |
KIKI Where clause looo STATE IS NULL anee endukuu adding syeaa kudaadu .. instead of using a inline view .. KIKIKI
ROLLUP luuu CUBE vadee savandee .. |
Moviemaker
Bewarse
Username: Moviemaker
Post Number: 1835
Registered: 05-2004
Posted From: 65.82.104.120
| | Posted on Monday, July 17, 2006 - 1:22 pm: | |
emugaa itanti taadulu rojuki 10 estey DB members anta oppice lo enta baga pani sestaro  |
Tmbuff
Pilla Bewarse
Username: Tmbuff
Post Number: 149
Registered: 04-2004
Posted From: 159.153.4.51
| | Posted on Monday, July 17, 2006 - 1:00 pm: | |
Tyreal --
Nested selects inside a from shouldn't be a too much of performance issue. But peformance depends on too many variables and just can't be blamed on nested selects
If the column is numeric and has less diversity, i will go with row_number function. We can also use CASE statement to see if that gives better performance. |
Tyrael
Bewarse
Username: Tyrael
Post Number: 1240
Registered: 05-2006
Posted From: 65.174.40.114
| | Posted on Monday, July 17, 2006 - 12:25 pm: | |
Tmbuff ,
performance wise ila nested select undakoodadu kada?? |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18354
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 12:25 pm: | |
mee andariki kullu. |
Cricfan
Bewarse
Username: Cricfan
Post Number: 2434
Registered: 03-2005
Posted From: 24.162.127.185
| | Posted on Monday, July 17, 2006 - 12:23 pm: | |
nee jeetham sagham DB members ivvu lekapothe mee manager ki septha
Nuvvu Man va Manchu Vamsam Pan va |
Sgparri
Pilla Bewarse
Username: Sgparri
Post Number: 23
Registered: 07-2006
Posted From: 192.44.136.103
| | Posted on Monday, July 17, 2006 - 12:22 pm: | |
hemanthaa nee pani baagundi gaa..
Last Man Standing... |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18353
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 12:21 pm: | |
Thanks mama. work ayyindi.  |
Tmbuff
Pilla Bewarse
Username: Tmbuff
Post Number: 148
Registered: 04-2004
Posted From: 159.153.4.51
| | Posted on Monday, July 17, 2006 - 12:13 pm: | |
select cost_center from (select * from fa_locations where state is null)
having count(*) =1
group by cost_center
this may help... |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18352
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 12:01 pm: | |
>>ippudu naku only rows null value unnavi,
means state null value unnavi ani. |
Hemanth
Bewarse ke Bewarse!
Username: Hemanth
Post Number: 18351
Registered: 03-2004
Posted From: 65.196.167.82
| | Posted on Monday, July 17, 2006 - 11:59 am: | |
mamalu, oka table lo oka column(state) values konni null undochu vatiki konni cost centers assign ayi undochu. adi inkoka column.
cost centers repititive undochu, ippudu naku only rows null value unnavi, cost centers repeat avvani list kavali. ela rayali quer??
select cost_center from fa_locations
having count(*) = 1
group by cost_center
ikkada state = null anedi ela add cheyyali?? |
