• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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

0
metropia
Asked:
metropia
2 Solutions
 
armchair_scouseCommented:
The left and right joins currently indicated by *= and =* would be replaced with LEFT JOIN or RIGHT JOIN, so:

LEFT JOIN
dbo.CUSTOMER_ORDER.ID*=dbo.CUST_ORDER_BINARY.CUST_ORDER_ID
would become:
dbo.CUSTOMER_ORDER LEFT JOIN dbo.CUST_ORDER_BINARY ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_BINARY.CUST_ORDER_ID

RIGHT JOIN
dbo.RG_PACKAGING.ID=*dbo.CUST_ORDER_LINE.USER_4
would become:
dbo.RG_PACKAGING RIGHT JOIN dbo.CUST_ORDER_LINE ON dbo.RG_PACKAGING.ID = dbo.CUST_ORDER_LINE.USER_4

EQUI/INNER JOIN
dbo.CUSTOMER_ORDER.ID=dbo.CUST_ORDER_LINE.CUST_ORDER_ID
would become:
dbo.CUSTOMER_ORDER INNER JOIN dbo.CUST_ORDER_LINE ON dbo.CUSTOMER_ORDER.ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID

It's something like that, I believe!!  Give it a try with a smaller select query and see how you get on.
0
 
winston33Commented:
FROM clause should look like this:


FROM      dbo.CUSTOMER_ORDER
INNER JOIN       dbo.CUST_ORDER_LINE ON dbo.CUST_ORDER_LINE.CUST_ORDER_ID = dbo.CUSTOMER_ORDER.ID
INNER JOIN      dbo.CUST_ADDRESS ON dbo.CUST_ADDRESS.CUSTOMER_ID = dbo.CUSTOMER_ORDER.CUSTOMER_ID AND dbo.CUST_ADDRESS.ADDR_NO=dbo.CUSTOMER_ORDER.SHIP_TO_ADDR_NO
LEFT OUTER JOIN dbo.CUST_ORDER_BINARY ON dbo.CUST_ORDER_BINARY.CUST_ORDER_ID = dbo.CUSTOMER_ORDER.ID
LEFT OUTER JOIN dbo.RM_CUSTORD_EXTRA ON dbo.RM_CUSTORD_EXTRA.CUST_ORDER_ID = dbo.CUST_ORDER_LINE.CUST_ORDER_ID
      AND dbo.RM_CUSTORD_EXTRA.CUST_ORDER_LINE_NO = dbo.CUST_ORDER_LINE.LINE_NO
RIGHT OUTER JOIN dbo.RG_PACKAGING ON dbo.RG_PACKAGING.ID = dbo.CUST_ORDER_LINE.USER_4
RIGHT OUTER JOIN dbo.RG_CUST_NOTATION_TOP1 ON dbo.RG_CUST_NOTATION_TOP1.OWNER_ID = dbo.CUSTOMER.ID
INNER JOIN      dbo.CUSTOMER ON dbo.CUSTOMER.ID = dbo.CUST_ADDRESS.CUSTOMER_ID
INNER JOIN       dbo.PART ON dbo.PART.ID = dbo.CUST_ORDER_LINE.PART_ID
WHERE      dbo.CUSTOMER_ORDER.ID = 'C251168'
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now