Over-Indenting of statements

 
Ideas, Issues and Questions
Last Post by Guido 2 years ago
4 Posts
2 Users
1 Reactions
587 Views
Posts: 7
 CmputrAce
Topic starter
Sep 25, 2024 3:52 pm
(@cmputrace)
Member
Joined: 4 years ago
[#128]

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

 


Topic Tags
compact sql formatting
3 Replies
Guido
Posts: 295
 Guido
Admin
Sep 25, 2024 4:48 pm
(@admin)
Member
Joined: 5 years ago

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

grafik

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.

grafik

Reply
CmputrAce reacted
Posts: 7
 CmputrAce
Topic starter
Sep 25, 2024 6:32 pm
(@cmputrace)
Member
Joined: 4 years ago

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


Reply
Guido
Posts: 295
 Guido
Admin
Sep 26, 2024 10:34 pm
(@admin)
Member
Joined: 5 years ago

it will be easier to follow up different questions/issues when they are in different tickets

Thanks

Guido


Reply
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  compact (1) , sql formatting (1) ,
Share: