asked on

View will not save

I have created a view with the folowing code:

SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT RMALineItems.RMANo AS [RMA #], MiradorClient.Client AS Customer, RMAs.RMANumber AS [Original Invoice #], RMAs.DateAuthorized,
MiradorOrders.ClientPO, (clientsku + ' ' + subpartnum) AS SKU, RMAs.NumberPieces, RMAs.Amount
FROM ((MiradorClient INNER JOIN
((RMALineItems INNER JOIN
RMAs ON RMALineItems.RMANo = RMAs.RMANo) INNER JOIN
Products ON RMALineItems.ProductID = Products.ProductID) ON MiradorClient.ClientID = RMAs.ClientID) INNER JOIN
Invoice ON RMAs.RMANumber = Invoice.InvoiceID) INNER JOIN
MiradorOrders ON Invoice.Morder = MiradorOrders.ClientOrderID
WHERE (((RMAs.DateAuthorized) > Dateadd(mm, - 6, CURRENT_TIMESTAMP)) AND ((RMAs.Complete) = 0))
ORDER BY RMAs.DateAuthorized

When I go to save it, it gives me an error near "SET". I have to turn NULL YIELDS OFF or all of my data comes back NULL in that combined field.

Can someone help?

Thanks,
R

Aneesh

You can't specify SET inside the view
remove that line SET CONCAT_NULL_YIELDS_NULL OFF

robynum

ASKER

I have to use something like it to have the View work properly.

Guy Hengel [angelIII / a3]

use the query analyser to run the CREATE VIEW statement:

SET CONCAT_NULL_YIELDS_NULL OFF;
CREATE VIEW yourviewname
AS
SELECT RMALineItems.RMANo AS [RMA #], MiradorClient.Client AS Customer, RMAs.RMANumber AS [Original Invoice #], RMAs.DateAuthorized,
MiradorOrders.ClientPO, (clientsku + ' ' + subpartnum) AS SKU, RMAs.NumberPieces, RMAs.Amount
FROM ((MiradorClient INNER JOIN
((RMALineItems INNER JOIN
RMAs ON RMALineItems.RMANo = RMAs.RMANo) INNER JOIN
Products ON RMALineItems.ProductID = Products.ProductID) ON MiradorClient.ClientID = RMAs.ClientID) INNER JOIN
Invoice ON RMAs.RMANumber = Invoice.InvoiceID) INNER JOIN
MiradorOrders ON Invoice.Morder = MiradorOrders.ClientOrderID
WHERE (((RMAs.DateAuthorized) > Dateadd(mm, - 6, CURRENT_TIMESTAMP)) AND ((RMAs.Complete) = 0))
ORDER BY RMAs.DateAuthorized

robynum

ASKER

I get the message that:

"Create View" must be the first statement in a Query Batch.

:(

robynum

ASKER

Any other ideas? I am really stumped on this one.

Thanks

Guy Hengel [angelIII / a3]

SET CONCAT_NULL_YIELDS_NULL OFF;
GO
CREATE VIEW yourviewname
AS
SELECT RMALineItems.RMANo AS [RMA #], MiradorClient.Client AS Customer, RMAs.RMANumber AS [Original Invoice #], RMAs.DateAuthorized,
MiradorOrders.ClientPO, (clientsku + ' ' + subpartnum) AS SKU, RMAs.NumberPieces, RMAs.Amount
FROM ((MiradorClient INNER JOIN
((RMALineItems INNER JOIN
RMAs ON RMALineItems.RMANo = RMAs.RMANo) INNER JOIN
Products ON RMALineItems.ProductID = Products.ProductID) ON MiradorClient.ClientID = RMAs.ClientID) INNER JOIN
Invoice ON RMAs.RMANumber = Invoice.InvoiceID) INNER JOIN
MiradorOrders ON Invoice.Morder = MiradorOrders.ClientOrderID
WHERE (((RMAs.DateAuthorized) > Dateadd(mm, - 6, CURRENT_TIMESTAMP)) AND ((RMAs.Complete) = 0))
ORDER BY RMAs.DateAuthorized

robynum

ASKER

OK...when I run this in the Analyzer it does create the view I want, but it drops the: SET CONCAT_NULL_YIELDS_NULL OFF; portion of the view. I have to be able to turn the NULL Yields Off to make the field work properly.

Any ideas?

Thanks,
R

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imran_fast

change your sql database setting to have CONCAT_NULL_YIELD_NULL off instead of doing it for each connection.

ALTER DATABASE YourDatabaseName SET CONCAT_NULL_YIELDS_NULL OFF
GO

robynum

ASKER

Thanks everyone...AngelIII got it set up perfectly. I am still very new at coding SQL, and I really appreciate all of the help.

R