Learn how to a build a cloud-first strategyRegister Now

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

SQL 2000 View Select Case syntax

I have a SQL 2000 database and want to put in an 'if' statement: -

if [MasterAccount] > "" then [MasterAccount] else [Customer]

Can anyone help with the syntax?
0
HKFuey
Asked:
HKFuey
  • 3
  • 3
  • 2
2 Solutions
 
deightonCommented:
SELECT case when [MasterAccount] > '' then [MasterAccount]  ELSE [Customer] END as YourField
FROM YourTable
0
 
Pratima PharandeCommented:
what is datatype of [MasterAccount] ? int or bigint

do you want to check for NUll ?


try this
SELECT case when ISNULL([MasterAccount],0) > 0 then [MasterAccount]  ELSE [Customer] END as YourField
FROM YourTable
0
 
HKFueyAuthor Commented:
Both fields are text, 7 chars. MasterAccount is either some text or padded with spaces.
If MasterAccount is spaces I want to return [Customer]

I tried your suggestions but get "Error in SELECT clause: expression near 'SELECT'.
Unable to parse query text"

I think this is because I'm on SQL 2000
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Pratima PharandeCommented:
try this

SELECT case when [MasterAccount] like '% %' then [Customer] ELSE  [MasterAccount] END as YourField
FROM YourTable
0
 
deightonCommented:
show us your query string which gives

"Error in SELECT clause: expression near 'SELECT'.
Unable to parse query text"


I just tried something like it on SQL2000, and for me it worked
0
 
HKFueyAuthor Commented:
I still get an error, here's simple version of my View: -
SELECT     TOP 100 PERCENT dbo.view_Sales.Customer, SUM(dbo.view_Sales.QtyInvoiced) AS QtyVal
FROM         dbo.view_Sales LEFT OUTER JOIN
                      dbo.ArCustomer ON dbo.view_Sales.Customer = dbo.ArCustomer.Customer LEFT OUTER JOIN
                      dbo.InvMaster ON dbo.view_Sales.StockCode = dbo.InvMaster.StockCode,
                          SELECT     CASE WHEN MasterAccount LIKE '% %' THEN Customer ELSE MasterAccount END AS Cust
                           FROM         dbo.ArCustomer
                           GROUP BY dbo.view_Sales.Customer
0
 
Pratima PharandeCommented:
SELECT     TOP 100 PERCENT dbo.view_Sales.Customer, SUM(dbo.view_Sales.QtyInvoiced) AS QtyVal,
 CASE WHEN MasterAccount LIKE '% %' THEN Customer ELSE MasterAccount END AS Cust
FROM         dbo.view_Sales LEFT OUTER JOIN
                      dbo.ArCustomer ON dbo.view_Sales.Customer = dbo.ArCustomer.Customer LEFT OUTER JOIN
                      dbo.InvMaster ON dbo.view_Sales.StockCode = dbo.InvMaster.StockCode
                         
                           GROUP BY dbo.view_Sales.Customer
0
 
HKFueyAuthor Commented:
This did the job. Thanks for the help (and quick response!): -

CASE WHEN MasterAccount = '' THEN Customer ELSE MasterAccount END AS Cust
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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