Having Baby, Will Pay $ for solution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Having Baby, Will Pay $ for solution

Author

Topic

mtassara
Starting Member

4 Posts

Posted - 2004-10-26 : 06:00:14

Hi everyone,
I have upsized an old Access 97 convert to Access 2000 to SQL 2000 for a client. They run this one Report that utilizes the following Query: I don't have time to convert the code as my wife is having our first baby.
Please take a look at this and help me convert the code, I will be more than happy to send whomever solves this puzzle a quick $20 via PayPal. Thanks So much.
Marco

SELECT DISTINCTROW tlkpTruck.Owner, IIf(Nz([tlkpTruck].[Owner],"")=Nz([tlkpTrailer].[Owner],""),True,False) AS IsCombo, No AS IsTrailer, (Nz([tlkpTruck].[Owner],"")<>Nz([tlkpTrailer].[Owner],"")) AS IsTruck, tblLoadTag.LoadTagNumber, tblLoadTag.LoadTagDate, tlkpCustomerLot.CustCode, tlkpCustomer.CompanyName, tlkpCustomerLot.CustLotNumber, tlkpCustomerLot.AreaID, tlkpArea.AreaDesc, tlkpCustomerLot.CustLotDate, tlkpCustomerLot.Mileage, tblLoadTagUnit.CmdtyCode, tlkpCommodity.CmdtyDesc, tblLoadTagUnit.Variety, tblLoadTagUnit.Units

FROM ((tlkpEquipment AS tlkpTruck RIGHT JOIN tblLoadTag ON tlkpTruck.VehicleNumber = tblLoadTag.TrailerNumber) LEFT JOIN (tlkpCustomer RIGHT JOIN ((tblLoadTagForemen LEFT JOIN tlkpCustomerLot ON tblLoadTagForemen.CustLotID = tlkpCustomerLot.CustLotID) LEFT JOIN tlkpArea ON tlkpCustomerLot.AreaID = tlkpArea.AreaID) ON tlkpCustomer.CustCode = tlkpCustomerLot.CustCode) ON tblLoadTag.LoadTagNumber = tblLoadTagForemen.LoadTagNumber)
LEFT JOIN ((tlkpEquipment AS tlkpTrailer RIGHT JOIN tblLoadTagUnit ON tlkpTrailer.VehicleNumber = tblLoadTagUnit.TrailerNumber) LEFT JOIN tlkpCommodity ON tblLoadTagUnit.CmdtyCode = tlkpCommodity.CmdtyCode) ON (tblLoadTagForemen.LoadTagNumber = tblLoadTagUnit.LoadTagNumber) AND (tblLoadTagForemen.Foreman = tblLoadTagUnit.Foreman) AND (tblLoadTagForemen.CustLotID = tblLoadTagUnit.CustLotID)

WHERE (((tblLoadTag.LoadTagDate) Between [Forms]![fdlgCustomers]![txtDate] And [Forms]![fdlgCustomers]![txtWeekEnding]))
UNION ALL SELECT DISTINCTROW tlkpTrailer.Owner, No AS IsCombo, Yes AS IsTrailer, No AS IsTruck, tblLoadTag.LoadTagNumber, tblLoadTag.LoadTagDate, tlkpCustomerLot.CustCode, tlkpCustomer.CompanyName, tlkpCustomerLot.CustLotNumber, tlkpCustomerLot.AreaID, tlkpArea.AreaDesc, tlkpCustomerLot.CustLotDate, tlkpCustomerLot.Mileage, tblLoadTagUnit.CmdtyCode, tlkpCommodity.CmdtyDesc, tblLoadTagUnit.Variety, tblLoadTagUnit.Units

