HOWTO: Format a AQL syntax to be readable

Let's say you are debugging some query that looks like this:


SELECT TOP 100 s.sid as _sid, isnull(s.contact_id, -1) as _contact_id, s.start_time as _start_time, s.dbs_end_time as _dbs_end_time, s.end_time as _end_time, isnull(s.agent_id,-1) as _agent_id, s.direction as _direction, s.audio_ch_no as _channel_number, s.audio_module_no as _module_number, isnull(s.switch_id,-1) as _switch_id, s.dbs_id as _dbs_id, s.local_start_time as _local_start_time, s.local_end_time as _local_end_time, isnull(s.wrapup_time, -1) as _wrapup_time, isnull(u.user_name,'') as _user_name, isnull(c.number_of_conferences,-1) as _number_of_conferences, isnull(c.number_of_holds,-1) as _number_of_holds, isnull(c.number_of_transfers,-1) as _number_of_transfers, isnull(c.total_hold_time,-1) as _total_hold_time, (isnull(s.pbx_login_id,'')) as Agent, (isnull(s.ani,'')) as ANI, (isnull(spd.p1_value,'')) as CD1, (isnull(spd.p10_value,'')) as CD10, (isnull(spd.p11_value,'')) as CD11, (isnull(spd.p12_value,'')) as CD12, (isnull(spd.p13_value,'')) as CD13, (isnull(spd.p14_value,'')) as CD14, (isnull(spd.p15_value,'')) as CD15, (isnull(spd.p16_value,'')) as CD16, (isnull(spd.p17_value,'')) as CD17, (isnull(spd.p18_value,'')) as CD18, (isnull(spd.p19_value,'')) as CD19, (isnull(spd.p2_value,'')) as CD2, (isnull(spd.p20_value,'')) as CD20, (isnull(spd.p21_value,'')) as CD21, (isnull(spd.p22_value,'')) as CD22, (isnull(spd.p23_value,'')) as CD23, (isnull(spd.p24_value,-1)) as CD24, (isnull(spd.p25_value,-1)) as CD25, (isnull(spd.p26_value,'')) as CD26, (isnull(spd.p27_value,'')) as CD27, (isnull(spd.p28_value,'')) as CD28, (isnull(spd.p29_value,'')) as CD29, (isnull(spd.p3_value,'')) as CD3, (isnull(spd.p30_value,'')) as CD30, (isnull(spd.p31_value,'')) as CD31, (isnull(spd.p32_value,'')) as CD32, (isnull(spd.p33_value,'')) as CD33, (isnull(spd.p34_value,'')) as CD34, (isnull(spd.p35_value,'')) as CD35, (isnull(spd.p36_value,'')) as CD36, (isnull(spd.p37_value,'')) as CD37, (isnull(spd.p38_value,'')) as CD38, (isnull(spd.p39_value,'')) as CD39, (isnull(spd.p4_value,'')) as CD4, (isnull(spd.p40_value,'')) as CD40, (isnull(spd.p41_value,'')) as CD41, (isnull(spd.p42_value,'')) as CD42, (isnull(spd.p43_value,'')) as CD43, (isnull(spd.p44_value,'')) as CD44, (isnull(spd.p45_value,'')) as CD45, (isnull(spd.p46_value,'')) as CD46, (isnull(spd.p47_value,'')) as CD47, (isnull(spd.p48_value,'')) as CD48, (isnull(spd.p49_value,-1)) as CD49, (isnull(spd.p5_value,'')) as CD5, (isnull(spd.p50_value,-1)) as CD50, (isnull(spd.p51_value,'')) as CD51, (isnull(spd.p52_value,'')) as CD52, (isnull(spd.p53_value,'')) as CD53, (isnull(spd.p54_value,'')) as CD54, (isnull(spd.p55_value,'')) as CD55, (isnull(spd.p56_value,'')) as CD56, (isnull(spd.p57_value,'')) as CD57, (isnull(spd.p58_value,'')) as CD58, (isnull(spd.p59_value,'')) as CD59, (isnull(spd.p6_value,'')) as CD6, (isnull(spd.p60_value,'')) as CD60, (isnull(spd.p61_value,'')) as CD61, (isnull(spd.p62_value,'')) as CD62, (isnull(spd.p63_value,'')) as CD63, (isnull(spd.p64_value,'')) as CD64, (isnull(spd.p65_value,'')) as CD65, (isnull(spd.p66_value,'')) as CD66, (isnull(spd.p67_value,'')) as CD67, (isnull(spd.p68_value,'')) as CD68, (isnull(spd.p69_value,'')) as CD69, (isnull(spd.p7_value,'')) as CD7, (isnull(spd.p70_value,'')) as CD70, (isnull(spd.p71_value,'')) as CD71, (isnull(spd.p72_value,'')) as CD72, (isnull(spd.p73_value,'')) as CD73, (isnull(spd.p74_value,-1)) as CD74, (isnull(spd.p75_value,-1)) as CD75, (isnull(spd.p8_value,'')) as CD8, (isnull(spd.p9_value,'')) as CD9, (s.audio_ch_no) as Channel_Number, (Direction) as Direction, (isnull(s.dnis_code,'')) as DNIS, (isnull(s.duration,-1)) as Duration, (isnull(s.extension,-1)) as Extension, (isnull(c.is_exception,-1)) as Is_Exception, (s.audio_module_no) as Module_Number, (isnull(s.pcd1_value,-1)) as PCD1, (isnull(s.pcd10_value,-1)) as PCD10, (isnull(s.pcd11_value,-1)) as PCD11, (isnull(s.pcd12_value,-1)) as PCD12, (isnull(s.pcd13_value,-1)) as PCD13, (isnull(s.pcd14_value,-1)) as PCD14, (isnull(s.pcd15_value,-1)) as PCD15, (isnull(s.pcd2_value,-1)) as PCD2, (isnull(s.pcd3_value,-1)) as PCD3, (isnull(s.pcd4_value,-1)) as PCD4, (isnull(s.pcd5_value,-1)) as PCD5, (isnull(s.pcd6_value,-1)) as PCD6, (isnull(s.pcd7_value,-1)) as PCD7, (isnull(s.pcd8_value,-1)) as PCD8, (isnull(s.pcd9_value,-1)) as PCD9, (isnull(s.screens_module,-1)) as Screen_Unit, (isnull(s.switch_call_id,'')) as Switch_Call_ID

