December 6, 2018 at 11:50 am
Hi,
I am using below SQL statement to load data into table which I will be using for my report.
SELECT ed_cons, office_names,
to_char(trunc(load_date, 'IW') - 1, 'YYYY/MM/DD') | | ' - ' | | to_char(next_day(trunc(load_date, 'IW'), 'SATURDAY'), 'YYYY/MM/DD') AS week,
CASE WHEN req_type = 1 then 'Search Notification'
WHEN req_type = 2 then ' Packet Notification'
ELSE ' Notification'
END AS alert_type, req_id
FROM ets_email_alert_history
WHERE trunc(load_date) < '03-Jun-2018'
When I click finish, there are some error popped up
" Error in list of function arguments: '|' not recognized.
Error in list of function arguments: ',' not recognized.
Error in list of function arguments: ')' not recognized.
Unable to parse query text"
but I tried to run above query in Toad. There is no problem at all.
What should I do?
thank you
December 6, 2018 at 12:07 pm
Is your backend Oracle? That looks like PL/SQL, not T-SQL.
December 6, 2018 at 12:08 pm
Yes Sir.
The back end is Oracle. Is there anyway i can make below statement to work? Thank you.
to_char(trunc(load_date, 'IW') - 1, 'YYYY/MM/DD') | | ' - ' | | to_char(next_day(trunc(load_date, 'IW'), 'SATURDAY'), 'YYYY/MM/DD') AS week
December 6, 2018 at 12:40 pm
When you set up the connection to your data source, did you specify Oracle as your source? Looks like SSRS is expecting you to be connecting to a different data source, so when the PL/SQL gets passed back, it gets misinterpreted (because maybe your setting is for a SQL Server source and not Oracle)?
I'm kinda guessing, but that's where I would check.
December 6, 2018 at 12:44 pm
kennyhuang0108 - Thursday, December 6, 2018 12:08 PMYes Sir.The back end is Oracle. Is there anyway i can make below statement to work? Thank you.
to_char(trunc(load_date, 'IW') - 1, 'YYYY/MM/DD') | | ' - ' | | to_char(next_day(trunc(load_date, 'IW'), 'SATURDAY'), 'YYYY/MM/DD') AS week
This site is dedicated to SQL Server. You'll have better luck on a site dedicated to Oracle or to a general SQL site. To give you an indication of how different they are, to_char() is not a valid T-SQL function; trunc() is not a valid T-SQL function; next_day() is not a valid T-SQL function.
I did notice that you have a space between '| |'. I would guess that there shouldn't be a space '||'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 6, 2018 at 1:08 pm
Under the same configuration, if I query below I can load data into table without issue.
select * FROM ets_email_alert_history
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply