Solved

Oracle Query

Posted on 2016-08-02
13
71 Views
Last Modified: 2016-08-05
Hello All,
I have the below query which produces the resultset
select dv.VENDOR_CODE,dv.VENDOR_NAME,  d.THISDATE, sum(po.QUANTITY) , rfp.VENDOR_SHARE_PERCENT as SPLIT
from fact_po_lines po 
inner join dim_product p on po.dim_product_id = p.dim_product_id and p.product_sku = '02128087'
INNER JOIN DIM_DATE d ON po.ORDERED_DATE_ID = d.DIM_DATE_ID
INNER JOIN FACT_PA_ITEM pai ON po.DIM_PRODUCT_ID  = pai.DIM_PRODUCT_ID and po.DIM_VENDOR_ID = pai.DIM_VENDOR_ID
INNER JOIN DIM_VENDOR dv ON po.DIM_VENDOR_ID = dv.DIM_VENDOR_ID
INNER JOIN DIM_DATE deff ON pai.DIM_DATE_EFFECTIVE_ID = deff.DIM_DATE_ID
INNER JOIN DIM_DATE dexp ON pai.DIM_DATE_EXPIRATION_ID = dexp.DIM_DATE_ID
INNER JOIN DIM_VENDOR dv ON pai.DIM_VENDOR_ID = dv.DIM_VENDOR_ID
INNER JOIN DIM_PRODUCT p ON pai.DIM_PRODUCT_ID = p.DIM_PRODUCT_ID
INNER JOIN FACT_RFP_PRODUCT rfp ON pai.SOURCE_RFP_ID = rfp.SOURCE_RFP_ID
                               and pai.DIM_PRODUCT_ID = rfp.DIM_PRODUCT_ID 
                              and pai.DIM_DATE_EFFECTIVE_ID = rfp.DIM_DATE_CREATE_ID 
where d.THISDATE = '19-DEC-14'-- and d.thisdate < '31-DEC-15'
and d.thisdate  between deff.THISDATE and dexp.THISDATE 
group by dv.vendor_code, dv.vendor_name, d.thisdate, rfp.vendor_share_percent
order by d.thisdate, dv.vendor_code;

Open in new window

How can I modify the above query in such a way that , it checks for split column, if the split is not <> 100 then it separates vendor_code separately under 2 columns vendor1_code and  vendor2_code. If the split = 100 then generates vendor1_code
Resultset.PNG
0
Comment
Question by:Star79
  • 8
  • 5
13 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 41739124
given your current result set output, what do you want the new output to look like?
please post rows and columns, rather than a description.
0
 

Author Comment

by:Star79
ID: 41739145
Thanks,
Iam looking to produce something like as in the excel file.
Resultset2.PNG
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41739190
so, your query will always return exactly 2 rows?
if not, what would a 3 row result look like?

please post text, not pictures. makes it much easier to build test cases
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Star79
ID: 41739208
The split is always 2 or 1 only , which means either it has 100 or it can be split into 2 vendorsm of 30/70 or 40/60 or 50/50 etc.....,
sorry about the pictures
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41739332
try this.  If there is a split, all columns will be populated, if not, then the "2" columns will be null


SELECT vendor1_code,
       vendor1_name,
       thisdate,
       qtysum_1,
       split_1,
       vendor2_code,
       vendor2_name,
       qtysum_2,
       split_2
  FROM (SELECT vendor_code                             vendor1_code,
               vendor_name                             vendor1_name,
               thisdate,
               qtysum                                  qtysum_1,
               split                                   split_1,
               LEAD(vendor_code) OVER (ORDER BY split) vendor2_code,
               LEAD(vendor_name) OVER (ORDER BY split) vendor2_name,
               LEAD(qtysum) OVER (ORDER BY split)      qtysum_2,
               LEAD(split) OVER (ORDER BY split)       split_2,
               ROW_NUMBER() OVER (ORDER BY split)      rn
          FROM (  SELECT dv.vendor_code,
                         dv.vendor_name,
                         d.thisdate,
                         SUM(po.quantity),
                         rfp.vendor_share_percent AS split
                    FROM fact_po_lines po
                         INNER JOIN dim_product p
                             ON po.dim_product_id = p.dim_product_id AND p.product_sku = '02128087'
                         INNER JOIN dim_date d ON po.ordered_date_id = d.dim_date_id
                         INNER JOIN fact_pa_item pai
                             ON     po.dim_product_id = pai.dim_product_id
                                AND po.dim_vendor_id = pai.dim_vendor_id
                         INNER JOIN dim_vendor dv ON po.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_date deff ON pai.dim_date_effective_id = deff.dim_date_id
                         INNER JOIN dim_date dexp ON pai.dim_date_expiration_id = dexp.dim_date_id
                         INNER JOIN dim_vendor dv ON pai.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_product p ON pai.dim_product_id = p.dim_product_id
                         INNER JOIN fact_rfp_product rfp
                             ON     pai.source_rfp_id = rfp.source_rfp_id
                                AND pai.dim_product_id = rfp.dim_product_id
                                AND pai.dim_date_effective_id = rfp.dim_date_create_id
                   WHERE     d.thisdate = '19-DEC-14'                -- and d.thisdate < '31-DEC-15'
                         AND d.thisdate BETWEEN deff.thisdate AND dexp.thisdate
                GROUP BY dv.vendor_code,
                         dv.vendor_name,
                         d.thisdate,
                         rfp.vendor_share_percent) t)
 WHERE rn = 1

