Link to home
Start Free TrialLog in
Avatar of Evan Fjellin
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.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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')
	)
)

Open in new window

Avatar of Evan Fjellin
Evan Fjellin

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.
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.
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!
Great !, Cheers
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members 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.