FROM Sessions_flat s WITH (NOLOCK) LEFT OUTER JOIN Sessions_pd_flat spd WITH (NOLOCK) ON s.sid = spd.sid and s.dbs_id = spd.dbs_id INNER JOIN Contacts c WITH (NOLOCK) ON s.contact_id = c.contact_id LEFT OUTER JOIN Users u WITH (NOLOCK) ON s.agent_id = u.user_id

WHERE                 ((((isnull(spd.p3_value,'') = 'CMGR_FAILPATTERN') OR (isnull(spd.p3_value,'') = 'CMGR_FAILDATA'))))  AND (s.local_start_time > '1900/01/01 00:00:00.000') AND (s.dbs_end_time >= '2004/05/01 00:00:00.000') AND (s.dbs_end_time <= '2008/07/17 01:06:01.170') AND (s.dbs_id = 1) AND (s.audio_module_no IN (14, 13, 12, 10, 9, 8, 7, 31, 30, 23, 22, 21, 20, 32, 62, 61, 60, 52, 51, 50, 71, 70, 120, 140, 150, 170, 160)) and sid = 25000006334 ORDER BY s.dbs_end_time

I guess one of the first things you are going to do, is to format it to be more readable (Manually).

Take a look at SQLINFORM site that enables you to format the SQL syntax (automatically).

The default end result is:


