Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Oracle Group by-Case
Hello, I have the below query:
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
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;
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
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.
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
user_tab_columns also works if it's all in the same schema
you'll have to case the varchar2 column to an nvarchar2
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
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial