Solved

rewriting sql query -

Posted on 2012-03-14
2
190 Views
Last Modified: 2012-06-27
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
Comment
Question by:metropia
2 Comments
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 300 total points
ID: 37721253
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
 
LVL 2

Assisted Solution

by:winston33
winston33 earned 200 total points
ID: 37721462
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now