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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy