display item month by month for the specific item, respectively

Posted on 2004-03-31
Last Modified: 2007-12-19
SELECT Customer_item_name, sum(Quantity) AS All_Item
FROM msc_sup_dem_entries@APS1159
WHERE publisher_order_type_desc like 'Order forecast'
AND TO_CHAR(Receipt_Date, 'YYYY') = '2004'
GROUP BY customer_item_name

CUSTOMER_ITEM_NAME               ALL_ITEM        JAN      FEB    MAR........DEC
01000371TIM                               295874             ?          ?        ?
01000421TIM                               259873             ?          ?        ?

how to retrieve out those quantity inside various months of 010000371TIM~???

01000371TIM                              295874             1589      0     2589...........
Question by:ne_ready
LVL 25

Accepted Solution

lwadwell earned 125 total points
ID: 10722247
Hi ne_ready,

use a series of decode statements...use the following as an example...

SELECT Customer_item_name,
       sum(decode(to_char(Receipt_Date, 'MM'), '01', Quantity, 0)) AS Jan,
       sum(decode(to_char(Receipt_Date, 'MM'), '02', Quantity, 0)) AS Feb,
       sum(decode(to_char(Receipt_Date, 'MM'), '03', Quantity, 0)) AS Mar
FROM msc_sup_dem_entries@APS1159
WHERE publisher_order_type_desc like 'Order forecast'
AND TO_CHAR(Receipt_Date, 'YYYY') = '2004'
GROUP BY customer_item_name

Author Comment

ID: 10722519 work~!!!

Author Comment

ID: 10756394
Iwadwell, i have a question want to ask u, hope u can help me~! thanks.....

SELECT a.customer_item_name, a.item_description, b.item_cost,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0)) AS January,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0)) AS February,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '03', a.Quantity, 0)) AS March,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '04', a.Quantity, 0)) AS April,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '05', a.Quantity, 0)) AS Mei,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '06', a.Quantity, 0)) AS Jun,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '07', a.Quantity, 0)) AS July,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '08', a.Quantity, 0)) AS August,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '09', a.Quantity, 0)) AS September,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '10', a.Quantity, 0)) AS October,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '11', a.Quantity, 0)) AS November,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '12', a.Quantity, 0)) AS December,
       sum(Quantity) AS Total_Item
FROM msc_sup_dem_entries@APS1159 a, cst_item_cost_details_v b
WHERE a.publisher_order_type_desc like 'Order forecast'
AND TO_CHAR(a.Receipt_Date, 'YYYY') = '2004'
AND a.inventory_item_id = b.inventory_item_id
AND b.cost_type_id = 1
GROUP BY a.customer_item_name, a.item_description, b.item_cost

why it came out the duplicate CUSTOMER_ITEM_NAME and duplicate ITEM_COST after i add-in the B.ITEM_COST~!???? is there anything wrong with the script~!??  Before of doing that, it just display a single's customer_item_name and the total month's quantity for the specific month only~!!! What i want to get is, i want to display out the item cost (unit price) for the specific item only~!!!

01000601TJM                       ABCDEF                          0.3658           20158

LVL 25

Expert Comment

ID: 10761668

GROUP BY does what the name might suggest, groups the results into the distinct combinations that exist in the group by list.  When you have three columns in the group by, all three columns must be the same before they can be considered as 'duplicate'.  I doubt that Oracle is doing this in your case...something on each returned row for 'a.customer_item_name, a.item_description, b.item_cost' must differ from all other rows.  For example...

01000601TJM             ABCDEF               0.3658        20158
01000601TJM             ABCDEF               0.4           99

are not duplicates because the ITEM_COST values differ (0.3658 vs 0.4).  Or...

01000601TJM             ABCDEF               0.3658        20158
01000601TJM             ZYXWVU               0.3658        99

are not duplicates because the ITEM_DESCRIPTION values differ (ABCDEF vs ZYXWVU).

I don't have enough information to suggest a solution yet...I need to understand the relationship between the two tables and the various columns.  You are grouping by customer_item_name yet you are joining using inventory_item_id, are these 1:1?

I need to understand the data much more...

Author Comment

ID: 10771061
Iwadwell, i knew the answer, because it have a different cost_element on it, some of them are duplicated~!!  :-)
So, how do i calc the item_cost (unit price) * quantity follow with month by month basic~!??? Do i need to declare the variable something like -> total  number(15)~???

CUSTOMER_ITEM_NAME      ITEM_COST       JANUARY      Total.............Dec
01001931MMTF                    0.236                 2500            590

Hope u can help me, cause i very new in PL/SQL~!!!
LVL 25

Expert Comment

ID: 10771177
what do you mean by "duplicated"...?  I am assuming this is two rows in the cst_item_cost_details_v table with the same inventory_item_id.  Please provide an might help me understand better.  Is the item_cost the same in each row?...if so, maybe just doing a max(item_cost) is all you need to do...

Author Comment

ID: 10771471
ops.....sorry~!!! Actually inside the table, it has a column called COST_ELEMENT, and it is categorize as follow : Material, Resource and so on....for the specific item_cost, respectively~! Now, i just want to select out those ITEM_COST which is based on Material inside the COST_ELEMENT. Finally, i wanna do a calculation of each month~!!

CUSTOMER_ITEM_NAME      ITEM_COST       JANUARY      Total       February     Total
01001931MMTF                    0.236                 2500            590          1458        344.088
010A0016801Q                     0.36                   258             92.88        xxxx          xxxx
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 25

Expert Comment

ID: 10771697
ok...lets see if I understand what you are saying...

the cst_item_cost_details_v has multiple rows, with differing COST_ELEMENT values.

