CODEWITHSUNDEEP
sqloracle-databaseswitch-statementcase
nimrod
nimrod

Reputation: 5732

Strange behaviour of a sql statement with 'case'

I am trying to create an SQL report. This is the select statement:

  select
  -- ticket id
  '<tr align=left valign=top><td width=95>'||maspid||'</td>'||

  -- priority
  case when instr(masfld017, '<|lang' ) = 0 then
        '<td width=90>'||masfld017
  else
        '<td width=90>'||substr( masfld017, 1 , instrb(masfld017, '<|lang') - 1)
  end||'</td>'||

  -- customer
  '<td width=150>'||masfld007||'</td>'||

  -- status
    '<td width=100>'||decode(masfld104, '0', 'Collected',
          '1', 'Postponed',
          '2', 'Accepted',
          '3', 'in Progress',
          '5', 'Work Around',
          '7', 'Solved',
          '8', 'Closed')||'</td>'||

  -- subject
  '<td width=400>'||masfld001||'</td>'||

  -- full name
  '<td width=100>' || replace(replace(masfld037, '<||>'), '</||>')||'</td>'||

  -- creation date
  '<td width=150>'||to_char(to_date(masfld022, 'YYYYMMDDHH24MI'), 'DD.MM.YYYY HH24:MM')||'</td>'||

  -- target date
  '<td width=150>'||to_char(to_date(masfld023, 'YYYYMMDDHH24MI'), 'DD.MM.YYYY HH24:MM')||'</td>'||

  -- ticket type
  '<td width=180>'||decode(masfld040, '9', 'HUS',
        '8', 'Project',
        '7', 'Enhancement',
        '6', 'Complaint',
        '5', 'Change Request',
        '4', 'Bug (Do not use)',
        '3', 'Support',
        '2', 'Service Request',
        '1', 'Incident (Problem)',
        null, 'N/A')||'</td></tr>'
from
        k2h.tmaster01 tm inner join k2h.tprofile tp on replace(replace(tm.masfld035, '<||>'), '</||>') = tp.propid
where
        masfld024 is null and
        masfld082 = '&REGION' 

order by case masfld017
        when 'Emergency'         then 1 
        when 'Critical'          then 2
        when 'High'              then 3
        when 'Major Problem'     then 4
        when 'Major defect'      then 5
        when 'Semi-major defect' then 6
        when 'Medium'            then 7
        when 'Minor Problem'     then 8
        when 'Low'               then 9
        end;

If I run it directly with sqldeveloper, I get a result list. If the report is started by crontab, it fails with this error message:

SP2-0734: unknown command beginning "case when ..." - rest of line ignored. SP2-0734: unknown command beginning "'

Seems like the case command is not recognized, but why?

Upvotes: 1

Views: 1582

Answers (3)

DCookie
DCookie

Reputation: 43533

I suggest removing the spaces and possibly even the comments from your SQL command and try that. SQL*Plus is picky about spaces embedded in commands. I've even had problems with double-dash comments and SQL*Plus scripts. I think it's the spaces, but if it's the comments, use the /* ... */ style of comment.

Upvotes: 3

DazzaL
DazzaL

Reputation: 21993

is it possible , in crontab, that you're running the SQL direct in the shell eg

sqlplus...<<ESQL
select..
...
order by case end;

? if so please remove the BLANK lines.

otherwise you will get something like:

SQL> select
  2    -- ticket id
  3    '<tr align=left valign=top><td width=95>'||maspid||'</td>'||
  4
SQL>   -- priority
SQL>   case when instr(masfld017, '<|lang' ) = 0 then
SP2-0734: unknown command beginning "case when ..." - rest of line ignored.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270713

For some reason, the parser seems to be interpreting the case statement as a PLSQL case statement, rather than a SELECT case statement. That is, it isn't seeing the expression as part of a column but as part of a PLSQL statement. The "SP" error messages refer to SQL-Plus rather than to the language itself.

My guess is that something above the select statement is confusing the script.

Upvotes: 0

Related Questions