December 16, 2010 at 9:29 am
Current Query:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
dbo.Transactions ON dbo.Asset.AssetID = dbo.Transactions.AssetID
WHERE (dbo.Transactions.ReturnDate IS NULL)
Want to change it so it returns ALL Asset rows, regardless, and only returns Transaction rows where the ReturnDate is NULL. I would think it would be coded:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
(Select * from dbo.Transactions WHERE dbo.Transactions.ReturnDate IS NULL)
ON dbo.Asset.AssetID = dbo.Transactions.AssetID
...but that doesn't pass the syntax checker. Help?
Jim
December 16, 2010 at 2:27 pm
Can you just remove the where clause to get all rows???
To look at your error:
Your subquery does not have an alias. You also should not use select *
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2011 at 3:53 am
JimS-Indy (12/16/2010)
Current Query:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
dbo.Transactions ON dbo.Asset.AssetID = dbo.Transactions.AssetID
WHERE (dbo.Transactions.ReturnDate IS NULL)
Want to change it so it returns ALL Asset rows, regardless, and only returns Transaction rows where the ReturnDate is NULL. I would think it would be coded:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
(Select * from dbo.Transactions WHERE dbo.Transactions.ReturnDate IS NULL)
ON dbo.Asset.AssetID = dbo.Transactions.AssetID
...but that doesn't pass the syntax checker. Help?
I think you have forgotten giving alias to
...........
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN (Select * from dbo.Transactions WHERE dbo.Transactions.ReturnDate IS NULL) Transactions ON dbo.Asset.AssetID = dbo.Transactions.AssetID
--Divya
January 6, 2011 at 2:52 am
Thank you, Divya. You're right!
Jim
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply