Sql Experts Urgent Help

[nenu_devudni]

edi na test data, indulo based on alert_msg i need to priorotize and show just one result in a cursor for all the records based on minv_code 

 

ex # minv_code is 873939 and has 3 alert_msg #  ..and priority 1 is Meter, if no meter next priority is Tamper and then Reverse, 

deniki sql query ela rayali   CITI#H@

 

minv_code  alert_msg          alert_toime

873939     Reverse          7/24/2015 3:31:18 AM

873939     Tamper           7/24/2015 3:30:00 AM

873939     Meter             7/24/2015 3:31:22 AM

[nenu_devudni]

Ltt  CITI#H@

[mtkr]

k

[Hitman]

select MAX(CASE lo 1 = Meter, 2 = Tamper, 3 = Reverse pettuko.. ) .so edi unte adi vastadi..

[nenu_devudni]

select MAX(CASE lo 1 = Meter, 2 = Tamper, 3 = Reverse pettuko.. ) .so edi unte adi vastadi..

SELECT uwtsmee_prem_code,

       uwtsmee_model,

       uwtsmee_asvc_code,

       uwtsmee_alert_time,

       uwtsmee_alert_type,

       uwtsmee_alert_msg,

     MAX(CASE WHEN uwtsmee_alert_type = 'Meter Removal' 

          Then 1 

          else 0

          WHEN uwtsmee_alert_type = 'Tamper Alert' 

          Then 1 

          else 0

          WHEN uwtsmee_alert_type = 'Reverse Energy' 

          Then 1 

          else 0) 

     END alert_type 

from uwtsmee  

where uwtsmee_work_ind is null

and uwtsmee_prem_code = '1018385'

 

TRIED THIS bhayya .. invalid arhuments antundi 

 

[ranku_mogudu]

Question koddiga clarity ivvu bro...alert message data ee table nunchi velthundhi ee column ni populate chesthundhi. Nuvvu adigina question ki raasina query ki relation kanipisthaledhu

[ranku_mogudu]

try this.... I modified your query a

 

SELECT UWTSMEE_PREM_CODE,

       UWTSMEE_MODEL,

       UWTSMEE_ASVC_CODE,

       UWTSMEE_ALERT_TIME,

       UWTSMEE_ALERT_TYPE,

       UWTSMEE_ALERT_MSG,

       CASE WHEN UWTSMEE_ALERT_TYPE = 'METER REMOVAL'  THEN 1 

            WHEN UWTSMEE_ALERT_TYPE = 'TAMPER ALERT'   THEN 2  

            WHEN UWTSMEE_ALERT_TYPE = 'REVERSE ENERGY' THEN 3  

            ELSE 0

           END AS ALERT_TYPE

FROM UWTSMEE  

WHERE UWTSMEE_WORK_IND IS NULL

AND UWTSMEE_PREM_CODE = '1018385'

[Hitman]

 

SELECT uwtsmee_prem_code,

       uwtsmee_model,

       uwtsmee_asvc_code,

       uwtsmee_alert_time,

       uwtsmee_alert_type,

       uwtsmee_alert_msg,

     MAX(CASE WHEN uwtsmee_alert_type = 'Meter Removal' 

          Then 1 

          else 0

          WHEN uwtsmee_alert_type = 'Tamper Alert' 

          Then 1 

          else 0

          WHEN uwtsmee_alert_type = 'Reverse Energy' 

          Then 1 

          else 0) 

     END alert_type 

from uwtsmee  

where uwtsmee_work_ind is null

and uwtsmee_prem_code = '1018385'

 

TRIED THIS bhayya .. invalid arhuments antundi 

 

 

I couldnt replicate.. but looking at the Query.. it is missing Group BY since you are using aggregate function..

 

Try something like this.. my answer is little bit generic ..

 

SELECT minv_code, Decode(MIN(CASE alert_msg WHEN  'Reverse' THEN 3 WHEN 'Tamper' THEN 2 WHEN  'Meter' THEN 1 END),1,'Meter',2,'Tamper',3,'Reverse') FROM MyTable GROUP BY minv_code;

 

I tried to get MIN available number and then decoded it.


 

[nenu_devudni]

 

