MSSQL View

• • Reply

  9 years ago

  by Aleks

  Aleks A.

  New York, USA, United States

  Joined 9 years ago

  Greetings,
  

  
  I've created an MSSQL view, but I'm unable to query it; in other words if I try to format the data inside the view, it gives me an error:
  

  
  

  Code:

  
  SELECT     CAST(LOWER(LEFT(LEFT(ac.email, CHARINDEX('@', ac.email, 1)), LEN(LEFT(ac.email, CHARINDEX('@', ac.email, 1))) - 1)) AS varchar(100)) AS UID,
                       ac.password AS UPASSWORD, LEFT(em.firstname, 1) + LOWER(SUBSTRING(em.firstname, 2, LEN(em.firstname))) AS UFIRST, LEFT(em.lastname, 1)
                       + LOWER(SUBSTRING(em.lastname, 2, LEN(em.lastname))) AS ULAST, ac.location AS ULOCATION, ac.email AS UEMAIL,
                       CASE ac.securitylevel WHEN 4096 THEN 'admin' ELSE 'user' END AS ACCESS
  FROM         dbo.access ac INNER JOIN
                       dbo.emp em ON ac.EmplID = em.EMPLID
  WHERE     ac.email IS NOT NULL
  

  
  

  
  Now if I run this command:
  

  
  

  Code:

  SELECT * FROM CLASSUSERS WHERE UID = 'aleks'

  
  

  
  I get an error:
  

  
  Server: Msg 536, Level 16, State 3, Line 1
  Invalid length parameter passed to the substring function.
  

  
  Now is that a limitation of the View?  Any ideas?
  

  
  BTW, CLASSUSERS is the view, and UID aleks exists.
  

  
  Thanks!

  Report abuse