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:

    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:

    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 2011
    Location
    Glasgow, UK
    Posts
    36
    Few key questions:
    1. What DB and OS are you using?
    2. Where did you get this code from?
    3. This doesn't look like SQL Server -- Is it MS Access?
    4. Does your query as it stand return anything? Does return an error message?

    I noticed you've posted your query to multiple forums (SQLServer, Oracle): Do you have a firm grasp in what tools you are using and the realization of the fundamental goals you wish to achieve?

    Another point thread title of "Query Help Needed" is unlikely to get you much traffic. Try to be more descriptive on your main goal. For example, "Problem crosstab query with sum calcs"

    Just trying to help: there are tonnes of zero reply threads due to vagueness or unclear requirements.

    Marc
    Last edited by marc_; 09-01-11 at 06:51. Reason: Typo

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    Hi marc,

    I'm getting this code from the Query builder of Meridium, which is a software used to manage asset performance. Now, one of the features of Meridium is the Query builder, where I can get data from multiple tables.

    When I run the code through Meridium, I get the second table above.

    By the way, when I posted my question in Oracle forum I thought the code is Oracle but someone there told me it isn't. Thus, I started this one here.

  4. #4
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Check with your DBAs for your company what DBMS Meridium connects to - that'd probably be a good start. Decode and trunc do seem to suggest Oracle. But TRANSFORM and PIVOT in the same query -- I do not recoginse.

    Not sure if it helps but: sum() is used to sum columns. To sum rows you'll need to add them together:

    select col1, col2, col3, (col1+col2+col3) total
    from mytab.

    Cheers,
    Marc

Posting Permissions

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