HOWTO: Format a AQL syntax to be readable
Let's say you are debugging some query that looks like this:
SET TRANSACTION ISOLATION LEVEL Read uncommitted
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:
SET TRANSACTION ISOLATION LEVEL Read uncommitted
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
Now let's see you saying – I don’t understand this query :)
There are other sites and utilities but this one is working great.
No comments:
Post a Comment