[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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
0
robynum
Asked:
robynum
1 Solution
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now