Adding an Alias column with standard information 1

[Cpreston]

Hi

I have a view that I am using to export information. Some of the information we do not hold in any tables and the result would always be the same.

So we have Delivery or Collection, I always want it to be D.
We also have Breakable and the result will always be 0

How do I add an alias in a view that it will always populate the result set with D and 0 in the respective alias columns. This is the code form SQL as the view looks now.

Thanks

SELECT dbo.Customer.CustomerCode, dbo.Customer.Name, dbo.CustomerAddress.Address1, dbo.CustomerAddress.Address2, dbo.CustomerAddress.Address3,
dbo.CustomerAddress.City, dbo.CustomerAddress.County, dbo.JourneySODetail.DeliveryPostCode, dbo.DeliveryArea.Name AS [Cust.text03],
dbo.Customer.CustomerCode AS [Call.ID], dbo.JourneySODetail.TotalVolume, dbo.JourneySODetail.TotalWeight, dbrderHeader.DateRequired,
dbrderHeader.CustomerRef, dbrderHeader.HeaderNotes
FROM dbrderHeader INNER JOIN
dbo.CustomerAddress ON dbrderHeader.DeliveryAddressID = dbo.CustomerAddress.CustomerAddressID INNER JOIN
dbo.JourneyHeader ON dbrderHeader.JourneyID = dbo.JourneyHeader.JourneyID INNER JOIN
dbo.Customer ON dbrderHeader.CustomerID = dbo.Customer.CustomerID INNER JOIN
dbo.DeliveryArea ON dbrderHeader.DeliveryAreaID = dbo.DeliveryArea.DeliveryAreaID INNER JOIN
dbo.JourneySODetail ON dbrderHeader.OrderID = dbo.JourneySODetail.OrderID

 

[johnherman]

How about just hard-coding it as a constant in the view?

SELECT dbo.Customer.CustomerCode, dbo.Customer.Name, dbo.CustomerAddress.Address1, dbo.CustomerAddress.Address2, dbo.CustomerAddress.Address3,
dbo.CustomerAddress.City, dbo.CustomerAddress.County, dbo.JourneySODetail.DeliveryPostCode, dbo.DeliveryArea.Name AS [Cust.text03],
dbo.Customer.CustomerCode AS [Call.ID], dbo.JourneySODetail.TotalVolume, dbo.JourneySODetail.TotalWeight, dbrderHeader.DateRequired,
dbrderHeader.CustomerRef, dbrderHeader.HeaderNotes, 'D' AS DELIVERY_OR_COLLECTION, 0 as BREAKABLE
FROM dbrderHeader INNER JOIN
dbo.CustomerAddress ON dbrderHeader.DeliveryAddressID = dbo.CustomerAddress.CustomerAddressID INNER JOIN
dbo.JourneyHeader ON dbrderHeader.JourneyID = dbo.JourneyHeader.JourneyID INNER JOIN
etc, etc.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[Cpreston]

Hi

yes that works fine , thanks for the reply,

 

[dhookom]

I never trust statements like "always want it to be D" and "the result will always be 0". I would create a small table with these values and include it in the SQL so that WHEN the values change, you can do it in data and not in hard-coded expressions.


Duane
Hook'D on Access
MS Access MVP