SELECT  TOP 100 s.sid                      AS _sid                  ,

        isnull(s.contact_id, -1)           AS _contact_id           ,

        s.start_time                       AS _start_time           ,

        s.dbs_end_time                     AS _dbs_end_time         ,

        s.end_time                         AS _end_time             ,

        isnull(s.agent_id,-1)              AS _agent_id             ,

        s.direction                        AS _direction            ,

        s.audio_ch_no                      AS _channel_number       ,

        s.audio_module_no                  AS _module_number        ,

        isnull(s.switch_id,-1)             AS _switch_id            ,

        s.dbs_id                           AS _dbs_id               ,

        s.local_start_time                 AS _local_start_time     ,

        s.local_end_time                   AS _local_end_time       ,

        isnull(s.wrapup_time, -1)          AS _wrapup_time          ,

        isnull(u.user_name,'')             AS _user_name            ,

        isnull(c.number_of_conferences,-1) AS _number_of_conferences,

        isnull(c.number_of_holds,      -1) AS _number_of_holds      ,

        isnull(c.number_of_transfers,  -1) AS _number_of_transfers  ,

        isnull(c.total_hold_time,      -1) AS _total_hold_time      ,

        (isnull(s.pbx_login_id,''))        AS Agent                 ,

        (isnull(s.ani,''))                 AS ANI                   ,

        (isnull(spd.p1_value,''))          AS CD1                   ,

        (isnull(spd.p10_value,''))         AS CD10                  ,

        (isnull(spd.p11_value,''))         AS CD11                  ,

        (isnull(spd.p12_value,''))         AS CD12                  ,

        (isnull(spd.p13_value,''))         AS CD13                  ,

        (isnull(spd.p14_value,''))         AS CD14                  ,

        (isnull(spd.p15_value,''))         AS CD15                  ,

        (isnull(spd.p16_value,''))         AS CD16                  ,

        (isnull(spd.p17_value,''))         AS CD17                  ,

        (isnull(spd.p18_value,''))         AS CD18                  ,

        (isnull(spd.p19_value,''))         AS CD19                  ,

        (isnull(spd.p2_value,''))          AS CD2                   ,

        (isnull(spd.p20_value,''))         AS CD20                  ,

        (isnull(spd.p21_value,''))         AS CD21                  ,

        (isnull(spd.p22_value,''))         AS CD22                  ,

        (isnull(spd.p23_value,''))         AS CD23                  ,

        (isnull(spd.p24_value,-1))         AS CD24                  ,

        (isnull(spd.p25_value,-1))         AS CD25                  ,

        (isnull(spd.p26_value,''))         AS CD26                  ,

        (isnull(spd.p27_value,''))         AS CD27                  ,

        (isnull(spd.p28_value,''))         AS CD28                  ,

        (isnull(spd.p29_value,''))         AS CD29                  ,

        (isnull(spd.p3_value,''))          AS CD3                   ,

        (isnull(spd.p30_value,''))         AS CD30                  ,

        (isnull(spd.p31_value,''))         AS CD31                  ,

        (isnull(spd.p32_value,''))         AS CD32                  ,

        (isnull(spd.p33_value,''))         AS CD33                  ,

        (isnull(spd.p34_value,''))         AS CD34                  ,

        (isnull(spd.p35_value,''))         AS CD35                  ,

        (isnull(spd.p36_value,''))         AS CD36                  ,

        (isnull(spd.p37_value,''))         AS CD37                  ,

        (isnull(spd.p38_value,''))         AS CD38                  ,

        (isnull(spd.p39_value,''))         AS CD39                  ,

        (isnull(spd.p4_value,''))          AS CD4                   ,

        (isnull(spd.p40_value,''))         AS CD40                  ,

        (isnull(spd.p41_value,''))         AS CD41                  ,

        (isnull(spd.p42_value,''))         AS CD42                  ,

        (isnull(spd.p43_value,''))         AS CD43                  ,

        (isnull(spd.p44_value,''))         AS CD44                  ,

        (isnull(spd.p45_value,''))         AS CD45                  ,

        (isnull(spd.p46_value,''))         AS CD46                  ,

        (isnull(spd.p47_value,''))         AS CD47                  ,

        (isnull(spd.p48_value,''))         AS CD48                  ,

        (isnull(spd.p49_value,-1))         AS CD49                  ,

        (isnull(spd.p5_value,''))          AS CD5                   ,

        (isnull(spd.p50_value,-1))         AS CD50                  ,

        (isnull(spd.p51_value,''))         AS CD51                  ,

        (isnull(spd.p52_value,''))         AS CD52                  ,

        (isnull(spd.p53_value,''))         AS CD53                  ,

        (isnull(spd.p54_value,''))         AS CD54                  ,

        (isnull(spd.p55_value,''))         AS CD55                  ,

        (isnull(spd.p56_value,''))         AS CD56                  ,

        (isnull(spd.p57_value,''))         AS CD57                  ,

        (isnull(spd.p58_value,''))         AS CD58                  ,

        (isnull(spd.p59_value,''))         AS CD59                  ,

        (isnull(spd.p6_value,''))          AS CD6                   ,

        (isnull(spd.p60_value,''))         AS CD60                  ,

        (isnull(spd.p61_value,''))         AS CD61                  ,

        (isnull(spd.p62_value,''))         AS CD62                  ,

        (isnull(spd.p63_value,''))         AS CD63                  ,

        (isnull(spd.p64_value,''))         AS CD64                  ,

        (isnull(spd.p65_value,''))         AS CD65                  ,

        (isnull(spd.p66_value,''))         AS CD66                  ,

        (isnull(spd.p67_value,''))         AS CD67                  ,

        (isnull(spd.p68_value,''))         AS CD68                  ,

        (isnull(spd.p69_value,''))         AS CD69                  ,

        (isnull(spd.p7_value,''))          AS CD7                   ,

        (isnull(spd.p70_value,''))         AS CD70                  ,

        (isnull(spd.p71_value,''))         AS CD71                  ,

        (isnull(spd.p72_value,''))         AS CD72                  ,

        (isnull(spd.p73_value,''))         AS CD73                  ,

        (isnull(spd.p74_value,-1))         AS CD74                  ,

        (isnull(spd.p75_value,-1))         AS CD75                  ,

        (isnull(spd.p8_value,''))          AS CD8                   ,

        (isnull(spd.p9_value,''))          AS CD9                   ,

        (s.audio_ch_no)                    AS Channel_Number        ,

        (Direction)                        AS Direction             ,

        (isnull(s.dnis_code,''))           AS DNIS                  ,

        (isnull(s.duration,    -1))            AS Duration              ,

        (isnull(s.extension,   -1))            AS Extension             ,

        (isnull(c.is_exception,-1))            AS Is_Exception          ,

        (s.audio_module_no)                    AS Module_Number         ,

        (isnull(s.pcd1_value,    -1))              AS PCD1                  ,

        (isnull(s.pcd10_value,   -1))              AS PCD10                 ,

        (isnull(s.pcd11_value,   -1))              AS PCD11                 ,

        (isnull(s.pcd12_value,   -1))              AS PCD12                 ,

        (isnull(s.pcd13_value,   -1))              AS PCD13                 ,

        (isnull(s.pcd14_value,   -1))              AS PCD14                 ,

        (isnull(s.pcd15_value,   -1))              AS PCD15                 ,

        (isnull(s.pcd2_value,    -1))              AS PCD2                  ,

        (isnull(s.pcd3_value,    -1))              AS PCD3                  ,

        (isnull(s.pcd4_value,    -1))              AS PCD4                  ,

        (isnull(s.pcd5_value,    -1))              AS PCD5                  ,

        (isnull(s.pcd6_value,    -1))              AS PCD6                  ,

        (isnull(s.pcd7_value,    -1))              AS PCD7                  ,

        (isnull(s.pcd8_value,    -1))              AS PCD8                  ,

        (isnull(s.pcd9_value,    -1))              AS PCD9                  ,

        (isnull(s.screens_module,-1))              AS Screen_Unit           ,

        (isnull(s.switch_call_id,''))              AS Switch_Call_ID

