Solved

Oracle Group by-Case

Posted on 2016-08-02
9
77 Views
Last Modified: 2016-08-09
Hello, I have the below query:
with shippedqty as (
  select sum(shipped_qty) as shpqty, source_salesitem_id
  from FACT_SHIPMENT_LINE
  where dim_org_id in (select dim_org_id from dim_org WHERE org_name like 'TSI%' AND org_name <> 'TSI FMS')
  group by source_salesitem_id
)
  select product.nsn_code,
  (CASE 
WHEN WHSE_CODE='EXE795' THEN 'TX'
WHEN WHSE_CODE='EXE796' THEN 'KUW'
WHEN WHSE_CODE<>'EXE795' AND WHSE_CODE<>'EXE796' THEN DLSF.whse_name

END) AS GAP,
        ven.vendor_name as Source,
        count(fsi.source_soline_id) as BO_num,
        sum(fsi.quantity - coalesce(sq.shpqty, 0)) as tirews_onBO, --qtyordered - qtydelivered
        min(od.THISDATE) as oldest_backorder
  from fact_salesitem fsi
  left join shippedqty sq on fsi.source_soline_id = sq.source_salesitem_id
  inner join dim_product product on fsi.dim_product_id  = product.dim_product_id
  inner join fact_pa_item pai on fsi.dim_product_id = pai.dim_product_id and pai.pa_isactive_ind = 'Y'
  inner join DIM_VENDOR ven on pai.DIM_VENDOR_ID = ven.DIM_VENDOR_ID
  left JOIN DIM_WHSE_BIN DLSF on FSI.DIM_WHSE_BIN_ID = DLSF.DIM_WHSE_BIN_ID
  INNER JOIN DIM_DATE od on FSI.CREATE_DATE_ID = od.DIM_DATE_ID
  INNER JOIN DIM_NAME_VALUES SOHS on FSI.DIM_NV_HDR_STATUS_ID = SOHS.DIM_NAME_VALUES_ID
  INNER JOIN DIM_NAME_VALUES SOLS on FSI.DIM_NV_LINE_STATUS_ID = SOLS.DIM_NAME_VALUES_ID
  WHERE FSI.DIM_ORG_ID IN (SELECT DIM_ORG_ID FROM DIM_ORG WHERE org_name like 'TSI%' AND org_name <> 'TSI FMS')
  and  SOLS.value_code in ('OH', 'BO') and  SOHS.value_code not in ('SC_AF')
  and product.nsn_code='2610-00-051-1270'
  group by product.nsn_code, DLSF.WHSE_CODE, DLSF.whse_name,ven.vendor_name;

Open in new window

When I run this Iam getting an error
character set mismatch
12704. 00000 -  "character set mismatch"
*Cause:    One of the following
           - The string operands(other than an nlsparams argument) to an
           operator or built-in function do not have the same character
           set.
           - An nlsparams operand is not in the database character set.
           - String data with character set other than the database character
           set is passed to a built-in function not expecting it.
           - The second argument to CHR() or CSCONVERT() is not CHAR_CS or
           NCHAR_CS.
           - A string expression in the VALUES clause of an INSERT statement,
           or the SET clause of an UPDATE statement, does not have the
           same character set as the column into which the value would
           be inserted.
           - A value provided in a DEFAULT clause when creating a table does
           not have the same character set as declared for the column.
           - An argument to a PL/SQL function does not conform to the
           character set requirements of the corresponding parameter.
