Enter Substitution Variable

Question

I have an sql statement from a colleague overseas, but I get a box that pops up when I try to run it that says "Enter Substitution Variable". What do I put in it? My colleague says that they do not get the same box. What causes this message? I am providing the statement below. Any help is greatly appreciated.

Also, line three gives a message saying missing right parenthesis.

select SUBSTR(created_ts, 1, 7) as created_month, 
count(t1.id) as mops_created,
count(completed_ts <> '') as num_complete,
round(AVG(DATEDIFF(planning_complete_ts, created_ts)), 1) as average_created_to_planning_complete,
round(AVG(DATEDIFF(review_complete_ts, planning_complete_ts)), 1) as average_planning_complete_to_review_complete,
round(AVG(DATEDIFF(scheduling_complete_ts, planning_complete_ts)), 1) as average_planning_complete_to_scheduling_complete,
round(AVG(DATEDIFF(scheduled_ts, scheduling_complete_ts)), 1) as average_scheduled_for_x_days_out,
round(AVG(DATEDIFF(completed_ts, planning_complete_ts)), 1) as average_planning_complete_to_mop_complete,
round(AVG(planning_complete_num), 1) as average_num_times_planning_complete,
max(planning_complete_num) as max_planning_complete,
round(AVG(scheduling_complete_num), 1) as average_num_time_scheduled,
max(scheduling_complete_num) as max_scheduled_num
FROM
(select a.id, a.created_ts, a.scheduled_ts, work_start_ts, completed_ts
from TRAFFIC_ENG.DSIS_CC_MASTER as a 
WHERE 
                a.created_ts >= '2013-01-01 00:00:00'
AND 
                a.status_cc_options_id NOT IN (810, 820)) as t1
LEFT JOIN
(select a.id, max(c.orig_date) as review_complete_ts 
from 
                TRAFFIC_ENG.DSIS_CC_MASTER as a
INNER JOIN
                TRAFFIC_ENG.DSIS_CC_LOG_NOTE as c
                ON c.cc_master_id = a.id
                AND (c.cc_log_note_type_id = 8 OR c.note = 'Passed Review & submitted to Scheduling')
WHERE 
                a.created_ts >= '2013-01-01 00:00:00'
AND 
                a.status_cc_options_id NOT IN (810, 820)
GROUP BY a.id) as review_complete
ON review_complete.id = t1.id
LEFT JOIN
(
select a.id, min(c.orig_date) as planning_complete_ts 
from 
                TRAFFIC_ENG.DSIS_CC_MASTER as a
INNER JOIN
                TRAFFIC_ENG.DSIS_CC_LOG_NOTE as c
                ON c.cc_master_id = a.id
                AND (c.cc_log_note_type_id = 5 OR c.note = 'Submitted for Review')
WHERE 
                a.created_ts >= '2013-01-01 00:00:00'
AND 
                a.status_cc_options_id NOT IN (810, 820)
GROUP BY a.id

) as planning_complete
ON planning_complete.id = t1.id
LEFT JOIN
(
select a.id, (c.orig_date) as scheduling_complete_ts 
from 
                TRAFFIC_ENG.DSIS_CC_MASTER as a
INNER JOIN
                TRAFFIC_ENG.DSIS_CC_LOG_NOTE as c
                ON c.cc_master_id = a.id
                AND (c.cc_log_note_type_id = 7 OR c.note='Scheduled')
WHERE 
                a.created_ts >= '2013-01-01 00:00:00'
AND 
                a.status_cc_options_id NOT IN (810, 820)
GROUP BY a.id
) as scheduling_complete
ON scheduling_complete.id = t1.id
LEFT JOIN
(
select a.id, count(c.orig_date) as planning_complete_num 
from 
                TRAFFIC_ENG.DSIS_CC_MASTER as a
INNER JOIN
                TRAFFIC_ENG.DSIS_CC_LOG_NOTE as c
                ON c.cc_master_id = a.id
                AND (c.cc_log_note_type_id = 5 OR c.note = 'Submitted for Review')
WHERE 
                a.created_ts >= '2013-01-01 00:00:00'
AND 
                a.status_cc_options_id NOT IN (810, 820)
GROUP BY a.id

) as planning_complete_num
ON planning_complete_num.id = t1.id
LEFT JOIN
(
select a.id, count(c.orig_date) as scheduling_complete_num
from 
                TRAFFIC_ENG.DSIS_CC_MASTER as a
INNER JOIN
                TRAFFIC_ENG.DSIS_CC_LOG_NOTE as c
                ON c.cc_master_id = a.id
                AND (c.cc_log_note_type_id = 7 OR c.note='Scheduled')
WHERE 
                a.created_ts >= '2013-01-01 00:00:00'
AND 
                a.status_cc_options_id NOT IN (810, 820)
GROUP BY a.id
) as scheduling_complete_num
ON scheduling_complete_num.id = t1.id

GROUP BY created_month

Alex Poole Alex Poole · Accepted Answer · 2013-11-08T15:55:33

As @the_slk said, you can turn off substitution variables with set define off. That will stop the client interpreting the & in the string 'Passed Review & submitted to Scheduling' as user input. It sounds like you might be using SQL Developer, but much of the SQL*Plus documentation is still relevant. If you're using a different client that it might have a preference to turn this off instead.

The second part of the question, about 'missing right parenthesis', looks like it might be because you have blank lines in your code. I think your client is treating each section of the code, separated by blank lines, as separate statements - I make that four the way you have it formatted, but I'm not sure that quite aligns with the error. SQL*Plus does that by default. Anyway, only the final block is actually being executed, but that if it was really just running 'GROUP BY created_month' you'd get a different error, so whatever your client is, it seems to behaving a bit differently, or you have more code after this that you haven't shown.

To make SQL*Plus ignore blank lines, and treat code separated by blanks lines as one block, set sqlblanklines on. SQL Developer doesn't use that setting though and lets you use blank lines anyway, so maybe your client is something else, and will either use the same command or have its own preference. If you are using the same client as your colleagues, ask them how they have their preferences set. If your client doesn't have an obvious way to change the behaviour then removing the blank lines from the script might solve this.

Enter Substitution Variable

3 Answers