Open in new window

0
 

Author Comment

by:Star79
ID: 41739363
Hello I tried the above ,
sorry very new to LEAD function.It gave me an error message on bolded line as

ORA-00904: "QTYSUM": invalid identifier
00904. 00000 -  "%s: invalid identifier"

Please help.

 
SELECT vendor1_code,
       vendor1_name,
       thisdate,
       qtysum_1,
       split_1,
       vendor2_code,
       vendor2_name,
       qtysum_2,
       split_2
  FROM (SELECT vendor_code                             vendor1_code,
               vendor_name                             vendor1_name,
               thisdate,
               qtysum                                  qtysum_1,
               split                                   split_1,
               LEAD(vendor_code) OVER (ORDER BY split) vendor2_code,
               LEAD(vendor_name) OVER (ORDER BY split) vendor2_name,
          [b][u]     LEAD(qtysum) OVER (ORDER BY split)      qtysum_2,[/u][/b]
               LEAD(split) OVER (ORDER BY split)       split_2,
               ROW_NUMBER() OVER (ORDER BY split)      rn
          FROM (  SELECT dv.vendor_code,
                         dv.vendor_name,
                         d.thisdate,
                         SUM(po.quantity),
                         rfp.vendor_share_percent AS split
                    FROM fact_po_lines po
                         INNER JOIN dim_product p
                             ON po.dim_product_id = p.dim_product_id AND p.product_sku = '02128087'
                         INNER JOIN dim_date d ON po.ordered_date_id = d.dim_date_id
                         INNER JOIN fact_pa_item pai
                             ON     po.dim_product_id = pai.dim_product_id
                                AND po.dim_vendor_id = pai.dim_vendor_id
                         INNER JOIN dim_vendor dv ON po.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_date deff ON pai.dim_date_effective_id = deff.dim_date_id
                         INNER JOIN dim_date dexp ON pai.dim_date_expiration_id = dexp.dim_date_id
                         INNER JOIN dim_vendor dv ON pai.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_product p ON pai.dim_product_id = p.dim_product_id
                         INNER JOIN fact_rfp_product rfp
                             ON     pai.source_rfp_id = rfp.source_rfp_id
                                AND pai.dim_product_id = rfp.dim_product_id
                                AND pai.dim_date_effective_id = rfp.dim_date_create_id
                   WHERE     d.thisdate = '19-DEC-14'                -- and d.thisdate < '31-DEC-15'
                         AND d.thisdate BETWEEN deff.thisdate AND dexp.thisdate
                GROUP BY dv.vendor_code,
                         dv.vendor_name,
                         d.thisdate,
                         rfp.vendor_share_percent) t)
 WHERE rn = 1

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41739381
sorry, I forgot to include the qtysum column alias on the inner query

SELECT vendor1_code,
       vendor1_name,
       thisdate,
       qtysum_1,
       split_1,
       vendor2_code,
       vendor2_name,
       qtysum_2,
       split_2
  FROM (SELECT vendor_code                             vendor1_code,
               vendor_name                             vendor1_name,
               thisdate,
               qtysum                                  qtysum_1,
               split                                   split_1,
               LEAD(vendor_code) OVER (ORDER BY split) vendor2_code,
               LEAD(vendor_name) OVER (ORDER BY split) vendor2_name,
               LEAD(qtysum) OVER (ORDER BY split)      qtysum_2,
               LEAD(split) OVER (ORDER BY split)       split_2,
               ROW_NUMBER() OVER (ORDER BY split)      rn
          FROM (  SELECT dv.vendor_code,
                         dv.vendor_name,
                         d.thisdate,
                         SUM(po.quantity) qtysum,
                         rfp.vendor_share_percent AS split
                    FROM fact_po_lines po
                         INNER JOIN dim_product p
                             ON po.dim_product_id = p.dim_product_id AND p.product_sku = '02128087'
                         INNER JOIN dim_date d ON po.ordered_date_id = d.dim_date_id
                         INNER JOIN fact_pa_item pai
                             ON     po.dim_product_id = pai.dim_product_id
                                AND po.dim_vendor_id = pai.dim_vendor_id
                         INNER JOIN dim_vendor dv ON po.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_date deff ON pai.dim_date_effective_id = deff.dim_date_id
                         INNER JOIN dim_date dexp ON pai.dim_date_expiration_id = dexp.dim_date_id
                         INNER JOIN dim_vendor dv ON pai.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_product p ON pai.dim_product_id = p.dim_product_id
                         INNER JOIN fact_rfp_product rfp
                             ON     pai.source_rfp_id = rfp.source_rfp_id
                                AND pai.dim_product_id = rfp.dim_product_id
                                AND pai.dim_date_effective_id = rfp.dim_date_create_id
                   WHERE     d.thisdate = '19-DEC-14'                -- and d.thisdate < '31-DEC-15'
                         AND d.thisdate BETWEEN deff.thisdate AND dexp.thisdate
                GROUP BY dv.vendor_code,
                         dv.vendor_name,
                         d.thisdate,
                         rfp.vendor_share_percent) t)
 WHERE rn = 1

