SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform_ims].[Invoice_No_Order_No]
AS
SELECT DISTINCT T1."order_number" AS "Order Number",
	T1."invoice_number" AS "Invoice Number_ohne_type",
	T2."invoice_type" AS "Invoice Type",
	(substring((convert(VARCHAR(50), T2."invoice_type")), 1, 1)) + (substring((convert(VARCHAR(50), T1."invoice_number")), 1, 6)) AS "Invoice Number"
FROM (
	LOCOSOFT."dbo"."invoices" T2 LEFT OUTER JOIN LOCOSOFT."dbo"."labours" T1 ON (T1."invoice_number" = T2."invoice_number")
		AND (T1."invoice_type" = T2."invoice_type")
	)
WHERE (((substring((convert(VARCHAR(50), T2."invoice_type")), 1, 1)) + (substring((convert(VARCHAR(50), T1."invoice_number")), 1, 6))) <> '')
	-- order by "Order Number" asc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