in short, you need to be able to join to one, and only one, row in the CST_ITEM_COST_DETAILS_V table for each value of INVENTORY_ITEM_ID.  You need to tell me how this can be done...?  Or is this the sum(item_cost) for these rows...?

if the following SQL returns rows, then we have a problem...

from   cst_item_cost_details_v
where  cost_type_id = 1
and    cost_element = 'Material' -- or whatever this is required to be to only select material costs...
having count(*) > 1

as to calculation, once we have the above sorted out, this is simple.

Author Comment

ID: 10772307
Well, inside the cst_item_cost_details_v, it have same inventory-item_id, due to different item_cost and cost_element~! And now, i just retrieved the item_cost which is based on Material  under the cost_element~! So, now i get the single inventory_item_id and item_cost.

But how can i add the script to calc the quantity x item_cost per monthly basic based on the script u guided me~????

CUSTOMER_ITEM_NAME      ITEM_COST       JANUARY      Total       February     Total
01001931MMTF                    0.236                 2500            590          1458        344.088
010A0016801Q                     0.36                   258             92.88        xxxx          xxxx

for example: 0.236 x 2500 = 590

LVL 25

Expert Comment

ID: 10772356
just multiply them out...either as...(again just a few for an example)...

       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0)) AS Jan_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity * item_cost, 0)) AS Jan_amt,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0)) AS Feb_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity * item_cost, 0)) AS Feb_amt,


       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0)) AS Jan_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '01', a.Quantity, 0))
            * item_cost                                                AS Jan_amt,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0)) AS Feb_qty,
       sum(decode(to_char(a.Receipt_Date, 'MM'), '02', a.Quantity, 0))
            * item_cost                                                AS Feb_amt,

Author Comment

ID: 10773137
Iwadwell, thank for your help~!!!
LVL 25

Expert Comment

ID: 10773183
that's ok...glad to be of help.

Expert Comment

ID: 10853047
Hey, i would like to creata a parameter form and run a query inside it, For example, i have many items -> e.g "MC-2.2MFD-10V-K-X5R-0805-TAP-X", "FAC-100MFD-25V-M-105C-6.3*7-DIP-TAP-X
" inside the item_description and I just want to key-in the first fews char either MC or FAC  and it will only come out those records that it's item name is start from MC or FAC. How to do that?
Well...from designing the report -> Data Model ->Layout Model're all fine, but i just wonder what's wrong in designing the parameter form. I need the correct steps and the appropriate script to make a query inside my parameter form.

The steps what i'd been done were:
1. Data Model add:
   AND cost_element like 'Material'
   GROUP BY a.inventory_item_id, a.customer_item_name, a.item_description, b.item_cost

2. Parameter Model add:
   source = p_item_desc
   datatype = character
   width = 500

   object navigator (report triggers):
   before report = rightclick - PL/SQL Editor add:
   :p_item_desc := 'and a.item_description like ';

I get the error msg : ORA-00933 SQL command not properly ended~!!!


Expert Comment

ID: 10927111
Hi, i would like to do a counting of suppliers, they are AMP, MURATA (US), MURATA(IPC)-USD, TI-VMI. I can get the total of suppliers, but i not know how to get the specific count total of each supplier.

here the script:

select count(pvs.vendor_site_code) as vendors
from mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
mtl_item_categories mic1,
mtl_categories mc1,
mtl_item_categories mic2,
mtl_categories mc2,
mtl_mfg_part_numbers mmpn,
po_asl_attributes paa,
po_approved_supplier_list pasl,
per_people_f ppf,
po_vendors pv,
po_vendor_sites_all pvs,
mrp_sr_source_org msso,
mrp_sourcing_rules msr,
mrp_sr_receipt_org msro,
org_organization_definitions ood
where msi.organization_id = :p_org_id
and msi.organization_id = ood.organization_id
and msi.inventory_item_status_code != 'Inactive'
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.inventory_item_id = mic1.inventory_item_id
and msi.organization_id = mic1.organization_id
and msi.inventory_item_id = mic2.inventory_item_id(+)
and msi.organization_id = mic2.organization_id(+)
and msi.segment1 like '0%'
and mic.category_set_id = 7
and mic1.category_set_id = 6
and mic2.category_set_id(+) = 1100000001
and mic.category_id = mc.category_id
and mic1.category_id = mc1.category_id
and mic2.category_id = mc2.category_id(+)
and mc.segment1 = 'RM'
and msi.inventory_item_id = mmpn.inventory_item_id(+)
and msi.inventory_item_id = paa.item_id
and paa.using_organization_id = -1
and paa.using_organization_id = pasl.using_organization_id
and paa.asl_id = pasl.asl_id
and msi.organization_id = pasl.owning_organization_id
and pasl.vendor_id = paa.vendor_id
and pasl.vendor_site_id = paa.vendor_site_id
and paa.item_id = pasl.item_id
and nvl(pasl.disable_flag,'N') != 'Y'
and msso.vendor_id = pv.vendor_id
and pvs.vendor_id = pv.vendor_id
and msso.vendor_site_id = pvs.vendor_site_id
and msso.sr_receipt_id = msro.sr_receipt_id
and msr.sourcing_rule_id = msro.sourcing_rule_id
and msr.organization_id = msi.organization_id
and msr.sourcing_rule_name = msi.segment1
and msro.disable_date is null
and paa.vendor_id = pv.vendor_id
and pvs.vendor_site_code in ('AMP','MURATA (US)','MURATA(IPC)-USD','TI-VMI')
and msi.buyer_id = ppf.person_id(+)
and ppf.full_name != 'TAH, Ms.'
order by msi.segment1

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now