Forum Stats

  • 3,827,370 Users
  • 2,260,763 Discussions
  • 7,897,219 Comments

Discussions

multiple case statement for one column

manojthakurii
manojthakurii Member Posts: 35 Green Ribbon

Hello Sir,


Can we write a multiple case statements for a column in sql

I have only one column as a reactorance column in a table

The DB version is oracle 19.0.0.0.0

I wanted to do a calculation for a field.

I have a meter table where we have a measurement record as well as a previous measurement record for a meter.

The logic i wanted to write is


case when measurement_1 is not null and measurement_2 is not null

(measurement - previous_measurement/previous_measurement) * 100 and if this percentage deviation is < 5 then make it 'Normal'

when there is no previous measurement then take the attribute value and the calculation would be

(measurement - attribute_value)/attribute_value) * 100 and if this percentage deviation is <=5 then make it normal.


How can this be achieved this.

Appreciate your help sir

Tagged:

Answers

  • manojthakurii
    manojthakurii Member Posts: 35 Green Ribbon

    the same calculation i need to do it for Borderline Logic. The column is only one.

    I have done in this way.


    Appreciate your help in providing best solution for this.

  • Alex Nuijten
    Alex Nuijten Member Posts: 242 Silver Badge

    Post your table as well as some sample data, and a sample of the expected result.

    From what I understand now is that you might be looking for the Analytic Function LAG

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond
    edited Mar 28, 2022 10:05AM

    Hi, @User_T8UPC

    Can we write a multiple case statements for a column in sql

    CASE statements are only found in PL/SQL. Maybe you mean CASE expressions, which are not the same thing.

    A column from a table can be used in multiple CASE expressions in the same SQL statement.

    A column of the results set can depend on multiple CASE expressions. CASE expressions can be nested within other CASE expressions, but often there are simpler ways to get the same results.

    As Alex said, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. 

  • manojthakurii
    manojthakurii Member Posts: 35 Green Ribbon

    Hello Sir ,

    Thanks for your reply.

    I have written a below select statement as shown below

    I will also share data as well.

    select

    inline_view.asset_number,

    F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE as total_capacitance_attr_value,

    F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE as top_capacitance_attr_value,

    F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE as BOT_capacitance_attr_value,

    F_aMIS_MEASUREMENT7_1.MEASUREMENT AS COMPL_STACK_CURRENT_READING,

    F_aMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT AS COMPL_STACK_PREV_READING

    F_AMIS_MEASUREMENT8_1.MEASUREMENT AS C1_CURRENT_rEADING,

    F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT AS C1_PREVI_READING,

    F_AMIS_MEASUREMENT9_1.MEASUREMENT AS C2_CURRENT_READING,

    F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT AS C2_PREVIOUS_READING,


    CASE

    when INLINE_VIEW.ASSET_TYPE in ('NCT','VT','CT') THEN 'Not Applicable'


    /*when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) <5 THEN 'Normal'

    when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=5 

     and (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) <=10 THEN 'Borderline'

    when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=10 THEN 'Very Poor'

    ELSE NULL

    END */

    --------------etl calculation for COMPL_STACK_METER and TOTAL_CAPACITANCE

     

    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) <-5

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Normal'

    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < -5 

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 10 THEN 'Normal'


    ----------------

    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) >=10

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Borderline'

    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=10

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'

    -----------------------

    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) >=15

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 20 THEN 'Very Poor'

    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=15

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 20 THEN 'Very Poor'


    --------------etl calculation for C1 and TOP_CAPACITANCE

    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) <-5

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 5 THEN 'Normal'

    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < -5 

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 5 THEN 'Normal'

    ----------------------------------------------

    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) >=5

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Borderline'

    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) >=5

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'

    ------------------------------------------------------------------------------------------------

    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) >=10

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Very Poor'

    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) >=10

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 15 THEN 'Very Poor'


    --------------------------------------------------------------------------------------

    --etl calculation for C2 and BOT CAPACITANCE

    --------------------------------------------------------


    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) <-5

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 5 THEN 'Normal'

    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < -5 

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 5 THEN 'Normal'

    ----------------------------------------------

    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) >=5

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Borderline'

    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) >=5

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'

    ------------------------------------------------------------------------------------------------

    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) >=10

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Very Poor'

    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) >=10

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 15 THEN 'Very Poor'

    ELSE NULL

    end as CAPACITANCE

    from STG_AHI_ASSET_DERIVED_DATA ASSET_INFO

     INNER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE2.ALN_VALUE AS ALN_VALUE  

    FROM 

     F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE2  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETATTRID in ('CT-CONFIG/CONSTR','VT-CONFIG/CONSTR','CVT-CONFIG/CONSTR')

    and F_AMIS_ASSET_ATTRIBUTE2.IS_DELETED = 0

     ) F_AMIS_ASSET_ATTRIBUTE2_1  

      ON F_AMIS_ASSET_ATTRIBUTE2_1.SRC_ASSETNUM = AHI_ASSETS_INSTRUMENT_TF.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     DISTINCT 

     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,

     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  

    FROM 

     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  

    WHERE

     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'

    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'

    AND F_AMIS_MEASUREMENT.IS_DELETED = 0

    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )

    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL

    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-COMPL-STK'

     ) DISTINCT_MEASURE  

      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     DISTINCT 

     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,

     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  

    FROM 

     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  

    WHERE

     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'

    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'

    AND F_AMIS_MEASUREMENT.IS_DELETED = 0

    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )

    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL

    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-C1'

     ) DISTINCT_MEASURE  

      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     DISTINCT 

     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,

     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  

    FROM 

     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  

    WHERE

     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'

    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'

    AND F_AMIS_MEASUREMENT.IS_DELETED = 0

    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )

    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL

    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-C2'

     ) DISTINCT_MEASURE  

      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE AS NUMERIC_VALUE  

    FROM 

     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE_1  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETATTRID = 'TOP-CAPACITANCE'

     ) F_AMIS_ASSET_ATTRIBUTE_2  

      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETNUM 

      LEFT OUTER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE AS NUMERIC_VALUE  

    FROM 

     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE_2  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETATTRID = 'BOT-CAPACITANCE'

     ) F_AMIS_ASSET_ATTRIBUTE_3

      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_3.SRC_ASSETNUM   

    LEFT OUTER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE.NUMERIC_VALUE AS NUMERIC_VALUE  

    FROM 

     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETATTRID = 'TOTAL-CAPACITANCE'

     ) F_AMIS_ASSET_ATTRIBUTE_1  

      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETNUM

       ) 


    The above is the select statement.

    Now F_AMIS_aSSET_ATTRIBUTE is the attribute table whereas F_AMIS_MEASUREMENT is a measurement table where all the previous measurement is measrement is taken.

    The above logic for capacitance is derived as shown below

    if 2 meter readings are available then calculate percentage deviation of current_reading - previous_reading/previous_measurement * 100

    else

    current_reading - attribute_value/attribute_value * 100

    else null


    for all 3 meters 'CCAP_CAPACTNCE-COMPL-STK','CCAP_CAPACTNCE-C1','CCAP_CAPACTNCE-C2' and for meter we 'CCAP_CAPACTNCE-COMPL-STK' we should take attribute TOTAL-CAPACITANCE attribute value

    for 'CCAP_CAPACTNCE-C1' we should take 'TOP-CAPACITNCE' and 'CCAP_CAPACTNCE-C2' we should take 'BOT-CAPACITNCE'

    There will only be one measurement record for a meter and there will be only one attribute value which is numeric value for the asset. hence it will give only asset


    The output value will be

    Appreciate your help on this sir.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond

    Hi, @User_T8UPC

    I will also share data as well.

    Good! There's not much we can do until we have the sample data, desired results and explanation.

    Here are a couple of things I can say based on what you've posted so far.

    Your code would be a lot easier to read and understand if you used table aliases, and whitespace. Which do you think is easier to read (and therefore to debug and maintain: what you posted

    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) <-5
    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Normal'
    

    or this equivalent

    when F7.MEASUREMENT is not null and F7.PREVIOUS_MEASUREMENT IS NOT NULL AND
        (((F7.MEASUREMENT - F7.PREVIOUS_MEASUREMENT) / F7.PREVIOUS_MEASUREMENT) * 100) < -5
    AND (((F7.MEASUREMENT - F7.PREVIOUS_MEASUREMENT) / F7.PREVIOUS_MEASUREMENT) * 100) < 10 THEN 'Normal'
    

    ? When you do that, how redundant those conditions are becomes clear. The second two conditions are comparing the same number, first to -5 and then to 10. If that number is less than -5, then it will also be less than 10, so the last condition isn't changing the results at all. Also, if either F7.MEASUREMENT or F7.PREVIOUS_MEASUREMENT is NULL, then

    (((F7.MEASUREMENT - F7.PREVIOUS_MEASUREMENT) / F7.PREVIOUS_MEASUREMENT)
    

    will also be NULL, which isn't be less than -5, so the entire clause could be simplified as

    when (((F7.MEASUREMENT - F7.PREVIOUS_MEASUREMENT) / F7.PREVIOUS_MEASUREMENT) * 100) < -5
    THEN 'Normal'
    


  • manojthakurii
    manojthakurii Member Posts: 35 Green Ribbon

    The query can be written as shown below

    --The logic is if current_measurement and previos_reading is present  for complete_stack then calculate the percentage
    --deviation of capacitance as shown below
    
    --current_reading - previous_reading/previous_reading * 100 and if this deviaton is Normal: < ±5% (including assets with no meter data)
    
    --Borderline: >= ±5% and <±10%
    
    --Very Poor: >= ±10% and <±15%
    
    --Null: for improbable values eg ±100%  
    --else take the attribute value for total_Capacitance and caluclate 
    --current_measurement - total_capacitance_attr_value/total_capacitance_attr_value * 100 and if this deviation is Normal: < ±5% (including assets with no meter data)
    
    --Borderline: >= ±5% and <±10%
    
    --Very Poor: >= ±10% and <±15%
    
    --Null: for improbable values eg ±100%  
    --  The same calculation i have done it for C1_CURRENT_rEADING. for c1 meter if previous_reading is null then take top_capacitance_attr_value
    -- calculation i have done it for C1_CURRENT_rEADING. for c1 meter if previous_reading is null then take BOT_capacitance_attr_value
    
    
    
    
    
    
    select
    
    inline_view.asset_number,
    
    F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE as total_capacitance_attr_value, --- this is attribute value for total_capacitance
    
    F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE as top_capacitance_attr_value, -- this is the attribute value for TOP_CAPACITANCE
    
    F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE as BOT_capacitance_attr_value,-- this is the attribute value for BOT_CAPACITANCE
    
    F_aMIS_MEASUREMENT7_1.MEASUREMENT AS COMPL_STACK_CURRENT_READING, -- current measurement for complete_stack
    
    F_aMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT AS COMPL_STACK_PREV_READING, -- previous measurement for complete stack
    
    F_AMIS_MEASUREMENT8_1.MEASUREMENT AS C1_CURRENT_rEADING, -- CURRENT_MEASUREMENT FOR C1 METER
    
    F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT AS C1_PREVI_READING,  -- PREVIOUS_MEASUREMENT FOR C1 METER
    
    F_AMIS_MEASUREMENT9_1.MEASUREMENT AS C2_CURRENT_READING,  -- CURRENT_MEASUREMENT FOR C2 METER
    
    F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT AS C2_PREVIOUS_READING,-- CURRENT_MEASUREMENT FOR C2 METER
    
    
    
    
    CASE
    
    when INLINE_VIEW.ASSET_TYPE in ('NCT','VT','CT') THEN 'Not Applicable'
    
    
    
    
    /*when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) <5 THEN 'Normal'
    
    when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=5 
    
     and (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) <=10 THEN 'Borderline'
    
    when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=10 THEN 'Very Poor'
    
    ELSE NULL
    
    END */
    
    --------------etl calculation for COMPL_STACK_METER and TOTAL_CAPACITANCE
    
     
    
    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) >-5
    
    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Normal'
    
    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) > -5 
    
    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 10 THEN 'Normal'
    
    
    
    
    ----------------
    
    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) >=10
    
    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Borderline'
    
    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=10
    
    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'
    
    -----------------------
    
    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) >=15
    
    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 20 THEN 'Very Poor'
    
    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=15
    
    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 20 THEN 'Very Poor'
    
    
    
    
    --------------etl calculation for C1 and TOP_CAPACITANCE
    
    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) >-5
    
    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 5 THEN 'Normal'
    
    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) > -5 
    
    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 5 THEN 'Normal'
    
    ----------------------------------------------
    
    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) >=5
    
    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Borderline'
    
    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) >=5
    
    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'
    
    ------------------------------------------------------------------------------------------------
    
    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) >=10
    
    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Very Poor'
    
    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) >=10
    
    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 15 THEN 'Very Poor'
    
    
    
    
    --------------------------------------------------------------------------------------
    
    --etl calculation for C2 and BOT CAPACITANCE
    
    --------------------------------------------------------
    
    
    
    
    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) >-5
    
    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 5 THEN 'Normal'
    
    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < -5 
    
    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 5 THEN 'Normal'
    
    ----------------------------------------------
    
    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) >=5
    
    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Borderline'
    
    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) >=5
    
    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'
    
    ------------------------------------------------------------------------------------------------
    
    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT IS NOT NULL AND
    
     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) >=10
    
    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Very Poor'
    
    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) >=10
    
    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 15 THEN 'Very Poor'
    
    ELSE NULL
    
    end as CAPACITANCE
    
    from STG_AHI_ASSET_DERIVED_DATA ASSET_INFO
    
     INNER JOIN (
    
    SELECT 
    
     F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETNUM AS SRC_ASSETNUM ,
    
     F_AMIS_ASSET_ATTRIBUTE2.ALN_VALUE AS ALN_VALUE  
    
    FROM 
    
     F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE2  
    
    WHERE
    
     (F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETATTRID in ('CT-CONFIG/CONSTR','VT-CONFIG/CONSTR','CVT-CONFIG/CONSTR')
    
    and F_AMIS_ASSET_ATTRIBUTE2.IS_DELETED = 0
    
    ) 
    
     ) F_AMIS_ASSET_ATTRIBUTE2_1  
    
      ON F_AMIS_ASSET_ATTRIBUTE2_1.SRC_ASSETNUM = AHI_ASSETS_INSTRUMENT_TF.ASSET_NUMBER
    
    LEFT OUTER JOIN (
    
    SELECT 
    
     DISTINCT 
    
     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,
    
     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  
    
    FROM 
    
     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  
    
    WHERE
    
     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'
    
    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'
    
    AND F_AMIS_MEASUREMENT.IS_DELETED = 0
    
    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
    
    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL
    
    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-COMPL-STK'
    
    ) 
    
     ) DISTINCT_MEASURE  
    
      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER
    
    LEFT OUTER JOIN (
    
    SELECT 
    
     DISTINCT 
    
     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,
    
     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  
    
    FROM 
    
     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  
    
    WHERE
    
     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'
    
    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'
    
    AND F_AMIS_MEASUREMENT.IS_DELETED = 0
    
    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
    
    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL
    
    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-C1'
    
    ) 
    
     ) DISTINCT_MEASURE  
    
      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER
    
    LEFT OUTER JOIN (
    
    SELECT 
    
     DISTINCT 
    
     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,
    
     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  
    
    FROM 
    
     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  
    
    WHERE
    
     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'
    
    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'
    
    AND F_AMIS_MEASUREMENT.IS_DELETED = 0
    
    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )
    
    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL
    
    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-C2'
    
    ) 
    
     ) DISTINCT_MEASURE  
    
      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER
    
    LEFT OUTER JOIN (
    
    SELECT 
    
     F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETNUM AS SRC_ASSETNUM ,
    
     F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE AS NUMERIC_VALUE  
    
    FROM 
    
     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE_1  
    
    WHERE
    
     (F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETATTRID = 'TOP-CAPACITANCE'
    
    ) 
    
     ) F_AMIS_ASSET_ATTRIBUTE_2  
    
      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETNUM 
    
      LEFT OUTER JOIN (
    
    SELECT 
    
     F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETNUM AS SRC_ASSETNUM ,
    
     F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE AS NUMERIC_VALUE  
    
    FROM 
    
     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE_2  
    
    WHERE
    
     (F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETATTRID = 'BOT-CAPACITANCE'
    
    ) 
    
     ) F_AMIS_ASSET_ATTRIBUTE_3
    
      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_3.SRC_ASSETNUM   
    
    LEFT OUTER JOIN (
    
    SELECT 
    
     F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETNUM AS SRC_ASSETNUM ,
    
     F_AMIS_ASSET_ATTRIBUTE.NUMERIC_VALUE AS NUMERIC_VALUE  
    
    FROM 
    
     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE  
    
    WHERE
    
     (F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETATTRID = 'TOTAL-CAPACITANCE'
    
    ) 
    
     ) F_AMIS_ASSET_ATTRIBUTE_1  
    
      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETNUM
    
       ) 
    

    I have also attached the sample data for your reference


    I will change the condition for Normal as well.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond

    Hi, @User_T8UPC

    I have also attached the sample data for your reference

    You need to post CREATE TABLE and INSERT statements for the sample data, so people can re-create the data on their own systems.

    It's okay to post the desired results as an image if the print is big enough for people to read.

  • manojthakurii
    manojthakurii Member Posts: 35 Green Ribbon

    Hello Sir,

    Thank you for this info. I have formatted the code now

    [code]

    Select

    inline_view.asset_number,

    F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE as total_capacitance_attr_value,

    F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE as top_capacitance_attr_value,

    F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE as BOT_capacitance_attr_value,

    F_aMIS_MEASUREMENT7_1.MEASUREMENT AS COMPL_STACK_CURRENT_READING,

    F_aMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT AS COMPL_STACK_PREV_READING

    F_AMIS_MEASUREMENT8_1.MEASUREMENT AS C1_CURRENT_rEADING,

    F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT AS C1_PREVI_READING,

    F_AMIS_MEASUREMENT9_1.MEASUREMENT AS C2_CURRENT_READING,

    F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT AS C2_PREVIOUS_READING,


    CASE

    when INLINE_VIEW.ASSET_TYPE in ('NCT','VT','CT') THEN 'Not Applicable'


    /*when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) <5 THEN 'Normal'

    when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=5 

     and (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) <=10 THEN 'Borderline'

    when (((F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE - F_AMIS_MEASUREMENT7_1.MEASUREMENT)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=10 THEN 'Very Poor'

    ELSE NULL

    END */

    --------------etl calculation for COMPL_STACK_METER and TOTAL_CAPACITANCE

     

    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) <-5

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Normal'

    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < -5 

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 10 THEN 'Normal'


    ----------------

    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) >=10

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Borderline'

    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=10

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'

    -----------------------

    when F_AMIS_MEASUREMENT7_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100) >=15

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT7_1.PREVIOUS_MEASUREMENT)*100)< 20 THEN 'Very Poor'

    WHEN (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) >=15

    AND (((F_AMIS_MEASUREMENT7_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE)*100) < 20 THEN 'Very Poor'


    --------------etl calculation for C1 and TOP_CAPACITANCE

    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) <-5

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 5 THEN 'Normal'

    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < -5 

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 5 THEN 'Normal'

    ----------------------------------------------

    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) >=5

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Borderline'

    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) >=5

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'

    ------------------------------------------------------------------------------------------------

    when F_AMIS_MEASUREMENT8_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100) >=10

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Very Poor'

    WHEN (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) >=10

    AND (((F_AMIS_MEASUREMENT8_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE)*100) < 15 THEN 'Very Poor'


    --------------------------------------------------------------------------------------

    --etl calculation for C2 and BOT CAPACITANCE

    --------------------------------------------------------


    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) <-5

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 5 THEN 'Normal'

    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < -5 

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 5 THEN 'Normal'

    ----------------------------------------------

    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) >=5

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT8_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 10 THEN 'Borderline'

    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) >=5

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 10 THEN 'Borderline'

    ------------------------------------------------------------------------------------------------

    when F_AMIS_MEASUREMENT9_1.MEASUREMENT is not null and F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT IS NOT NULL AND

     (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100) >=10

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)/F_AMIS_MEASUREMENT9_1.PREVIOUS_MEASUREMENT)*100)< 15 THEN 'Very Poor'

    WHEN (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) >=10

    AND (((F_AMIS_MEASUREMENT9_1.MEASUREMENT - F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)/F_AMIS_ASSET_ATTRIBUTE_3.NUMERIC_VALUE)*100) < 15 THEN 'Very Poor'

    ELSE NULL

    end as CAPACITANCE

    from STG_AHI_ASSET_DERIVED_DATA ASSET_INFO

     INNER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE2.ALN_VALUE AS ALN_VALUE  

    FROM 

     F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE2  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE2.SRC_ASSETATTRID in ('CT-CONFIG/CONSTR','VT-CONFIG/CONSTR','CVT-CONFIG/CONSTR')

    and F_AMIS_ASSET_ATTRIBUTE2.IS_DELETED = 0

     ) F_AMIS_ASSET_ATTRIBUTE2_1  

      ON F_AMIS_ASSET_ATTRIBUTE2_1.SRC_ASSETNUM = AHI_ASSETS_INSTRUMENT_TF.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     DISTINCT 

     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,

     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  

    FROM 

     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  

    WHERE

     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'

    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'

    AND F_AMIS_MEASUREMENT.IS_DELETED = 0

    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )

    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL

    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-COMPL-STK'

     ) DISTINCT_MEASURE  

      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     DISTINCT 

     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,

     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  

    FROM 

     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  

    WHERE

     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'

    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'

    AND F_AMIS_MEASUREMENT.IS_DELETED = 0

    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )

    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL

    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-C1'

     ) DISTINCT_MEASURE  

      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     DISTINCT 

     F_AMIS_MEASUREMENT.MEASUREMENT AS MEASUREMENT ,

     F_AMIS_MEASUREMENT.SRC_ASSETNUM AS SRC_ASSETNUM  

    FROM 

     BI_ADS.F_AMIS_MEASUREMENT F_AMIS_MEASUREMENT  

    WHERE

     (F_AMIS_MEASUREMENT.CURRENT_READING = 'Y'

    AND F_AMIS_MEASUREMENT.CURRENT_READING_EXISTS = 'Y'

    AND F_AMIS_MEASUREMENT.IS_DELETED = 0

    AND F_AMIS_MEASUREMENT.MEASUREMENT NOT IN ( 9999999,999999,99999,9999,999 )

    AND F_AMIS_MEASUREMENT.MEASUREMENT IS NOT NULL

    AND F_AMIS_MEASUREMENT.SRC_METERNAME = 'CCAP_CAPACTNCE-C2'

     ) DISTINCT_MEASURE  

      ON DISTINCT_MEASURE.SRC_ASSETNUM = INLINE_VIEW.ASSET_NUMBER

    LEFT OUTER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE_1.NUMERIC_VALUE AS NUMERIC_VALUE  

    FROM 

     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE_1  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETATTRID = 'TOP-CAPACITANCE'

     ) F_AMIS_ASSET_ATTRIBUTE_2  

      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETNUM 

      LEFT OUTER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE_2.NUMERIC_VALUE AS NUMERIC_VALUE  

    FROM 

     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE_2  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE_2.SRC_ASSETATTRID = 'BOT-CAPACITANCE'

     ) F_AMIS_ASSET_ATTRIBUTE_3

      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_3.SRC_ASSETNUM   

    LEFT OUTER JOIN (

    SELECT 

     F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETNUM AS SRC_ASSETNUM ,

     F_AMIS_ASSET_ATTRIBUTE.NUMERIC_VALUE AS NUMERIC_VALUE  

    FROM 

     BI_ADS.F_AMIS_ASSET_ATTRIBUTE F_AMIS_ASSET_ATTRIBUTE  

    WHERE

     (F_AMIS_ASSET_ATTRIBUTE.SRC_ASSETATTRID = 'TOTAL-CAPACITANCE'

     ) F_AMIS_ASSET_ATTRIBUTE_1  

      ON INLINE_VIEW.ASSET_NUMBER = F_AMIS_ASSET_ATTRIBUTE_1.SRC_ASSETNUM

       ) 

    [/code]

    The below is the sample data. The CAPACITANCE column is the calculated column value.


    I will change the condition for Normal and will keep only one comparison rather than making it redundant.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond

    Hi, @User_T8UPC

     The below is the sample data.

    You still need to post CREATE TABLE and INSERT statements for the sample data and the exact results you want from that data