Pl/sql Topulu Konchem Randi Babu... Oo Sahayam Kavali

[deals2buy]

aithe CASE statements raasukovadame

[ChampakDas]

as crisp as it gets...good one man...

red: since the value is matching...we have to get the count in a variable and then use these counts to insert or do some action. If so, as per the query kaka has written has to be in a while loop because he has to check for every record in the second table...which is time consuming instead do this.....I am joining two tables who output will be the keyid with the count then do the operation insert delete whatever

[CODE]
SELECT COUNT(T1.KEYID),T1.KEYID, COUNT(T2.KEYID)
FROM TABLE1 T1
LEFT JOIN TABLE2 T1
ON T1.KEYID=T2.KEYID
GROUP
BY T1.KEYID, T2.KEYID
[/CODE]


output I got:

2 110 2
1 111 0

now in the above result you are interested in the 2nd and 3rd column. 2nd row column count is 0 because there are no records in the other table.

[quote name='Mrlavangam' timestamp='1356045441' post='1302991085']


This is what I understand from what kakateeyudu told...

Lets suppose the table has the data like this

[color=#282828][font=helvetica, arial, sans-serif]KEY1 Status[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]1 N[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]1 N[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]2 N[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]2 N[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]1 X[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]2 X[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]Now, he is trying to load a new record(might be to a different table or same table) and it has a value which he is comparing it with key1.Say if the value is 3, since there is no record with 3, it should show the count as zero.it the value is 2,[/font][/color][color=#ff0000][font=helvetica,arial,sans-serif][b] then it has to check in this table with key1 as 2[/b][/font][/color][color=#282828][font=helvetica, arial, sans-serif] and staus as 'N'..[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]Naaku ila arthamayyindi mama...kakateeyudu cheppali crrcto wrongo...and the below query satisfy chetshudi anukuntunna..[/font][/color]

select CASE when :OLD.field1= :NEW.field1 and status = 'N'
THEN 1 else 0 END into kakateyudu from afdb_table

if kakateeyudu =1
v_status:='X'
else
v_status:='N'

INSERT INTO New_tbl
(X,X,X,X )
VALUES
( :new.X,
:new.X,
:new.X,
:new.X,
v_status );
[/quote]

[Kakateyudu]

Clear ga na requirement post chestunna baa:

Table1, table2 vunnai same structure.... table 2 lo status ane field extra vuntadi..

table1 structure

Column Name
key1
key2
key3

table2 structure

key1
key2
key3
status

table 1 meda trigger rastunna and with conditions

table1 lo emanna inserts or updates inappudu same data table2 lo rasta with an extra column which is status

table2 ki data rase appudu conditions check cheyyali if key1 same value and status 'n' tho row vunte next manam rase row status 'x' tho rayali.. else status 'n' tho ne

ela status 'n' and 'x' endhi ra ayya ante... aa table2 meda inkoka trigger vuntadi adhi status 'n' tho vunna anni records ni pick cheskutadi.... so already same key1 tho rows vundi status 'n' vunte nenu danni 'x' tho rasestunna so that it doesnt pick it... anyhow same kada why anni sarlu pick cheskovatam ani

so basic ga aa table2 lo key1 and status 'n' tho yenni rows vunnai ane count oka varaible kakateyudu ane danlo store chesi.. if aa value = 0 ithe status 'n' tho rayi lekapothe 'x' tho rayi ani cheppali

hope ardam ayye basha lo ne rasa anukuntunna

[deals2buy]

naaku still ardham kaaledu nuvvu raasindi

btw ee tables ki source enti? for u to insert data into them?

[Kakateyudu]

[quote name='Kaarthikeya' timestamp='1356047685' post='1302991306']
naaku still ardham kaaledu nuvvu raasindi

btw ee tables ki source enti? for u to insert data into them?
[/quote]

basic ga ee table transaction control table baa...... chalaa tables ma tables lo data change indhi ani na table1 ki cheptai... ye table ane info kuda oka field lo istaru and unique key relate to them kuda istaru....

[mrudhula99]

[quote name='Mrlavangam' timestamp='1356044640' post='1302990999']
okasaari aa line motham vestaava...names maarchi
[/quote]
ee line marchi??

[Mrlavangam]

[quote name='Kakateeyudu©' timestamp='1356047470' post='1302991288']
Clear ga na requirement post chestunna baa:

Table1, table2 vunnai same structure.... table 2 lo status ane field extra vuntadi..

table1 structure

Column Name
key1
key2
key3

table2 structure

key1
key2
key3
status

table 1 meda trigger rastunna and with conditions

table1 lo emanna inserts or updates inappudu same data table2 lo rasta with an extra column which is status

table2 ki data rase appudu conditions check cheyyali if key1 same value and status 'n' tho row vunte next manam rase row status 'x' tho rayali.. else status 'n' tho ne

ela status 'n' and 'x' endhi ra ayya ante... aa table2 meda inkoka trigger vuntadi adhi status 'n' tho vunna anni records ni pick cheskutadi.... so already same key1 tho rows vundi status 'n' vunte nenu danni 'x' tho rasestunna so that it doesnt pick it... anyhow same kada why anni sarlu pick cheskovatam ani

so basic ga aa table2 lo key1 and status 'n' tho yenni rows vunnai ane count oka varaible kakateyudu ane danlo store chesi.. if aa value = 0 ithe status 'n' tho rayi lekapothe 'x' tho rayi ani cheppali

hope ardam ayye basha lo ne rasa anukuntunna
[/quote]
ade baa naku arthamayyindi...kani count cheyalsina avsaram ledu kada...nenu cheppina case statement try chesaava??em error vastundi??

[Mrlavangam]

[quote name='mrudhula99' timestamp='1356048578' post='1302991389']
ee line marchi??
[/quote]
neeku error vachina line ikkada raayamanna....nuvvu set anedi 2-3 times raathe aa statements anni pettu ikkada...just error name chepthe ela cheppali...edo miss chesaav

[vissu]

Eme jarugutundheee ekkada?

[mrudhula99]

[quote name='Mrlavangam' timestamp='1356048859' post='1302991410']
neeku error vachina line ikkada raayamanna....nuvvu set anedi 2-3 times raathe aa statements anni pettu ikkada...just error name chepthe ela cheppali...edo miss chesaav
[/quote]
office laptop close chesa...

repu post chestha....

[Kakateyudu]

[quote name='Mrlavangam' timestamp='1356048743' post='1302991403']
ade baa naku arthamayyindi...kani count cheyalsina avsaram ledu kada...nenu cheppina case statement try chesaava??em error vastundi??
[/quote]
yeahh try chesa baa a line lo error kodutundhi baa.... vere table update chesinappudu error em evvatled error at so and so line ane istundhi

[Mrlavangam]

[quote name='Kakateeyudu©' timestamp='1356049250' post='1302991445']
yeahh try chesa baa a line lo error kodutundhi baa.... vere table update chesinappudu error em evvatled error at so and so line ane istundhi
[/quote]
oops...neeku prob lekapothe okasaari logic pm chey baa...emanna try cheyagalanemo choostha

[Kakateyudu]

[quote name='Mrlavangam' timestamp='1356049745' post='1302991493']

oops...neeku prob lekapothe okasaari logic pm chey baa...emanna try cheyagalanemo choostha
[/quote]

Bayataki vacha mama ragane chestha

[deals2buy]

[quote name='vissu' timestamp='1356048862' post='1302991411']
Eme jarugutundheee ekkada?
[/quote]


thamari bondha happening...kurrodiki help cheyyakunda inka psycho cinemalu chustunnava ? [img]http://lh3.ggpht.com/--o7mXz3u-j4/T9VVBGzBJAI/AAAAAAAAGo0/kmj8a1-XW2g/s150/PK-1.gif[/img]