The basic format is:
select tableColumn
from dbName
where somethingIsTrue
Code: Select all
select id, name, casting_time, ctUnit, '' ctDuration, range,
case when formula = '' and personal = 0 and touch = 0 then 'textRange' else formula end as rangeFormula,
personal, touch, rangeTouch, '' rDistance, target, tCreature,
case when (tCreature = 0
or target like '%object%'
or target like '%undead%'
or id in (123,138,160,342,385,387,507,517,662,533))
then 1 else 0 end tObject,
targetLeveled, area, areaShape, areaLeveled, '' areaUnits, '' areaQuantity, effect, '' damageFormula,
duration, durationUnit, durationLeveled, durationCondition, durationAdder, durationCancel,
saving_throw,fortSave,willSave,refSave,noSave,saveCondition,
spell_resistance, noSR, yesSR, srCondition
from (
select id, name,
casting_time,
case when casting_time like '%free%' then 'free'
when casting_time like '%standard%' then 'standard'
when casting_time like '%round%' then 'round'
when casting_time like '%minute%' then 'minute'
when casting_time like '%hour%' then 'hour'
else 'special' end ctUnit,
range,
case when range like 'Close%' then 'Close'
when range like 'Medium%' then 'Medium'
when range like 'Long%' then 'Long'
when range like '1 mile/level' then 'WhisperingWind'
when range like '40 ft./level' then 'ControlWinds'
when range like 'Up to 10 ft./level' then 'Repulsion'
when range like '0 ft%' then 'noRange'
else '' end as formula,
case when range like '%personal%' then 1 else 0 end as personal,
case when range like '%touch%' then 1 else 0 end as touch,
case when description like '%ranged touch%' then 1 else 0 end as rangeTouch,
target,
case when (target like '%creature%'
or target = 'You'
or id in (153,445,320,473,474,475,341)
or target like '%humanoid%'
or target like '%animal%'
or target like '%plant%') and not id in (109,587,605)
then 1 else 0 end tCreature,
case when target like '%level%' then 1 else 0 end targetLeveled,
area,
case when area like '%radius%' then 'radius'
when area like '%square%' then 'square'
when area like '%line%' and id <> 563 then 'line'
when area like '%cube%' then 'cube'
when area like '%cone%' then 'cone'
when id = 186 then 'controlwater'
when area like '%sq. ft.%' then 'square'
when area like '%apart%' then 'radius'
when area like '%within%' then 'radius'
when area like '%see text%' then 'special' else '' end
as areaShape,
case when area like '%/level%' then 1 else 0 end as areaLeveled,
effect,
duration,
case when duration like 'Instantaneous%' then 'instant'
when duration like '%Concentration%' then 'consentrate'
when duration like '%round%' then 'round'
when duration like '%minute%' or duration like '%min.%' then 'minute'
when duration like '%hour%' then 'hour'
when duration like '%day%' then 'day'
when duration like 'Permanent%' then 'permanent'
else 'special' end durationUnit,
case when duration like '%level%' then 1 else 0 end as durationLeveled,
case when duration like '% or %' or duration like '%until%' then 1 else 0 end as durationCondition,
case when duration like '% and %' or duration like '%+%' then 1 else 0 end as durationAdder,
'' as durationCancel,
saving_throw,
case when saving_throw like '%Fortitude half%' then 'half'
when saving_throw like '%Fortitude negates (harmless)%' then 'negates,harmless'
when saving_throw like '%Fortitude negates (object)%' then 'negates,object'
when saving_throw like '%Fortitude negates%' then 'negates'
when saving_throw like '%Fortitude partial (object)%' then 'partial,object'
when saving_throw like '%Fortitude partial%' then 'partial'
else '' end as fortSave,
case when saving_throw like '%Will half (harmless)%' then 'half,harmless'
when saving_throw like '%Will half%' then 'half'
when saving_throw like '%Will negates (harmless)%' then 'negates,harmless'
when saving_throw like '%Will negates (harmless, object)%' then 'negates,harmless,object'
when saving_throw like '%Will negates (object)%' then 'negates,object'
when saving_throw like '%Will negates%' then 'negates'
when saving_throw like '%Will disbelief%' then 'disbelief'
when saving_throw like '%Will partial%' then 'partial'
else '' end as willSave,
case when saving_throw like '%Reflex half%' then 'half'
when saving_throw like '%Reflex negates (object)%' then 'negates,object'
when saving_throw like '%Reflex negates%' then 'negates'
when saving_throw like '%Reflex partial%' then 'partial'
else '' end as refSave,
case when saving_throw like '%none (object)%' then 'object'
when saving_throw like '%no%' then 'no'
else '' end as noSave,
case when saving_throw like '%see text%' or saving_throw like '% or %' then 1 else 0 end as saveCondition,
spell_resistance,
case when spell_resistance like '%no (object)%' then 'object'
when spell_resistance like '%no%' then 'no' else '' end as noSR,
case when spell_resistance like '%Yes (harmless)%' then 'harmless'
when spell_resistance like '%Yes (harmless, object)%' then 'harmless,object'
when spell_resistance like '%Yes (object)%' then 'object'
when spell_resistance like '%Yes%' then 'yes' else '' end as yesSR,
case when spell_resistance like '%see text%' then 1 else 0 end as srCondition
from spell
where id > 93
) a
order by id
