troubleshooting Question

rewriting sql query -

Avatar of metropia
metropiaFlag for United States of America asked on
Microsoft SQL ServerSQL
2 Comments2 Solutions262 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros