Sometimes select-queries become very hard to read, especially when you use display expressions. A lot of nesting takes place.
In order to make them readable, I use the ApexSQL formatting plugin, but I think it would help a lot of people if the GUI did the propper formatting for you. Especially when you need to debug or tune something.
This is an example from a current project:
select top 1000 t1.[sales_order_id], t1.[administration_id], t1.[customer_id], t1.[sales_order_number], t1.[sales_order_type], t1.[based_on_complaint_id], t1.[payment_term_id], t1.[delivery_term_id], t1.[customer_reference], t1.[delivery_address_id], t1.[type_delivery_date], t1.[delivery_date], t1.[delivery_year], t1.[delivery_month], t1.[delivery_week], rtrim(t1.[currency_code]) as [currency_code], t1.[logistics_remark], t1.[invoice_organisation_id], t1.[invoice_recipient_person_id], t1.[avg_invoice_recipient_person_id], t1.[invoice_address_id], t1.[regular_confirmation_or_pro_forma], t1.[certificate_recipient_person_id], t1.[avg_certificate_recipient_person_id], t1.[exchange_rate], t1.[quotation_or_order], t1.[valid_until], t1.[delivery_time_after_agreement], t1.[delivery_time_sort], t1.[organisation_remark], t1.[agent_id], t1.[organisation_agent_fee], t1.[commission_calculation], t1.[commission_amount], t1.[percentage_amount], t1.[customer_contact_id], t1.[avg_customer_contact_id], t1.[manual_calculation_agent_fee], t1.[current_credit_limit], t1.[sales_order_status], t1.[sales_order_look_up], t1.[total_price_line], t1.[total_price_line_costs], t1.[total_price_header_costs], t1.[total_price_order], t1.[shipping_address_id], t1.[neutral_delivery], t1.[default_sales_location], t1.[total_processing_discount], t1.[sales_order_discount], t1.[sales_order_discount_percentage], t1.[sales_order_gross_profit], t1.[total_price_order_including_discount], t1.[added_by_look_up], t1.[vat_type_id], t1.[total_price_incl_vat_discount], t1.[default_price], t1.[default_price_per], t1.[contains_crossdock], t1.[purchase_order_crossdock], t1.[sales_order_form_id], t1.[charge_costs_type], t1.[added_on_look_up], t1.[total_order_amount_kg], t1.[use_po_box], t1.[pick_up_time], t1.[on_hold_logistic], t1.[total_cost_price], t1.[total_gross_profit_percentage], t1.[added_by], t1.[added_on], t1.[modified_by], t1.[modified_on]
from (
select t1.[sales_order_id], t1.[administration_id], t1.[customer_id], t1.[sales_order_number], t1.[sales_order_type], t1.[based_on_complaint_id], t1.[payment_term_id], t1.[delivery_term_id], t1.[customer_reference], t1.[delivery_address_id], t1.[type_delivery_date], t1.[delivery_date], t1.[delivery_year], t1.[delivery_month], t1.[delivery_week], t1.[currency_code], t1.[logistics_remark], t1.[invoice_organisation_id], t1.[invoice_recipient_person_id], t1.[avg_invoice_recipient_person_id], t1.[invoice_address_id], t1.[regular_confirmation_or_pro_forma], t1.[certificate_recipient_person_id], t1.[avg_certificate_recipient_person_id], (dbo.get_exchange_rate(t1.administration_id,t1.currency_code,t1.delivery_date)
)
as [exchange_rate], t1.[quotation_or_order], t1.[valid_until], t1.[delivery_time_after_agreement], t1.[delivery_time_sort], t1.[organisation_remark], t1.[agent_id], t1.[organisation_agent_fee], t1.[commission_calculation], t1.[commission_amount], t1.[percentage_amount], t1.[customer_contact_id], t1.[avg_customer_contact_id], t1.[manual_calculation_agent_fee], t1.[current_credit_limit], t1.[sales_order_status], (select iif(so.quotation_or_order = 1
,'Order nr. '
,'Offerte nr. ') + cast(so.sales_order_number as varchar(30)) + ' - '
+ o.organisation_name as sales_order_look_up
from sales_order so
join organisation o
on o.organisation_id = so.customer_id
where so.sales_order_id = t1.sales_order_id
)
as [sales_order_look_up], t1.[total_price_line], t1.[total_price_line_costs], t1.[total_price_header_costs], t1.[total_price_order], t1.[shipping_address_id], t1.[neutral_delivery], t1.[default_sales_location], t1.[total_processing_discount], t1.[sales_order_discount], t1.[sales_order_discount_percentage], t1.[sales_order_gross_profit], ((t1.total_price_order - ISNULL(t1.sales_order_discount, 0))
)
as [total_price_order_including_discount], (t1.added_by
)
as [added_by_look_up], t1.[vat_type_id], ((t1.total_price_order - ISNULL(t1.sales_order_discount, 0)) +
((t1.total_price_order - ISNULL(t1.sales_order_discount, 0)) *
(select vat_percentage / 100 from vat_type where vat_type_id = t1.vat_type_id))
)
as [total_price_incl_vat_discount], t1.[default_price], t1.[default_price_per], (select top 1 crossdock_type
from sales_order_line sol
where sol.sales_order_id = t1.sales_order_id
order by crossdock_type desc
)
as [contains_crossdock], (select 'Order nr. ' + STRING_AGG(cast(purchase_order_number as varchar(15)), ', ')
from (select distinct dbo.get_purchase_order_number(x.purchase_order_id) purchase_order_number
from purchase_order x
where x.crossdocked_with_sales_order_id = t1.sales_order_id
)y
)
as [purchase_order_crossdock], t1.[sales_order_form_id], t1.[charge_costs_type], (cast(t1.added_on as date)
)
as [added_on_look_up], t1.[total_order_amount_kg], t1.[use_po_box], t1.[pick_up_time], t1.[on_hold_logistic], t1.[total_cost_price], (isnull(t1.sales_order_gross_profit,0) /iif(t1.total_price_order = 0,1,isnull(t1.total_price_order,1)) * 100
)
as [total_gross_profit_percentage], t1.[added_by], t1.[added_on], t1.[modified_by], t1.[modified_on]
from [sales_order] as t1
where ((dbo.check_sales_order_status_is_open(t1.sales_order_id) = 1
) and (t1.administration_id in (select administration_id from dbo.get_my_administrations())
) and (t1.administration_id = dbo.get_my_current_administration()
) )
) as t1
left outer join (
select t1.[administration_id], t1.[organisation_id], ((select organisation_name from organisation t2 where t2.organisation_id = t1.organisation_id)
)
as [customer_name]
from [customer] as t1
) as t2 on t1.[administration_id] = t2.[administration_id] and t1.[customer_id] = t2.[organisation_id]
left outer join (
select t1.[delivery_term_id], t1.[delivery_term_name]
from [delivery_term] as t1
) as t3 on t1.[delivery_term_id] = t3.[delivery_term_id]
where (t1.[added_by_look_up] = 'me' and t2.[customer_name] = N'Customer' and t3.[delivery_term_name] = N'Niet van Toepassing')
order by t1.[sales_order_id] descThis is after I use a formatting tool, please note, it looks even better in SSMS:
select top 1000 t1.[sales_order_id]
,t1.[administration_id]
,t1.[customer_id]
,t1.[sales_order_number]
,t1.[sales_order_type]
,t1.[based_on_complaint_id]
,t1.[payment_term_id]
,t1.[delivery_term_id]
,t1.[customer_reference]
,t1.[delivery_address_id]
,t1.[type_delivery_date]
,t1.[delivery_date]
,t1.[delivery_year]
,t1.[delivery_month]
,t1.[delivery_week]
,rtrim(t1.[currency_code]) as [currency_code]
,t1.[logistics_remark]
,t1.[invoice_organisation_id]
,t1.[invoice_recipient_person_id]
,t1.[avg_invoice_recipient_person_id]
,t1.[invoice_address_id]
,t1.[regular_confirmation_or_pro_forma]
,t1.[certificate_recipient_person_id]
,t1.[avg_certificate_recipient_person_id]
,t1.[exchange_rate]
,t1.[quotation_or_order]
,t1.[valid_until]
,t1.[delivery_time_after_agreement]
,t1.[delivery_time_sort]
,t1.[organisation_remark]
,t1.[agent_id]
,t1.[organisation_agent_fee]
,t1.[commission_calculation]
,t1.[commission_amount]
,t1.[percentage_amount]
,t1.[customer_contact_id]
,t1.[avg_customer_contact_id]
,t1.[manual_calculation_agent_fee]
,t1.[current_credit_limit]
,t1.[sales_order_status]
,t1.[sales_order_look_up]
,t1.[total_price_line]
,t1.[total_price_line_costs]
,t1.[total_price_header_costs]
,t1.[total_price_order]
,t1.[shipping_address_id]
,t1.[neutral_delivery]
,t1.[default_sales_location]
,t1.[total_processing_discount]
,t1.[sales_order_discount]
,t1.[sales_order_discount_percentage]
,t1.[sales_order_gross_profit]
,t1.[total_price_order_including_discount]
,t1.[added_by_look_up]
,t1.[vat_type_id]
,t1.[total_price_incl_vat_discount]
,t1.[default_price]
,t1.[default_price_per]
,t1.[contains_crossdock]
,t1.[purchase_order_crossdock]
,t1.[sales_order_form_id]
,t1.[charge_costs_type]
,t1.[added_on_look_up]
,t1.[total_order_amount_kg]
,t1.[use_po_box]
,t1.[pick_up_time]
,t1.[on_hold_logistic]
,t1.[total_cost_price]
,t1.[total_gross_profit_percentage]
,t1.[added_by]
,t1.[added_on]
,t1.[modified_by]
,t1.[modified_on]
from (select t1.[sales_order_id]
,t1.[administration_id]
,t1.[customer_id]
,t1.[sales_order_number]
,t1.[sales_order_type]
,t1.[based_on_complaint_id]
,t1.[payment_term_id]
,t1.[delivery_term_id]
,t1.[customer_reference]
,t1.[delivery_address_id]
,t1.[type_delivery_date]
,t1.[delivery_date]
,t1.[delivery_year]
,t1.[delivery_month]
,t1.[delivery_week]
,t1.[currency_code]
,t1.[logistics_remark]
,t1.[invoice_organisation_id]
,t1.[invoice_recipient_person_id]
,t1.[avg_invoice_recipient_person_id]
,t1.[invoice_address_id]
,t1.[regular_confirmation_or_pro_forma]
,t1.[certificate_recipient_person_id]
,t1.[avg_certificate_recipient_person_id]
, (dbo.get_exchange_rate (t1.administration_id,t1.currency_code,t1.delivery_date
)
) as [exchange_rate]
,t1.[quotation_or_order]
,t1.[valid_until]
,t1.[delivery_time_after_agreement]
,t1.[delivery_time_sort]
,t1.[organisation_remark]
,t1.[agent_id]
,t1.[organisation_agent_fee]
,t1.[commission_calculation]
,t1.[commission_amount]
,t1.[percentage_amount]
,t1.[customer_contact_id]
,t1.[avg_customer_contact_id]
,t1.[manual_calculation_agent_fee]
,t1.[current_credit_limit]
,t1.[sales_order_status]
, (select iif(so.quotation_or_order = 1,'Order nr. ','Offerte nr. ') + cast(so.sales_order_number as varchar(30)) + ' - ' + o.organisation_name as sales_order_look_up
from sales_order as so
join organisation as o
on o.organisation_id = so.customer_id
where so.sales_order_id = t1.sales_order_id
) as [sales_order_look_up]
,t1.[total_price_line]
,t1.[total_price_line_costs]
,t1.[total_price_header_costs]
,t1.[total_price_order]
,t1.[shipping_address_id]
,t1.[neutral_delivery]
,t1.[default_sales_location]
,t1.[total_processing_discount]
,t1.[sales_order_discount]
,t1.[sales_order_discount_percentage]
,t1.[sales_order_gross_profit]
, ( (t1.total_price_order - isnull(t1.sales_order_discount,0)
)
) as [total_price_order_including_discount]
, (t1.added_by
) as [added_by_look_up]
,t1.[vat_type_id]
, ( (t1.total_price_order - isnull(t1.sales_order_discount,0)
) + ( (t1.total_price_order - isnull(t1.sales_order_discount,0)
) * (select vat_percentage / 100
from vat_type
where vat_type_id = t1.vat_type_id
)
)
) as [total_price_incl_vat_discount]
,t1.[default_price]
,t1.[default_price_per]
, (select top 1 crossdock_type
from sales_order_line as sol
where sol.sales_order_id = t1.sales_order_id
order by crossdock_type desc
) as [contains_crossdock]
, (select 'Order nr. ' + string_agg(cast(purchase_order_number as varchar(15)),', ')
from (select distinct
dbo.get_purchase_order_number (x.purchase_order_id
) as purchase_order_number
from purchase_order as x
where x.crossdocked_with_sales_order_id = t1.sales_order_id
) as y
) as [purchase_order_crossdock]
,t1.[sales_order_form_id]
,t1.[charge_costs_type]
, (cast(t1.added_on as date)
) as [added_on_look_up]
,t1.[total_order_amount_kg]
,t1.[use_po_box]
,t1.[pick_up_time]
,t1.[on_hold_logistic]
,t1.[total_cost_price]
, (isnull(t1.sales_order_gross_profit,0) / iif(t1.total_price_order = 0,1,isnull(t1.total_price_order,1)) * 100
) as [total_gross_profit_percentage]
,t1.[added_by]
,t1.[added_on]
,t1.[modified_by]
,t1.[modified_on]
from [sales_order] as t1
where ( (dbo.check_sales_order_status_is_open (t1.sales_order_id
) = 1
)
and (t1.administration_id in (select administration_id
from dbo.get_my_administrations()
)
)
and (t1.administration_id = dbo.get_my_current_administration()
)
)
) as t1
left outer join (select t1.[administration_id]
,t1.[organisation_id]
, ( (select organisation_name
from organisation as t2
where t2.organisation_id = t1.organisation_id
)
) as [customer_name]
from [customer] as t1
) as t2
on t1.[administration_id] = t2.[administration_id]
and t1.[customer_id] = t2.[organisation_id]
left outer join (select t1.[delivery_term_id]
,t1.[delivery_term_name]
from [delivery_term] as t1
) as t3
on t1.[delivery_term_id] = t3.[delivery_term_id]
where (t1.[added_by_look_up] = 'me'
and t2.[customer_name] = N'Customer'
and t3.[delivery_term_name] = N'Niet van Toepassing'
)
order by t1.[sales_order_id] desc
