Link to home
Create AccountLog in
Avatar of metropia
metropiaFlag for United States of America

asked on

rewriting sql query -

I have a query that uses the operators *= or =* to left or right outer join tables (I think)

I need help rewriting how the tables are joined because I want to use this query to create a view and SQL Server does not allow me use =* or *= operators when creating a view.

I would like to ask for help rewriting this query if possible.

SELECT
	convert (SMALLDATETIME, convert(char(2), DatePart(mm, dbo.CUSTOMER_ORDER.DESIRED_SHIP_DATE)) + '/' + convert(char(2), DatePart(dd, 	dbo.CUSTOMER_ORDER.DESIRED_SHIP_DATE)) + '/' + convert(char(4), DatePart(yyyy, dbo.CUSTOMER_ORDER.DESIRED_SHIP_DATE))),
	dbo.CUSTOMER_ORDER.ID,
  	dbo.CUSTOMER.ID,
  	dbo.CUSTOMER_ORDER.SALESREP_ID,
  	convert (SMALLDATETIME, convert(char(2), DatePart(mm, dbo.CUSTOMER_ORDER.ORDER_DATE)) 
	+ '/' 
	+ convert(char(2), DatePart(dd, dbo.CUSTOMER_ORDER.ORDER_DATE)) 
	+ '/' 
	+ convert(char(4), DatePart(yyyy, dbo.CUSTOMER_ORDER.ORDER_DATE))),
  	dbo.CUSTOMER_ORDER.CUSTOMER_PO_REF,
  	case
 		when dbo.CUSTOMER.NAME is not null then dbo.CUSTOMER.NAME
 		else ' '
	end,
  	case
		when dbo.CUSTOMER.ADDR_1 is not null then dbo.CUSTOMER.ADDR_1
		else ' '
	end,
	case
		when dbo.CUSTOMER.ADDR_2 is not null then dbo.CUSTOMER.ADDR_2
		else ' '
	end,
	case
		when dbo.CUSTOMER.ADDR_3 is not null then dbo.CUSTOMER.ADDR_3
		else ' '
	end,
	dbo.CUSTOMER.CITY + ', ' + dbo.CUSTOMER.STATE + '   '  + dbo.CUSTOMER.ZIPCODE,
	CASE  
		WHEN dbo.CUSTOMER_ORDER.CONTACT_LAST_NAME is null  THEN dbo.CUSTOMER_ORDER.CONTACT_FIRST_NAME 
		ELSE dbo.CUSTOMER_ORDER.CONTACT_FIRST_NAME + ' '  + dbo.CUSTOMER_ORDER.CONTACT_LAST_NAME  
	END,
	case 
		when dbo.CUST_ADDRESS.NAME is not null then dbo.CUST_ADDRESS.NAME
		else ' '
	end,
	case 
		when dbo.CUST_ADDRESS.ADDR_1 is not null then dbo.CUST_ADDRESS.ADDR_1
		else ' '
	end,
	case 
		when dbo.CUST_ADDRESS.ADDR_2 is not null then dbo.CUST_ADDRESS.ADDR_2
		else ' '
	end,
	dbo.CUST_ADDRESS.CITY + ', ' + dbo.CUST_ADDRESS.STATE + '   '  + dbo.CUST_ADDRESS.ZIPCODE,
	dbo.CUSTOMER_ORDER.SHIP_VIA,
	convert(varchar(2048),Convert(binary(2048),dbo.CUST_ORDER_BINARY.BITS)),
	isnull(dbo.CUST_ORDER_LINE.PART_ID, 'Freight or AR'),
	dbo.RM_CUSTORD_EXTRA.PIECES,
	dbo.RM_CUSTORD_EXTRA.LENGTH,
	case dbo.RM_CUSTORD_EXTRA.INCHES
		when 'RML' then 0
		else dbo.RM_CUSTORD_EXTRA.INCHES
	end,
	dbo.RM_CUSTORD_EXTRA.FRACTION,
	dbo.CUST_ORDER_LINE.LINE_NO,
	dbo.RM_CUSTORD_EXTRA.TOLERANCE_CODE,
	(dbo.PART.WEIGHT) * dbo.CUST_ORDER_LINE.ORDER_QTY,
	dbo.CUSTOMER_ORDER.TERRITORY,
	dbo.CUSTOMER.NAME,
	dbo.RG_PACKAGING.PackagingMethod,
	dbo.RG_CUST_NOTATION_TOP1.NOTE,
	dbo.RM_CUSTORD_EXTRA.UB_ROW_NUMBER,
	dbo.RM_CUSTORD_EXTRA.UB_BENDER_SORT_ORDER,
	max(( (dbo.CUST_ORDER_LINE.ORDER_QTY) ))
FROM
	dbo.CUSTOMER_ORDER,
	dbo.CUST_ORDER_LINE,
	dbo.CUST_ADDRESS,
	dbo.CUST_ORDER_BINARY,
	dbo.RM_CUSTORD_EXTRA,
	dbo.RG_PACKAGING,
	dbo.RG_CUST_NOTATION_TOP1,
	dbo.CUSTOMER,
	dbo.PART
