Solved

adding % symbol to a percentage - oracle query

Posted on 2016-10-04
16
49 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 24

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 24

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
 

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 24

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 24

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

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 24

Expert Comment

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

Accepted Solution

by:
sdstuber earned 500 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 24

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 24

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Updating Table/Fields Documentation 3 68
Best RAID for a BDD Oracle 4 61
Substring() and LEFT() syntax 4 21
Make query more efficient 1 16
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

911 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

24 Experts available now in Live!

Get 1:1 Help Now