Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

Oracle Query

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
Star79
Asked:
Star79
  • 8
  • 5
1 Solution
 
sdstuberCommented:
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
 
Star79Author Commented:
Thanks,
Iam looking to produce something like as in the excel file.
Resultset2.PNG
0
 
sdstuberCommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Star79Author Commented:
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
 
sdstuberCommented:
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
 
Star79Author Commented:
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
 
sdstuberCommented:
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
 
Star79Author Commented:
Thank you still testing it out
0
 
Star79Author Commented:
The above works perfectly well.
I just have extension to the same question above.
0
 
Star79Author Commented:
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
 
Star79Author Commented:
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
 
Star79Author Commented:
Please let me know if I need to open it as new question
0
 
sdstuberCommented:
yes, it should be a new question
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now