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

x
?
Solved

adding % symbol to a percentage - oracle query

Posted on 2016-10-04
16
Medium Priority
?
211 Views
Last Modified: 2016-10-04
When attempting to use the following statement I get ORA-01790: expression must have same datatype as corresponding expression.  Can someone tell me how I can calculate this percentage and add the % symbol at the end?  Thanks!!

cast(round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) as varchar(10)) || '%' as percentage

Open in new window

0
Comment
Question by:Basssque
  • 8
  • 7
16 Comments
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828210
Try this ..

CONCAT ( round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) ,'%' ) percentage
0
 

Author Comment

by:Basssque
ID: 41828212
throws the same error
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828221
or may be below...

SELECT ( CAST( round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) / count(*) * 100, 2) AS VARCHAR(100)) || '%' ) as [Percentage]
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:Basssque
ID: 41828227
Still same error
so far it only works without trying to add the percent sign
round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) percentage
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828232
Try..Which Oracle version are you using..

SELECT CAST (

ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 )

, AS  VARCHAR(100)) || CAST ( '%'  AS VARCHAR(2)) as [Percentage]
0
 

Author Comment

by:Basssque
ID: 41828237
oracle 12c
that statement says missing expression
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828242
SELECT CAST (

ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 )

, AS  VARCHAR(100)) || TO_CHAR ( '%' (2)) as Percentage
0
 

Author Comment

by:Basssque
ID: 41828245
That one says missing keyword
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828251
I think that is dual..

SELECT CAST (

ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION FROM dual = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 )

, AS  VARCHAR(100)) || TO_CHAR ( '%' (2)) as Percentage
0
 

Author Comment

by:Basssque
ID: 41828257
invalid relational operator
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828259
Sir can you post your entire query..
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 41828308
based on your previous question I think you're trying to do this

SELECT
SCHOOLS.NAME as "School",
COUNT(CASE WHEN grade_level = '9' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "9",
COUNT(CASE WHEN grade_level = '10' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "10",
COUNT(CASE WHEN grade_level = '11' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "11",
COUNT(CASE WHEN grade_level = '12' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "12",
to_char(round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2)) || '%' percentage
from PS.STUDENTS STUDENTS
LEFT JOIN PS.SCHOOLS SCHOOLS on STUDENTS.SCHOOLID = SCHOOLS.SCHOOL_NUMBER
LEFT JOIN PS.S_CT_STU_SPED_X S_CT_STU_SPED_X ON STUDENTS.DCID = S_CT_STU_SPED_X.STUDENTSDCID
where enroll_status=0 
AND SCHOOLID IN (3, 4, 7, 8, 9)
group by SCHOOLS.NAME

Open in new window

0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828311
Thanks sdstuber... Didnt thought that way.. I thought issue was at %..


SELECT
 TO_CHAR(
ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION FROM dual = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 ))
 ||  '%'  as Percentage
0
 

Author Comment

by:Basssque
ID: 41828341
sdstuber
I still get the following error with your example
ORA-01790: expression must have same datatype as corresponding expression
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41828343
Basssque - can you try mine..? Although it is on similar lines.

I checked that it is not giving any error on live SQL..
0
 

Author Comment

by:Basssque
ID: 41828346
nevermind, sdstubers example does work after all!  Thanks!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
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 shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

876 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