FROM ((tlkpEquipment AS tlkpTruck RIGHT JOIN tblLoadTag ON tlkpTruck.VehicleNumber = tblLoadTag.TrailerNumber) LEFT JOIN (tlkpCustomer RIGHT JOIN ((tblLoadTagForemen LEFT JOIN tlkpCustomerLot ON tblLoadTagForemen.CustLotID = tlkpCustomerLot.CustLotID) LEFT JOIN tlkpArea ON tlkpCustomerLot.AreaID = tlkpArea.AreaID) ON tlkpCustomer.CustCode = tlkpCustomerLot.CustCode) ON tblLoadTag.LoadTagNumber = tblLoadTagForemen.LoadTagNumber)
LEFT JOIN ((tlkpEquipment AS tlkpTrailer RIGHT JOIN tblLoadTagUnit ON tlkpTrailer.VehicleNumber = tblLoadTagUnit.TrailerNumber) LEFT JOIN tlkpCommodity ON tblLoadTagUnit.CmdtyCode = tlkpCommodity.CmdtyCode) ON (tblLoadTagForemen.CustLotID = tblLoadTagUnit.CustLotID) AND (tblLoadTagForemen.Foreman = tblLoadTagUnit.Foreman) AND (tblLoadTagForemen.LoadTagNumber = tblLoadTagUnit.LoadTagNumber)
WHERE (((tblLoadTag.LoadTagDate) Between [Forms]![fdlgCustomers]![txtDate] And [Forms]![fdlgCustomers]![txtWeekEnding])) AND (((Nz([tlkpTruck].[Owner],"")<>Nz([tlkpTrailer].[Owner],""))=True));

RESULTS
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'tblLoadTag'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '.'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'tblLoadTag'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'Nz'.
Server: Msg 1038, Level 15, State 1, Line 8
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 8
Cannot use empty object or column names. Use a single space if necessary.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-26 : 06:21:42

of course there is no way to check the results of the query so i can only try to do it with correct syntax.
testing is up to you.


declare @date1 datetime, @date2 datetime
select 	@date1 = '20040512', --[Forms]![fdlgCustomers]![txtDate]
	@date2 = '20041112'  --[Forms]![fdlgCustomers]![txtWeekEnding]
SELECT DISTINCT tlkpTruck.Owner, 
	case when isnull([tlkpTruck].[Owner],'') = isnull([tlkpTrailer].[Owner],'') then 1
	else 0 end as IsCombo,
	--IIf(Nz([tlkpTruck].[Owner],"")=Nz([tlkpTrailer].[Owner],""),True,False) AS IsCombo, 
	No AS IsTrailer, 
	--(Nz([tlkpTruck].[Owner],"")<>Nz([tlkpTrailer].[Owner],"")) AS IsTruck, 
	case when isnull([tlkpTruck].[Owner],'') <> isnull([tlkpTrailer].[Owner],'') then 1
	else 0 end as IsCombo,
	tblLoadTag.LoadTagNumber, tblLoadTag.LoadTagDate, tlkpCustomerLot.CustCode, tlkpCustomer.CompanyName, 
	tlkpCustomerLot.CustLotNumber, tlkpCustomerLot.AreaID, tlkpArea.AreaDesc, tlkpCustomerLot.CustLotDate, 
	tlkpCustomerLot.Mileage, tblLoadTagUnit.CmdtyCode, tlkpCommodity.CmdtyDesc, tblLoadTagUnit.Variety, 
	tblLoadTagUnit.Units
FROM 	((tlkpEquipment AS tlkpTruck 
		RIGHT JOIN tblLoadTag ON tlkpTruck.VehicleNumber = tblLoadTag.TrailerNumber) 
	LEFT JOIN 
		(tlkpCustomer RIGHT JOIN 
			((tblLoadTagForemen 
				LEFT JOIN tlkpCustomerLot ON tblLoadTagForemen.CustLotID = tlkpCustomerLot.CustLotID) 
			LEFT JOIN tlkpArea ON tlkpCustomerLot.AreaID = tlkpArea.AreaID) 
		ON tlkpCustomer.CustCode = tlkpCustomerLot.CustCode) 
	ON tblLoadTag.LoadTagNumber = tblLoadTagForemen.LoadTagNumber) 
	LEFT JOIN ((tlkpEquipment AS tlkpTrailer RIGHT JOIN tblLoadTagUnit ON tlkpTrailer.VehicleNumber = tblLoadTagUnit.TrailerNumber) LEFT JOIN tlkpCommodity ON tblLoadTagUnit.CmdtyCode = tlkpCommodity.CmdtyCode) ON (tblLoadTagForemen.LoadTagNumber = tblLoadTagUnit.LoadTagNumber) AND (tblLoadTagForemen.Foreman = tblLoadTagUnit.Foreman) AND (tblLoadTagForemen.CustLotID = tblLoadTagUnit.CustLotID)
