Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: Query Help Needed

    Hi guys,

    I need your help getting this query i'm trying to build right.

    This is my query:

    Code:
    TRANSFORM Count([MC_FNCLOC00].[MC_FNCLOC00_FNC_LOC_C])
     PIVOT Decode([MI Security User].[SEUS_ID], '8930', 'Operations Status', '7413', 'Operations Status', '566901', 'Operations Status', '6901', 'Operations Status', '7165', 'Operations Status', '7391', 'CS Status', '7020', 'Operations Status', '29986', 'Operations Status', 
    
    '7377', 'Operations Status', '6707', 'Operations Status', '7034', 'Operations Status', '6925', 'Operations Status', '1783', 'CS Status', '30965', 'Operations Status')
    SELECT Decode([MC_FNCLOC00].[MC_FNCLOC00_HIER_LVL_2_C], 'SD04', 'MTBE', 'SD05', 'ETHY', 'SD10', 'EBS', 'SD17', 'CA/EDC', 'SD19', 'CA/EDC', 'SD29', 'SALT', 'SD80', 'UDD') "Plant", Count(Decode([MC_FNCLOC00].[MC_FNCLOC00_HIER_LVL_2_C], 'SD04', 
    
    'MTBE', 'SD05', 'ETHY', 'SD10', 'EBS', 'SD17', 'CA/EDC', 'SD19', 'CA/EDC', 'SD29', 'SALT', 'SD80', 'UDD')) "Total", Decode([MC_FNCLOC00].[MC_FNCLOC00_HIER_LVL_2_C], 'SD04', 2404, 'SD05', 4499, 'SD10', 3146, 'SD19', 4707, 'SD17', 1000, 'SD29', 567, 'SD80', 
    
    4174) "Target", TRUNC(((Count([MC_FNCLOC00].[MC_FNCLOC00_FNC_LOC_C]) / Decode([MC_FNCLOC00].[MC_FNCLOC00_HIER_LVL_2_C], 'SD04', 2404, 'SD05', 4499, 'SD10', 3146, 'SD19', 4707, 'SD17', 1000, 'SD29', 567, 'SD80', 4174)) * 100), 2) "Total 
    
    Completion %"
    FROM [MC_FNCLOC00] JOIN_SUCC [MC_LOCAT000] JOIN_SUCC [MC_ASSET000] ON {MCR_HSASSET} ON {MCR_HSLOCSPC} JOIN [MI Security User] JOIN_PRED [MI Human Resource] ON {MI Is a User} ON [MC_LOCAT000].LAST_UPBY_SEUS_KEY = [MI 
    
    Security User].ENTY_KEY
    WHERE [MC_LOCAT000].[MC_LOCAT000_RISK_N] > 0
    GROUP BY Decode([MC_FNCLOC00].[MC_FNCLOC00_HIER_LVL_2_C], 'SD04', 'MTBE', 'SD05', 'ETHY', 'SD10', 'EBS', 'SD17', 'CA/EDC', 'SD19', 'CA/EDC', 'SD29', 'SALT', 'SD80', 'UDD'), Decode([MC_FNCLOC00].[MC_FNCLOC00_HIER_LVL_2_C], 'SD04', 2404, 'SD05', 
    
    4499, 'SD10', 3146, 'SD19', 4707, 'SD17', 1000, 'SD29', 567, 'SD80', 4174)
    HAVING Decode([MC_FNCLOC00].[MC_FNCLOC00_HIER_LVL_2_C], 'SD04', 2404, 'SD05', 4499, 'SD10', 3146, 'SD19', 4707, 'SD17', 1000, 'SD29', 567, 'SD80', 4174) > 10
    Now, this will give the following table:

    Code:
    Plant		Operations Status	CS Status	Target		Total		Total Completion %
    CA/EDC		500			600		4254		1100		27.5
    SALT		10			10		200		20		10
    UDD		100			900		10000		1000		10
    EBS		4200			211		19000		4411		23.22
    ETHY		500			500		5000		1000		20
    MTBE		150			522		8000		672		8.4
    CA/EDC		366			214		4254		580		13.63

    What I want to do is to combine (sum) all the values of the 1st row (CA/EDC) with the last one also called (CA/EDC), but (Target) value I don't want to combine.

    Your help is really appreciated.

    BR,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Sir StressLess,

    Please note this sub-forum is for Oracle RDBMS.
    I do not recognize the language or syntax for the "code" you posted.
    Are you lost or confused or have I finally turned 100% senile?

    Have A Nice Day!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    Actually i'm not sure !
    But i think this is Microsoft SQL code.

    Anyway, i started a new post in Microsoft SQL forum.

    Sorry guys.

    By the way, is it possible that i remove this post ?

    Regards,

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You can't remove it, but you can report your message to a moderator and someone will do that for you. A "Report Post" button is located in the upper right corner of a message.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •