Using SQL to fill DataTable

  • 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

  • Is your backend Oracle? That looks like PL/SQL, not T-SQL.

  • 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

  • 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.

  • kennyhuang0108 - Thursday, December 6, 2018 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

    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

  • 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