CODEWITHSUNDEEP
sqloracle-database
Vikas
Vikas

Reputation: 199

Expression must have same datatype error while executing a query

Need your help in resolving the error which I'm getting. I'm using a union to join two queries and is giving me an error. Below is the query along with the error message.

Query:

SELECT 'Firewall Rules' as Platform
       , 'TestCount' TestCount
       , 'TestPassCount' TestPassCount
       , percentage as percentage
       , firewall_name as Server 
from(
    SELECT round(avg(replace(PER_OF_VIOLATING_RULES, '%', ''))* 1 , 2)||'%'  as percentage
           , firewall_name
    from table1 
    group by firewall_name
)
union
select 'Database' as Platform
       , count( *) TestCount
       , count(case when result_status = 'Not A Finding' then 1 end) TestPassCount
       , Round(  count(case when result_status = 'Not A Finding' then 1 end) / count(*) * 100 , 2) percentage
       , asset as Server
FROM table2 
group by asset

Error message:

ORA-01790: expression must have the same datatype as corresponding expression
01790. 00000 -  "expression must have the same datatype as the corresponding expression"
*Cause:    
*Action:
Error at Line: 1 Column: 37

Can someone help in modifying the query?

Upvotes: 1

Views: 239

Answers (2)

APC
APC

Reputation: 146229

With set operations the projection of all the subqueries must have the same signature. So the datatypes of the columns must match.

With your top subquery you are returning string literals for TestCount and TestPassCount but numbers for the bottom subquery. So you need to change one of those projections. You could use to_char() to cast the numerics to strings but it would be better to remove the string literals in the firewall subquery:

SELECT 'Firewall Rules' as Platform
       , to_number(null) TestCount
       , to_number(null) TestPassCount
       , percentage as percentage
....

Alternatively you could use literal zeroes, it depends on your needs.

By the way: you can use UNION ALL rather than UNION, because the result set of both subqueries is guaranteed to be a set due to the platform literal. UNION does a sort to ensure uniqueness, so for a big query UNION ALL is more efficient.

Upvotes: 2

Bikash Ranjan Bhoi
Bikash Ranjan Bhoi

Reputation: 516

'TestCount' TestCount and TestPassCount' TestPassCount are string in first query and you are fetching numeric values in second query for the respective fields.

You can typecast using TO_CHAR Function in the second query to overcome this.

Upvotes: 0

Related Questions