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
robynumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You can't specify SET inside the view
remove that line SET              CONCAT_NULL_YIELDS_NULL OFF
0
robynumAuthor Commented:
I have to use something like it to have the View work properly.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

robynumAuthor Commented:
I get the message that:

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

:(
0
robynumAuthor Commented:
Any other ideas? I am really stumped on this one.

Thanks
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
robynumAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
running the script as show will make the view being created with the CONCAT_NULL_YIELD_NULL to off.
however, as you have experienced probably, you need to set this setting in the connection that runs the sql statement, which you CANNOT do inside the view.

you should make your view like this, and avoid the setting:

CREATE VIEW yourviewname
AS
                          SELECT     RMALineItems.RMANo AS [RMA #], MiradorClient.Client AS Customer, RMAs.RMANumber AS [Original Invoice #], RMAs.DateAuthorized,
                                                  MiradorOrders.ClientPO, ltrim(coalesce(clientsku, '') + ' ' + coalesce(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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
imran_fastCommented:
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
0
robynumAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.