FROM    Sessions_flat s WITH (NOLOCK)

        LEFT OUTER JOIN Sessions_pd_flat spd WITH (NOLOCK)

        ON      s.sid    = spd.sid

            AND s.dbs_id = spd.dbs_id

        INNER JOIN Contacts c WITH (NOLOCK)

        ON      s.contact_id = c.contact_id


        ON      s.agent_id        = u.user_id

WHERE ((((isnull(spd.p3_value,'') = 'CMGR_FAILPATTERN')

     OR (isnull(spd.p3_value,'')  = 'CMGR_FAILDATA'))))

    AND (s.local_start_time       > '1900/01/01 00:00:00.000')

    AND (s.dbs_end_time          >= '2004/05/01 00:00:00.000')

    AND (s.dbs_end_time          <= '2008/07/17 01:06:01.170')

    AND (s.dbs_id                 = 1)

    AND (s.audio_module_no       IN (14, 13, 12, 10, 9, 8, 7, 31, 30, 23, 22, 21, 20, 32, 62, 61, 60, 52, 51, 50, 71, 70, 120, 140, 150, 170, 160))

    AND sid                       = 25000006334

ORDER BY s.dbs_end_time

Now let's see you saying – I don’t understand this query :)

There are other sites and utilities but this one is working great.

Download original query

Download formatted query

HOWTO: Format a AQL syntax to be readable HOWTO: Format a AQL syntax to be readable Reviewed by Ran Davidovitz on 1:44 PM Rating: 5

No comments:

Powered by Blogger.