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

x
?
Solved

How to define column width in a view

Posted on 2011-10-10
6
Medium Priority
?
183 Views
Last Modified: 2012-06-22
Hi,

I am calculating a realtime SLA for a processing system. I am calculating this in a view. My problem is that I cannot seem to set the calculated fields length as it it calculated automatically. The calling softare is particular about the length and I cannot change it. It will throw an error if it is not what it is expecting which is numeric 6.2

The SQL is :

CREATE view [SLA_View] as select [SYS_RECORDKEY] AS [SYS_RECORKEY], [SYS_RECORDKEY] as SYS_TABLEKEY,
(((T_9556_SLATimeAllowed - (datediff(hh,(DATEADD(day, 0, DATEDIFF(day, 0, T_9554_SLAStartdate)) + DATEADD(day, 0 - DATEDIFF(day, 0, T_9555_SLAStartTime), T_9555_SLAStartTime)),getdate())))*100)/ T_9556_SLATimeAllowed) AS[T_9553_SLA]
from application

I need to get the date and itme into a single field which is why this statement is so messy. The field i am concerned with is the T_9553_SLA. It end up being numeric 21,6. the fields that are used in the calculation are defined as follows:

T_9554_SLAStartdate DateTime
T_9555_SLAStartTime DateTime
T_9556_SLATimeAllowed Numeric 4,0

Do i need to recast it into the length and type i need?

Regards
0
Comment
Question by:Michael
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:Brian Chan
ID: 36946752
yeap, cast it again will solve you issue.
0
 
LVL 1

Author Comment

by:Michael
ID: 36946984
i cant seem to get the syntax right to cast within the one statement for the view.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36947073
this should do:
CREATE view [SLA_View] as select [SYS_RECORDKEY] AS [SYS_RECORKEY], [SYS_RECORDKEY] as SYS_TABLEKEY,
cast(((T_9556_SLATimeAllowed - (datediff(hh,(DATEADD(day, 0, DATEDIFF(day, 0, T_9554_SLAStartdate)) + DATEADD(day, 0 - DATEDIFF(day, 0, T_9555_SLAStartTime), T_9555_SLAStartTime)),getdate())))*100)/ T_9556_SLATimeAllowed as numeric(6,2) ) AS[T_9553_SLA]

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:Michael
ID: 36948110
Thanks for your help guys. The statemnt works fine. I have one more issue which i fear cannot be solved. My application expects that the sys_tablekey field is defined as primary key. I have reaserched this and I only seem to find that you cannot have primary keys defined in a view. Is there any way to acheive this?
0
 
LVL 1

Author Comment

by:Michael
ID: 36948116
Further more, The sys Recordkey which im using as the sys tablekey field in the view is in fact a primary key in the application table
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36948149
indeed, a "view" cannot have primary key on itself, and there is no way to solve this, I fear...
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question