try this.... I modified your query a

 

SELECT UWTSMEE_PREM_CODE,

       UWTSMEE_MODEL,

       UWTSMEE_ASVC_CODE,

       UWTSMEE_ALERT_TIME,

       UWTSMEE_ALERT_TYPE,

       UWTSMEE_ALERT_MSG,

       CASE WHEN UWTSMEE_ALERT_TYPE = 'METER REMOVAL'  THEN 1 

            WHEN UWTSMEE_ALERT_TYPE = 'TAMPER ALERT'   THEN 2  

            WHEN UWTSMEE_ALERT_TYPE = 'REVERSE ENERGY' THEN 3  

            ELSE 0

           END AS ALERT_TYPE

FROM UWTSMEE  

WHERE UWTSMEE_WORK_IND IS NULL

AND UWTSMEE_PREM_CODE = '1018385'

 

 

 

NAKU jus one record ravali bhayya ... meter removal exists for minv_code then dispay just that one record, and then if Meter Removal does not exist then check if Tamper Alert exists 

 then display just that one record ala .. i need justone record based on priority ..  bye1

[SUbba LIngam]

 

SELECT uwtsmee_prem_code,

       uwtsmee_model,

       uwtsmee_asvc_code,

       uwtsmee_alert_time,

       uwtsmee_alert_type,

       uwtsmee_alert_msg,

     MAX(CASE WHEN uwtsmee_alert_type = 'Meter Removal' 

          Then 1 

          else 0

          WHEN uwtsmee_alert_type = 'Tamper Alert' 

          Then 1 

          else 0

          WHEN uwtsmee_alert_type = 'Reverse Energy' 

          Then 1 

          else 0) 

     END alert_type 

from uwtsmee  

where uwtsmee_work_ind is null

and uwtsmee_prem_code = '1018385'

 

TRIED THIS bhayya .. invalid arhuments antundi 

 

 

MAX and CASE - starting and closing brackets mess up ayyayi paina query lo, try to rewrite it correctly and see

[CheGuevara]

Try this

Select * from
(

SELECT UWTSMEE_PREM_CODE,
UWTSMEE_MODEL,
UWTSMEE_ASVC_CODE,
UWTSMEE_ALERT_TIME,
UWTSMEE_ALERT_TYPE,
UWTSMEE_ALERT_MSG,
CASE WHEN UWTSMEE_ALERT_TYPE = 'METER REMOVAL' THEN 1
WHEN UWTSMEE_ALERT_TYPE = 'TAMPER ALERT' THEN 2
WHEN UWTSMEE_ALERT_TYPE = 'REVERSE ENERGY' THEN 3
ELSE 0
END AS ALERT_TYPE
FROM UWTSMEE
WHERE UWTSMEE_WORK_IND IS NULL
AND UWTSMEE_PREM_CODE = '1018385')
Where alert_type = 1;

[nenu_devudni]

Try this

Select * from
(

SELECT UWTSMEE_PREM_CODE,
UWTSMEE_MODEL,
UWTSMEE_ASVC_CODE,
UWTSMEE_ALERT_TIME,
UWTSMEE_ALERT_TYPE,
UWTSMEE_ALERT_MSG,
CASE WHEN UWTSMEE_ALERT_TYPE = 'METER REMOVAL' THEN 1
WHEN UWTSMEE_ALERT_TYPE = 'TAMPER ALERT' THEN 2
WHEN UWTSMEE_ALERT_TYPE = 'REVERSE ENERGY' THEN 3
ELSE 0
END AS ALERT_TYPE
FROM UWTSMEE
WHERE UWTSMEE_WORK_IND IS NULL
AND UWTSMEE_PREM_CODE = '1018385')
Where alert_type = 1;

(.L@  thanks bhayya .. this worked :)  

 

thank you all ..  ))(<

[SUbba LIngam]

(.L@  thanks bhayya .. this worked :)

 

thank you all ..  ))(<

 

i think it will get you only priority with meter removal, just check for others

[loveindia]

devudu adi work avadu ga, what if you don't have meter for that record and it does have only tamper... it won't pickup the second priority item man.... 

[loveindia]

i think it will get you only priority with meter removal, just check for others

 

corresht man...