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 2005 Forums
 Transact-SQL (2005)
 [Resolved] Select, join and sub select - need help

Author  Topic 

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-06-01 : 08:57:49
I need to do the select based upon the content (equipmentID) in #TempTable1 and bring the result into #TempTable2. I am not sure how to do the join. Can anyone help?

CREATE TABLE #TempTable1 (equipmentID1 nvarchar(20))


Fill #TempTable1 here .......

Now use records in #TempTable1 as basis for my select..... Tried below code, but gives me error: Incorrect syntax near the keyword 'on'.


INSERT INTO #TempTable2
SELECT      equipmentID1 
FROM        #TempTable1
	        INNER JOIN
                  (SELECT         
                       substring(dbo.Job.CompanyJobId, 1, 10) as job,
                       substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
                       dbo.Batch.ReportDate as jobdate,
                       dbo.Equipment.CompanyEquipmentID as equipmentID,
                       substring(dbo.Equipment.Name, 1, 20) as equipmentname,
                       dbo.EquipmentLaborEvent.Hours as hours, 
                       substring(dbo.EventStatusType.Name, 1, 1) as event_code,  
                       dbo.EventStatusType.Name as event_name,  
                       substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                  
                       FROM dbo.Equipment)

                 on dbo.Equipment.CompanyEquipmentID = equipmentID1
		 inner join EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                 inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid                  
                 inner join dbo.Event on dbo.Event.EventGuid = EquipmentLaborEvent.EventGuid
                 inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                 inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
                 inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
                 inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
                 inner join dbo.Equipment e3 on dbo.Equipment.CompanyEquipmentID = #TempTable1.equipmentID

WHERE            dbo.Region.CompanyRegionID = '00060' and
                 dbo.Job.CompanyJobID = @JobNumber and
                 dbo.Batch.Reportdate >=  '2009-05-14' and
                 dbo.Batch.ReportDate <= '2009-05-29' and
	         dbo.EquipmentLaborEvent.Hours > 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-01 : 10:27:52
Does #TempTable2 already exist?

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-06-01 : 10:34:46
I am creating the #TempTable2 (like I do with #TempTable1) just I did not show the code:

CREATE TABLE #TempTable2 (job nvarchar(10), costcode nvarchar(10), jobdate DateTime,
                                                  equipmentID nvarchar(20), equipmentname nvarchar(20), 
                                                hours decimal(10,2), event_code nvarchar(1), event_name nvarchar(50), 
                                              foreman nvarchar(10), approver nvarchar(10))
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-01 : 10:59:51
[code]FROM dbo.Equipment) AS x

on x.EquipmentID = #temptable1.equipmentID1[/code]
E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-06-01 : 11:45:31
Using:

FROM dbo.Equipment) as x

                 on x.EquipmentID = #temptable1.equipmentID1


Getting errors:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.Job' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Item' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Batch' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EquipmentLaborEvent' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EventStatusType' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.EventStatusType' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Batch' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Batch' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'e2' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Equipment' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Equipment' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Equipment' does not match with a table name or alias name used in the query.

I also tried this:

INSERT INTO #TempTable2
SELECT      *  
FROM        #TempTable1
	        INNER JOIN
                  (SELECT         
                       substring(dbo.Job.CompanyJobId, 1, 10) as job,
                       substring(dbo.Item.CompanyItemId, 1, 10) as costcode,
                       dbo.Batch.ReportDate as jobdate,
                       dbo.Equipment.CompanyEquipmentID as equipmentID,
                       substring(dbo.Equipment.Name, 1, 20) as equipmentname,
                       dbo.EquipmentLaborEvent.Hours as hours, 
                       substring(dbo.EventStatusType.Name, 1, 1) as event_code,  
                       dbo.EventStatusType.Name as event_name,  
                       substring (dbo.Batch.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                  FROM dbo.Equipment 

                
		 inner join EquipmentLaborEvent on dbo.EquipmentLaborEvent.EquipmentGuid = dbo.Equipment.EquipmentGuid
                 inner join dbo.Region on dbo.Region.RegionGuid = Equipment.RegionGuid                  
                 inner join dbo.Event on dbo.Event.EventGuid = EquipmentLaborEvent.EventGuid
                 inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
                 inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
                 inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
                 inner join dbo.Batch on dbo.Batch.BatchGuid = dbo.Event.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = dbo.Event.ApprovedByAccountGuid
                 inner join dbo.Equipment e3 on dbo.Equipment.CompanyEquipmentID = #TempTable1.equipmentID


WHERE            dbo.Region.CompanyRegionID = '00060' and
                 dbo.Job.CompanyJobID = @JobNumber and
                 dbo.Batch.Reportdate >=  '2009-05-14' and
                 dbo.Batch.ReportDate <= '2009-05-29' and
	         dbo.EquipmentLaborEvent.Hours > 0 ) as x

                 on x.EquipmentID = #Temptable1.equipmentID1


Error: The column prefix '#TempTable1' does not match with a table name or alias name used in the query.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-01 : 13:37:43
try to use short aliases for tables

INSERT INTO #TempTable2
SELECT      *  
FROM        #TempTable1 t1
	        CROSS APPLY
                  (SELECT         
                       substring(j.CompanyJobId, 1, 10) as job,
                       substring(i.CompanyItemId, 1, 10) as costcode,
                       b.ReportDate as jobdate,
                       e.CompanyEquipmentID as equipmentID,
                       substring(e.Name, 1, 20) as equipmentname,
                       ele.Hours as hours, 
                       substring(est.Name, 1, 1) as event_code,  
                       est.Name as event_name,  
                       substring (b.Name, 1, charindex('-',dbo.Batch.Name)-1) as foreman,
                       substring (e2.UserName, 1, 10) as approver
                  FROM dbo.Equipment e

                
		 inner join EquipmentLaborEvent ele on ele.EquipmentGuid = e.EquipmentGuid
                 inner join dbo.Region r on r.RegionGuid = e.RegionGuid                  
                 inner join dbo.Event ev on ev.EventGuid = ele.EventGuid
                 inner join dbo.EventStatusType est on est.EventStatusTypeGuid = ev.EventStatus
                 inner join dbo.Job j on j.JobGuid = ev.JobGuid
                 inner join dbo.Item i on i.ItemGuid = ev.ItemGuid
                 inner join dbo.Batch b on b.BatchGuid = ev.BatchGuid
                 left join dbo.Account e2 on e2.AccountGuid = ev.ApprovedByAccountGuid
                 inner join dbo.Equipment e3 on e3.CompanyEquipmentID = t1.equipmentID


WHERE            r.CompanyRegionID = '00060' and
                 j.CompanyJobID = @JobNumber and
                 b.Reportdate >=  '2009-05-14' and
                 b.ReportDate <= '2009-05-29' and
	         ele.Hours > 0 
                 and  e.CompanyEquipmentID = t1.equipmentID1) as x
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2009-06-01 : 14:35:12
Got it working. Thank you very very much.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-02 : 13:21:22
welcome
Go to Top of Page

shijobaby
Starting Member

44 Posts
Posted - 2009-08-20 : 02:58:27
Hi

I also got this error message when running my Acc Web service

Nw i got solved


http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site