Evan Fjellin
asked on
Invalid number of arguments
Hello,
In my following code, I am getting the error 'Invalid number of arguments':
with Combined as(
Select staff_name Staff, Status
from rpt_scheduled_activities
where trunc(service_date) between '01 aug 2017' and '31 aug 2017'
and staff_id in (39882, 41116, 45723, 19395, 19365, 63898, 48071,
55352, 36065, 60240, 62247, 33570, 60693, 61025)
order by 1)
select
Staff,
"'Kept'" as NumKept,
"'CBT'" as NumCBT,
"'CBC'" as NumCBC,
"'DNS'" as NumDNS,
cast(round((("'Kept'" / NULLIF( ("'Kept'" + "'CBT'" + "'CBC'" + "'DNS'"))*100),2), 0) as varchar(10)) || ' %' as PercentKept
from
(Select * from Combined
PIVOT(count(*) for (Status)
IN ('Kept', 'CBT', 'CBC', 'DNS')
))
I figure the error is somewhere in the 'cast(round...' statement.
In my following code, I am getting the error 'Invalid number of arguments':
with Combined as(
Select staff_name Staff, Status
from rpt_scheduled_activities
where trunc(service_date) between '01 aug 2017' and '31 aug 2017'
and staff_id in (39882, 41116, 45723, 19395, 19365, 63898, 48071,
55352, 36065, 60240, 62247, 33570, 60693, 61025)
order by 1)
select
Staff,
"'Kept'" as NumKept,
"'CBT'" as NumCBT,
"'CBC'" as NumCBC,
"'DNS'" as NumDNS,
cast(round((("'Kept'" / NULLIF( ("'Kept'" + "'CBT'" + "'CBC'" + "'DNS'"))*100),2), 0) as varchar(10)) || ' %' as PercentKept
from
(Select * from Combined
PIVOT(count(*) for (Status)
IN ('Kept', 'CBT', 'CBC', 'DNS')
))
I figure the error is somewhere in the 'cast(round...' statement.
Is this for SQL Server or oracle database?
Your NULL if..was incorrect try like..You can change the expression...
with Combined as
(
Select staff_name Staff, [Status]
from rpt_scheduled_activities
where
trunc(service_date) between '01 aug 2017' and '31 aug 2017'
and
staff_id in (39882, 41116, 45723, 19395, 19365, 63898, 48071,55352, 36065, 60240, 62247, 33570, 60693, 61025)
)
select
Staff,
"'Kept'" as NumKept,
"'CBT'" as NumCBT,
"'CBC'" as NumCBC,
"'DNS'" as NumDNS,
CAST( "'Kept'" * 1.0 / NULLIF( ("'Kept'" + "'CBT'" + "'CBC'" + "'DNS'"), 1 ) as varchar(10) ) || ' %' as PercentKept
from
(
Select * from Combined
PIVOT(count(*) for (Status)
IN ('Kept', 'CBT', 'CBC', 'DNS')
)
)
ASKER
Hello Pawan, thank you for the reply!
Using SQL Server, to answer your first question. For your second response, I am now getting a 'missing expression' error. Tried adding another set of parentheses and a 'Round' function but to no avail.
Using SQL Server, to answer your first question. For your second response, I am now getting a 'missing expression' error. Tried adding another set of parentheses and a 'Round' function but to no avail.
What you want to do if you get NULL with "'Kept'" + "'CBT'" + "'CBC'" + "'DNS'" ?
Also why you are using double quotes... around columns names..if possible can you send me few rows from rpt_scheduled_activities and create table script ..will work out this for you.
Also why you are using double quotes... around columns names..if possible can you send me few rows from rpt_scheduled_activities and create table script ..will work out this for you.
ASKER
I am trying to show a '0' if there is no data entered or there is a '0' for the day.
I went in and added this:
cast(round((("'Kept'" / NULLIF(("'Kept'" + "'CBT'" + "'CBC'" + "'DNS'"),0))*100),2) as varchar(10)) || ' %' as PercentKept
and this worked. I needed to add in the ',0' and was omitting that, hence the errors.
Thank you for your help and thanks for the timely responses!
I went in and added this:
cast(round((("'Kept'" / NULLIF(("'Kept'" + "'CBT'" + "'CBC'" + "'DNS'"),0))*100),2) as varchar(10)) || ' %' as PercentKept
and this worked. I needed to add in the ',0' and was omitting that, hence the errors.
Thank you for your help and thanks for the timely responses!
Great !, Cheers
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.