*Action:
Error at Line: 11 Column: 55
Please guide or is there a better way to include the case statement so that I just have to do grouping on DLSF.WHSE_CODE only
0
Comment
Question by:Star79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41739875
You have to include the CASE logic in GROUP BY as well.
with shippedqty as (
  select sum(shipped_qty) as shpqty, source_salesitem_id
  from FACT_SHIPMENT_LINE
  where dim_org_id in (select dim_org_id from dim_org WHERE org_name like 'TSI%' AND org_name <> 'TSI FMS')
  group by source_salesitem_id
)
  select product.nsn_code,
  (CASE 
WHEN WHSE_CODE='EXE795' THEN 'TX'
WHEN WHSE_CODE='EXE796' THEN 'KUW'
WHEN WHSE_CODE<>'EXE795' AND WHSE_CODE<>'EXE796' THEN DLSF.whse_name

END) AS GAP,
        ven.vendor_name as Source,
        count(fsi.source_soline_id) as BO_num,
        sum(fsi.quantity - coalesce(sq.shpqty, 0)) as tirews_onBO, --qtyordered - qtydelivered
        min(od.THISDATE) as oldest_backorder
  from fact_salesitem fsi
  left join shippedqty sq on fsi.source_soline_id = sq.source_salesitem_id
  inner join dim_product product on fsi.dim_product_id  = product.dim_product_id
  inner join fact_pa_item pai on fsi.dim_product_id = pai.dim_product_id and pai.pa_isactive_ind = 'Y'
  inner join DIM_VENDOR ven on pai.DIM_VENDOR_ID = ven.DIM_VENDOR_ID
  left JOIN DIM_WHSE_BIN DLSF on FSI.DIM_WHSE_BIN_ID = DLSF.DIM_WHSE_BIN_ID
  INNER JOIN DIM_DATE od on FSI.CREATE_DATE_ID = od.DIM_DATE_ID
  INNER JOIN DIM_NAME_VALUES SOHS on FSI.DIM_NV_HDR_STATUS_ID = SOHS.DIM_NAME_VALUES_ID
  INNER JOIN DIM_NAME_VALUES SOLS on FSI.DIM_NV_LINE_STATUS_ID = SOLS.DIM_NAME_VALUES_ID
  WHERE FSI.DIM_ORG_ID IN (SELECT DIM_ORG_ID FROM DIM_ORG WHERE org_name like 'TSI%' AND org_name <> 'TSI FMS')
  and  SOLS.value_code in ('OH', 'BO') and  SOHS.value_code not in ('SC_AF')
  and product.nsn_code='2610-00-051-1270'
  group by product.nsn_code, (CASE 
WHEN WHSE_CODE='EXE795' THEN 'TX'
WHEN WHSE_CODE='EXE796' THEN 'KUW'
WHEN WHSE_CODE<>'EXE795' AND WHSE_CODE<>'EXE796' THEN DLSF.whse_name

END),ven.vendor_name;

Open in new window

1
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41740234
Definitely, there is data type mismatch in your case. Do you know which column is leading to this problem? If not, try to figure it out.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41740447
check the view dba_tab_columns and look for VARCHAR2 and NVARCHAR2 columns which you use in the where
user_tab_columns also works if it's all in the same schema

you'll have to case the varchar2 column to an nvarchar2
0
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41740448
maybe it's column DLSF.whse_name
0
 

Author Comment

by:Star79
ID: 41741265
Tried Sharath code gave me the same error:
haracter set mismatch
12704. 00000 -  "character set mismatch"
*Cause:    One of the following
           - The string operands(other than an nlsparams argument) to an
           operator or built-in function do not have the same character
           set.
           - An nlsparams operand is not in the database character set.
           - String data with character set other than the database character
           set is passed to a built-in function not expecting it.
           - The second argument to CHR() or CSCONVERT() is not CHAR_CS or
           NCHAR_CS.
           - A string expression in the VALUES clause of an INSERT statement,
           or the SET clause of an UPDATE statement, does not have the
           same character set as the column into which the value would
           be inserted.
           - A value provided in a DEFAULT clause when creating a table does
           not have the same character set as declared for the column.
           - An argument to a PL/SQL function does not conform to the
           character set requirements of the corresponding parameter.
*Action:
Error at Line: 34 Column: 55
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41741852
try this in the case:
cast DLSF.whse_name as varchar2(*length* ?)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41741861
Instead of running the whole query, run the CTE first. If no issue with CTE, JOIN one table at a time and try to find out where exactly you are getting the error.
0
 

Accepted Solution

by:
Star79 earned 0 total points
ID: 41742531
The solution is below:
 USE N FUNCTION (the N function converts the data to nvarchar at compilation time)
 (CASE
WHEN WHSE_CODE=('EXE795') THEN N'TX'
WHEN WHSE_CODE=('EXE796') THEN N'KUW'
WHEN WHSE_CODE<>('EXE795') AND WHSE_CODE<>('EXE796') THEN DLSF.whse_name
0
 

Author Closing Comment

by:Star79
ID: 41748475
The solution worked for me.
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

695 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