SQLinForm SQL FormatterCommunity Discussion Forum
SQLinForm SQL Formatter
Hello, Here's some SQL that doesn't come out quite as expected:
select cai.instruction_uid, cai.resource_id, CAST(cai.start_date AS DATE) start_date, case when (cai.start_hour - 1) between 0 and 9 then to_date(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 0' || to_char(cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy'),'mm/dd/yyyy') else to_date(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || to_char(cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy'),'mm/dd/yyyy') end start_date_ppt, cai.start_hour, decode(cai.start_hour,24, to_number(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 00:00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24')), case when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_LONG_DAY]' and cai.start_hour = 10 then 25 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_LONG_DAY]' and cai.start_hour = 9 then 2 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_SHORT_DAY]' and cai.start_hour = 10 then 2 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24') = '00' then 24 else to_number(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24')) end) start_hour_ppt, (cai.start_hour - 1) start_hour_orig, decode((cai.start_hour - 1),24, to_number(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 00:00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24')), case when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_LONG_DAY]' and (cai.start_hour - 1) = 10 then 25 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_LONG_DAY]' and (cai.start_hour - 1) = 9 then 2 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_SHORT_DAY]' and (cai.start_hour - 1) = 10 then 2 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24') = '00' then 24 else to_number(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24')) end) start_hour_orig_ppt, cai.start_hour stop_hour, decode(cai.start_hour,24, to_number(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 00:00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24')), case when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_LONG_DAY]' and cai.start_hour = 10 then 25 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_LONG_DAY]' and cai.start_hour = 9 then 2 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'mm/dd/yyyy') = '[DST_SHORT_DAY]' and cai.start_hour = 10 then 2 when to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24') = '00' then 24 else to_number(to_char(CAST((FROM_TZ(CAST(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'HH24')) end) stop_hour_ppt, cai.start_min, cai.bid_delay, case when cai.bid_delay >= 0 then (cai.start_min - cai.bid_delay) end start_min_adj, cai.start_sec, CAST(cai.start_time AS DATE) start_time, CAST((FROM_TZ(cai.start_time,'+00:00') AT TIME ZONE 'US/Pacific') AS DATE) start_time_ppt, CAST((cai.start_time - NUMTODSINTERVAL(cai.start_sec, 'second')) - NUMTODSINTERVAL(cai.bid_delay, 'minute') AS DATE) start_time_adj, CAST((FROM_TZ((cai.start_time - NUMTODSINTERVAL(cai.start_sec, 'second')) - NUMTODSINTERVAL(cai.bid_delay, 'minute'),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE) start_time_adj_ppt, to_char(cai.dot,'99999.9999') dot, to_char(cai_details.mw,'99999.9999') mw, cai_details.service_type, CAST(cai.date_inserted AS DATE) date_inserted, CAST(cai.date_updated AS DATE) date_updated from ze_data.CAISO_ADS_INSTRUCTIONS@mdrlink cai, ze_data.CAISO_ADS_INSTRUCTION_DETAILS@mdrlink cai_details where cai.instruction_uid = cai_details.instruction_uid and cai_details.service_type = 'SUPP' order by cai.start_date, cai.start_hour, cai.start_min
I selected the "Compact" button in the SQL / Select node.
Here's the output:
select cai.instruction_uid, cai.resource_id, cast(cai.start_date as date) start_date,
case
when (cai.start_hour - 1) between 0 and 9
then to_date(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 0' || to_char(cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy'),'mm/dd/yyyy')
else to_date(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || to_char(cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy'),'mm/dd/yyyy')
end start_date_ppt, cai.start_hour, decode(cai.start_hour,24, to_number(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 00:00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24')),
case
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_LONG_DAY]'
and cai.start_hour = 10
then 25
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_LONG_DAY]'
and cai.start_hour = 9
then 2
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_SHORT_DAY]'
and cai.start_hour = 10
then 2
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24') = '00'
then 24
else to_number(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24'))
end) start_hour_ppt, (cai.start_hour - 1) start_hour_orig, decode((cai.start_hour - 1),24, to_number(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 00:00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24')),
case
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_LONG_DAY]'
and (
cai.start_hour - 1) = 10
then 25
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_LONG_DAY]'
and (
cai.start_hour - 1) = 9
then 2
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_SHORT_DAY]'
and (
cai.start_hour - 1) = 10
then 2
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24') = '00'
then 24
else to_number(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || (cai.start_hour - 1) || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24'))
end) start_hour_orig_ppt, cai.start_hour stop_hour, decode(cai.start_hour,24, to_number(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' 00:00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24')),
case
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_LONG_DAY]'
and cai.start_hour = 10
then 25
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_LONG_DAY]'
and cai.start_hour = 9
then 2
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'mm/dd/yyyy') = '[DST_SHORT_DAY]'
and cai.start_hour = 10
then 2
when to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24') = '00'
then 24
else to_number(to_char(cast((from_tz(cast(to_date(to_char(cai.start_date,'mm/dd/yyyy') || ' ' || cai.start_hour || ':00:00','mm/dd/yyyy hh24:mi:ss') as timestamp),'+00:00') at time zone 'US/Pacific') as date), 'HH24'))
end) stop_hour_ppt, cai.start_min, cai.bid_delay,
case
when cai.bid_delay >= 0
then (cai.start_min - cai.bid_delay)
end start_min_adj, cai.start_sec, cast(cai.start_time as date) start_time, cast((from_tz(cai.start_time,'+00:00') at time zone 'US/Pacific') as date) start_time_ppt, cast((cai.start_time - numtodsinterval(cai.start_sec, 'second')) - numtodsinterval(cai.bid_delay, 'minute') as date) start_time_adj, cast((from_tz((cai.start_time - numtodsinterval(cai.start_sec, 'second')) - numtodsinterval(cai.bid_delay, 'minute'),'+00:00') at time zone 'US/Pacific') as date) start_time_adj_ppt, to_char(cai.dot,'99999.9999') dot, to_char(cai_details.mw,'99999.9999') mw, cai_details.service_type, cast(cai.date_inserted as date) date_inserted, cast(cai.date_updated as date) date_updated
from ze_data.caiso_ads_instructions@mdrlink cai, ze_data.caiso_ads_instruction_details@mdrlink cai_details
where cai.instruction_uid = cai_details.instruction_uid
and cai_details.service_type = 'SUPP'
order by cai.start_date, cai.start_hour, cai.start_min
Thank you,
Jim
Hi @cmputrace, the strange formatting comes from the fact, that the formatter breaks the decode in such way that it is aligned below the decode keyword
When you choose to put more than one column per line then the decode can be in the middle of the line and then the break happens there
If you choose to put only 1 column per line then it will look more consistent.
Thank you! I'm spending some time right now redoing my profiles and checking the improvements. Should I create different threads to check other issues?
Jim
it will be easier to follow up different questions/issues when they are in different tickets
Thanks
Guido
