Solved

Oracle Query

Posted on 2016-08-02
13
69 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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