?
Solved

rewriting sql query -

Posted on 2012-03-14
2
Medium Priority
?
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 1200 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 800 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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