WHERE 	tblLoadTag.LoadTagDate Between @date1 And @date2
UNION ALL 
SELECT 	DISTINCT tlkpTrailer.Owner, No AS IsCombo, Yes AS IsTrailer, No AS IsTruck, 
	tblLoadTag.LoadTagNumber, tblLoadTag.LoadTagDate, tlkpCustomerLot.CustCode, tlkpCustomer.CompanyName, 
	tlkpCustomerLot.CustLotNumber, tlkpCustomerLot.AreaID, tlkpArea.AreaDesc, tlkpCustomerLot.CustLotDate, 
	tlkpCustomerLot.Mileage, tblLoadTagUnit.CmdtyCode, tlkpCommodity.CmdtyDesc, tblLoadTagUnit.Variety, 
	tblLoadTagUnit.Units
FROM 	((tlkpEquipment AS tlkpTruck RIGHT JOIN tblLoadTag ON tlkpTruck.VehicleNumber = tblLoadTag.TrailerNumber) LEFT JOIN (tlkpCustomer RIGHT JOIN ((tblLoadTagForemen LEFT JOIN tlkpCustomerLot ON tblLoadTagForemen.CustLotID = tlkpCustomerLot.CustLotID) LEFT JOIN tlkpArea ON tlkpCustomerLot.AreaID = tlkpArea.AreaID) ON tlkpCustomer.CustCode = tlkpCustomerLot.CustCode) ON tblLoadTag.LoadTagNumber = tblLoadTagForemen.LoadTagNumber) 
	LEFT JOIN ((tlkpEquipment AS tlkpTrailer RIGHT JOIN tblLoadTagUnit ON tlkpTrailer.VehicleNumber = tblLoadTagUnit.TrailerNumber) LEFT JOIN tlkpCommodity ON tblLoadTagUnit.CmdtyCode = tlkpCommodity.CmdtyCode) ON (tblLoadTagForemen.CustLotID = tblLoadTagUnit.CustLotID) AND (tblLoadTagForemen.Foreman = tblLoadTagUnit.Foreman) AND (tblLoadTagForemen.LoadTagNumber = tblLoadTagUnit.LoadTagNumber)
WHERE 	(tblLoadTag.LoadTagDate Between @date1 And @date2)
	AND isnull([tlkpTruck].[Owner],'') <> isnull([tlkpTrailer].[Owner],'')

CONGRATS ON THE BABY!!!

Go with the flow & have fun! Else fight the flow

mtassara
Starting Member

4 Posts

Posted - 2004-10-26 : 12:15:32

Spirit1 thank you,
Query Analyzer returned only one error:

Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'No'.

mtassara
Starting Member

4 Posts

Posted - 2004-10-26 : 21:11:22

Spirit1, When I replaced the Union Query with your code I get:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (Error 3129)
Your SQL statement could not be recognized because it does not begin with one of the specified reserved words.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-27 : 02:03:17

quote:
Originally posted by mtassara

Spirit1 thank you,
Query Analyzer returned only one error:

Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'No'.

No is a reserved word, enclose it, [No]

from what i partially read there are some redundancy, can you post the ddl of the tables and sample result?

congrats on the baby, is it girl or boy?

--------------------
keeping it simple...

mtassara
Starting Member

4 Posts

Posted - 2004-10-29 : 03:53:28

Hi Jen and Spirit1,
Thank you so much for your thoughtfulness and help.
I stayed up late the last few nights to resolve this problem and I have found what seems to be the solution for now. During the Upsizing some of the columns in one very important table were blank as well the relationships of a few tables were not enforced correctly. This was the actual problem. Although the code be cleaned up its beyond the scope of this project.
The query run from Access is not compliant with the SQL code you gave me. Access 2000 (which is what that corporation is running) has many discrepancy's. When I tested directly in SQL it worked like a charm.
Spirit1 I owe you $20. Please send me an email with how or where you want that sent. Thank you, Marco

This is my first child and although I thought I was going to be a dad a few nights ago I didn't know you can actually go through a few days of pretty hard labor.

I praise all women now and thank them for dealing with all this to have children. My wife is finally sleeping now but still has contractions all the time...but i guess not frequent enough! Soon I will be having a son and I will write back when he is born and post a pic.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-29 : 03:57:28

marco, glad you made it. i thought you knew that access and sql server sql statements are not 100% compliant,
otherwise i'd mention that

and i think you'll need money more than i do with the baby and all.
and besides paying for such a "small" thing would be besides this forum's point.
so enjoy your baby and give your wife a lot of attention.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources