CODEWITHSUNDEEP
sqljdbc
collin7681
collin7681

Reputation: 19

Missing character ORA-00911 in SQL Statement

I am trying to write a report as a table on JDBC. The report will accept, but when I try to run it, I keep receiving the oracle-00911 error stating there is a missing character. I was assuming I missed a quote somewhere, but I cannot find the error anywhere. Does anyone see a missing character anywhere in this code?

Thanks for the help!

        (SELECT
            '<b>Trouble Events</b>',
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.electric_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI') AND Completed = 'N' 
                AND FirstAssignedDateTime IS NULL AND FirstArrivedDateTime IS NULL
                AND Analysis = 'Non-Outage'), 'fm9,999,990'), '0'), ' ') Pending,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.electric_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI') AND Completed = 'N' 
                AND FirstAssignedDateTime IS NOT NULL
        AND Status not in ('Crew Enroute', 'Crew Onsite')
                AND Analysis = 'Non-Outage'), 'fm9,999,990'), '0'), ' ') Assigned,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.electric_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI') AND Completed = 'N' 
                AND (FirstAssignedDateTime IS NOT NULL OR FirstArrivedDateTime IS NOT NULL)
        AND Status in ('Crew Enroute', 'Crew Onsite')
                AND Analysis = 'Non-Outage'), 'fm9,999,990'), '0'), ' ') Working,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.electric_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Completed = 'Y' AND Active = 'N'
        AND Status <> 'Cancelled'
        AND Completioncode like '%COMP%'
                AND Analysis = 'Non-Outage), 'fm9,999,990'), '0'), ' ') Closed,
            '<b>' || NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.electric_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
        AND Status <> 'Cancelled'
                AND Analysis = 'Non-Outage'), 'fm9,999,990'), '0'), ' ') || '</b>' Total
        FROM
            dual)
            
        UNION ALL
        
        (SELECT
            '<b>Outage Events (< 50 Customers)</b>',
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Completed = 'N' AND CustomersOut < 50 
                AND FirstAssignedDateTime IS NULL AND FirstArrivedDateTime IS NULL), 'fm9,999,990'), '0'), ' ') Pending,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Completed = 'N' AND CustomersOut < 50 
                AND FirstAssignedDateTime IS NOT NULL
        AND Status not in ('Crew Enroute', 'Crew Onsite')), 'fm9,999,990'), '0'), ' ') Assigned,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Completed = 'N' AND CustomersOut < 50 
                AND (FirstAssignedDateTime IS NOT NULL OR FirstArrivedDateTime IS NOT NULL)
        AND Status in ('Crew Enroute', 'Crew Onsite')), 'fm9,999,990'), '0'), ' ') Working,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Active = 'N' AND Completed = 'Y' AND CustomersOut < 50 
                AND Status <> 'Cancelled' 
        AND Completioncode like '%COMP%'), 'fm9,999,990'), '0'), ' ') Closed,
            '<b>' || NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Status <> 'Cancelled'), 'fm9,999,990'), '0'), ' ') || '</b>' Total
        FROM
            dual)
            
        UNION ALL
        
        (SELECT
            '<b>Outage Events (50+ Customers)</b>',
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Completed = 'N' AND CustomersOut >= 50 
                AND FirstAssignedDateTime IS NULL AND FirstArrivedDateTime IS NULL), 'fm9,999,990'), '0'), ' ') Pending,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Completed = 'N' AND CustomersOut >= 50 
                AND FirstAssignedDateTime IS NOT NULL
        AND Status not in ('Crew Enroute', 'Crew Onsite')), 'fm9,999,990'), '0'), ' ') Assigned,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Completed = 'N' AND CustomersOut >= 50 
                AND (FirstAssignedDateTime IS NOT NULL OR FirstArrivedDateTime IS NOT NULL)
        AND Status in ('Crew Enroute', 'Crew Onsite')), 'fm9,999,990'), '0'), ' ') Working,
            NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND Active = 'N' AND Completed = 'Y' AND CustomersOut >= 50 
        AND Status <> 'Cancelled' AND Completioncode like '%COMP%'), 'fm9,999,990'), '0'), ' ') Closed,
            '<b>' || NVL(NULLIF(TO_CHAR((SELECT COUNT(EventID) FROM kub_nms.ieee_jobs WHERE
                BeginDateTime BETWEEN TO_DATE('<{[begin_date]}> <{[begin_time]}>', 'MM/DD/YYYY HH24:MI') 
                AND TO_DATE('<{[end_date]}> <{[end_time]}>', 'MM/DD/YYYY HH24:MI')
                AND CustomersOut >= 50 
                AND Status <> 'Cancelled'), 'fm9,999,990'), '0'), ' ') || '</b>' Total
        FROM
            dual)

Upvotes: 0

Views: 58

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21043

Best practice - copy you query in SQL Developer (that has a syntax highlighting) and observe.

You'll see immedietely a problem on line 26 column 46

            AND Analysis = ' non - outage),

should be

            AND Analysis = ' non - outage'),

Upvotes: 1

Related Questions