WHERE
  ( dbo.CUSTOMER_ORDER.ID=dbo.CUST_ORDER_LINE.CUST_ORDER_ID  )
  AND  ( dbo.CUST_ADDRESS.CUSTOMER_ID=dbo.CUSTOMER_ORDER.CUSTOMER_ID and dbo.CUST_ADDRESS.ADDR_NO=dbo.CUSTOMER_ORDER.SHIP_TO_ADDR_NO  )
  AND  ( dbo.CUSTOMER_ORDER.ID*=dbo.CUST_ORDER_BINARY.CUST_ORDER_ID  )
  AND  ( dbo.CUST_ORDER_LINE.CUST_ORDER_ID*=dbo.RM_CUSTORD_EXTRA.CUST_ORDER_ID  )
  AND  ( dbo.RG_PACKAGING.ID=*dbo.CUST_ORDER_LINE.USER_4  )
  AND  ( dbo.RG_CUST_NOTATION_TOP1.OWNER_ID=*dbo.CUSTOMER.ID  )
  AND  ( dbo.CUST_ORDER_LINE.LINE_NO*=dbo.RM_CUSTORD_EXTRA.CUST_ORDER_LINE_NO  )
  AND  ( dbo.CUST_ADDRESS.CUSTOMER_ID=dbo.CUSTOMER.ID  )
  AND  ( dbo.CUST_ORDER_LINE.PART_ID=dbo.PART.ID  )
  AND  ( dbo.CUSTOMER_ORDER.ID  =  ('C251168') )
GROUP BY
	convert (SMALLDATETIME, convert(char(2), DatePart(mm, dbo.CUSTOMER_ORDER.DESIRED_SHIP_DATE)) + '/' + convert(char(2), DatePart(dd, dbo.CUSTOMER_ORDER.DESIRED_SHIP_DATE)) + '/' + convert(char(4), DatePart(yyyy, dbo.CUSTOMER_ORDER.DESIRED_SHIP_DATE))), 
	dbo.CUSTOMER_ORDER.ID, 
	dbo.CUSTOMER.ID, 
	dbo.CUSTOMER_ORDER.SALESREP_ID, 
	convert (SMALLDATETIME, convert(char(2), DatePart(mm, dbo.CUSTOMER_ORDER.ORDER_DATE)) + '/' + convert(char(2), DatePart(dd, dbo.CUSTOMER_ORDER.ORDER_DATE)) + '/' + convert(char(4), DatePart(yyyy, dbo.CUSTOMER_ORDER.ORDER_DATE))), 
	dbo.CUSTOMER_ORDER.CUSTOMER_PO_REF, 
	case
		when dbo.CUSTOMER.NAME is not null then dbo.CUSTOMER.NAME
		else ' '
	end, 
	case
		when dbo.CUSTOMER.ADDR_1 is not null then dbo.CUSTOMER.ADDR_1
		else ' '
	end, 
	case
		when dbo.CUSTOMER.ADDR_2 is not null then dbo.CUSTOMER.ADDR_2
		else ' '
	end, 
	case
		when dbo.CUSTOMER.ADDR_3 is not null then dbo.CUSTOMER.ADDR_3
		else ' '
	end, 
	dbo.CUSTOMER.CITY + ', ' + dbo.CUSTOMER.STATE + '   '  + dbo.CUSTOMER.ZIPCODE, 
	CASE  
		WHEN dbo.CUSTOMER_ORDER.CONTACT_LAST_NAME is null  THEN dbo.CUSTOMER_ORDER.CONTACT_FIRST_NAME 
		ELSE dbo.CUSTOMER_ORDER.CONTACT_FIRST_NAME + ' '  + dbo.CUSTOMER_ORDER.CONTACT_LAST_NAME  
	END, 
	case 
		when dbo.CUST_ADDRESS.NAME is not null then dbo.CUST_ADDRESS.NAME
		else ' '
	end, 
	case 
		when dbo.CUST_ADDRESS.ADDR_1 is not null then dbo.CUST_ADDRESS.ADDR_1
		else ' '
	end, 
	case 
		when dbo.CUST_ADDRESS.ADDR_2 is not null then dbo.CUST_ADDRESS.ADDR_2
		else ' '
	end, 
	dbo.CUST_ADDRESS.CITY + ', ' + dbo.CUST_ADDRESS.STATE + '   '  + dbo.CUST_ADDRESS.ZIPCODE, 
	dbo.CUSTOMER_ORDER.SHIP_VIA, 
	convert(varchar(2048),Convert(binary(2048),dbo.CUST_ORDER_BINARY.BITS)), 
	isnull(dbo.CUST_ORDER_LINE.PART_ID, 'Freight or AR'), 
	dbo.RM_CUSTORD_EXTRA.PIECES, 
	dbo.RM_CUSTORD_EXTRA.LENGTH, 
	case dbo.RM_CUSTORD_EXTRA.INCHES
		when 'RML' then 0
		else dbo.RM_CUSTORD_EXTRA.INCHES
	end, 
	dbo.RM_CUSTORD_EXTRA.FRACTION, 
	dbo.CUST_ORDER_LINE.LINE_NO, 
	dbo.RM_CUSTORD_EXTRA.TOLERANCE_CODE, 
	(dbo.PART.WEIGHT) * dbo.CUST_ORDER_LINE.ORDER_QTY, 
	dbo.CUSTOMER_ORDER.TERRITORY, 
	dbo.CUSTOMER.NAME, 
	dbo.RG_PACKAGING.PackagingMethod, 
	dbo.RG_CUST_NOTATION_TOP1.NOTE, 
	dbo.RM_CUSTORD_EXTRA.UB_ROW_NUMBER, 
	dbo.RM_CUSTORD_EXTRA.UB_BENDER_SORT_ORDER

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Andy Marshall
Andy Marshall
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account