Open in new window

0
 

Author Comment

by:Star79
ID: 41739766
Thank you still testing it out
0
 

Author Comment

by:Star79
ID: 41744473
The above works perfectly well.
I just have extension to the same question above.
0
 

Author Comment

by:Star79
ID: 41744482
If I have to run the same above query for a date range instead of a single date I modify the where condition to say:
SELECT nsn,thisdate,  
vendor1_code, vendor1_name, qtysum_1, DELIVERED_QTY_1, split_1,
vendor2_code, vendor2_name, qtysum_2, DELIVERED_QTY_2, split_2
FROM 
(SELECT nsn_code AS nsn, vendor_code AS vendor1_code, vendor_name AS vendor1_name, thisdate,
qtysum AS qtysum_1, DELIVERED_QTY AS DELIVERED_QTY_1,
split AS split_1, LEAD(vendor_code) OVER (ORDER BY split) AS vendor2_code,
               LEAD(vendor_name) OVER (ORDER BY split) AS vendor2_name,
               LEAD(qtysum) OVER (ORDER BY split)      AS qtysum_2,
              LEAD(DELIVERED_QTY)OVER (ORDER BY split) AS DELIVERED_QTY_2,
               LEAD(split) OVER (ORDER BY split)       AS split_2
               ,ROW_NUMBER() OVER (ORDER BY split)      rn
          FROM (  SELECT p.NSN_CODE,
                        dv.vendor_code,
                         dv.vendor_name,d.thisdate,
                         SUM(po.quantity) qtysum,
                         sum(po.QTYDELIVERED) as DELIVERED_QTY,
                         rfp.vendor_share_percent AS split
                    FROM fact_po_lines po
                         INNER JOIN dim_product p
                             ON po.dim_product_id = p.dim_product_id 
                         INNER JOIN dim_date d ON po.ORDERED_DATE_ID = d.dim_date_id
                         INNER JOIN fact_pa_item pai
                             ON     po.dim_product_id = pai.dim_product_id
                                AND po.dim_vendor_id = pai.dim_vendor_id
                         INNER JOIN dim_vendor dv ON po.dim_vendor_id = dv.dim_vendor_id
                         INNER JOIN dim_date deff ON pai.dim_date_effective_id = deff.dim_date_id
                         INNER JOIN dim_date dexp ON pai.dim_date_expiration_id = dexp.dim_date_id
                         INNER JOIN dim_product p ON pai.dim_product_id = p.dim_product_id
                         INNER JOIN fact_rfp_product rfp
                             ON     pai.source_rfp_id = rfp.source_rfp_id
                                AND pai.dim_product_id = rfp.dim_product_id
                                AND pai.dim_date_effective_id = rfp.dim_date_create_id
                   WHERE    d.THISDATE BETWEEN '01-AUG-14' AND '15-AUG-14'  
                   AND d.thisdate BETWEEN deff.thisdate AND dexp.thisdate
                         and p.NSN_CODE = '2610-01-214-1344'
                GROUP BY p.nsn_code,
                         dv.vendor_code,
                         dv.vendor_name, 
                         d.thisdate,
                         rfp.vendor_share_percent) t)
                        
 WHERE rn = 1
 order by thisdate;

Open in new window

But in this case since its a range of values do I have to comment rn=1 as the purpose is to get the data in the same way but we will have multiple rows
Please let me know.
0
 

Author Comment

by:Star79
ID: 41744489
So basically Iam trying to get the bottom part of the resultset picture.The grand total for each vendor , the total order quantity and total delivered qty.
Please help.
Resultset.PNG
0
 

Author Comment

by:Star79
ID: 41744491
Please let me know if I need to open it as new question
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41744502
yes, it should be a new question
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

776 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