Index In Calculated Field From A Ntext Field

Nov 8, 2005

For some reasons I have to use a ntext field for both small strings like "10" and large binalry files.

I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc.

To trick the sorting I use a calculated field:

ORDER BY RSort - where Rsort is:

convert(varchar(4), RD.response) as RSort

It works but put a high load on the SQL server when the number of responses increases.

I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended.

What do I do. The last thing would be to change the ntext to vchar(3800) or something like that. :confused:

View 3 Replies


ADVERTISEMENT

SQL 2012 :: Index On Calculated Field With UDF?

May 11, 2015

is it possible to have a field that is calculated using a User Defined Function, and index that field?

View 9 Replies View Related

Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.

Jul 11, 2005

I'm using DTS to import data from an Access memo field into a SQL Server ntext field.  DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View 4 Replies View Related

NText Field In SQL??

Jan 15, 2004

Hello All,

Maybe a stupid question but I'm new to the db admin work so please bear with me.

I've imported an Access db into SQL, in the Access db the field type was 'memo' to accomodate the large amount of text (on avg ruffly 4100 chars. with spaces). Now in SQL the field in the table I have set up as an ntext field, which I understood to be equivalent to a memo field in Access.

My problem is when saving data to the field the first time it saves all the data correctly with the exception of the field in question. The data in the field is '<LongText>', now when I try to update the data in the table I get a 'Data Truncated' error message and no update takes place throughout the table.

After testing this and trying different things, I've found that if I shorten this one field and try to save to the db I still get the 'Data Truncated' error message. If I shorten the data in the field AND delete the record from the SQL table then it will save just fine from there on out (which won't work for the reports).

I'm not sure what I'm missing here to get this to work the way it did in Access.

View 11 Replies View Related

NText Update Field

Aug 17, 2004

Hi All,

I will be doing stress test for my app.

Loading thousands of records to the DB through bulk insert.
There's one field NText which I have left NULL because it will be hard to gen dummy flat file to it.

I have another table which has the Ntext Value which i will want to copy and duplicate to the other table.

what is the way to do it?

simply said i want to update a record with NULL value from one table with NText field with the value from another table..

View 1 Replies View Related

Ntext Field From Profiler

Jul 20, 2005

I am trying to view all the ntext from a profiler trace. The data istruncated at 256 and I am not sure why... The max length is 1820 viathis command:select max(datalength(textdata)) from "monitor forms usage"where textdata like '%gforms%' .I then issueset textsize 8000select (textdata) from "monitor forms usage" where textdata like'%gforms%' and datalength(textdata) >1800and still only 256 is returned. this is true even if I redirect theoutput to a file.Any ideas on how a humble man like me can see all of the data.Mike--Posted via http://dbforums.com

View 2 Replies View Related

Parsing An Ntext Field

Jan 29, 2008



I'm trying to parse an ntext field that in my SQL View contains an invoice comment in order to be able to group on parts of the comment. I have two problems--one, the syntax to do this, and two, the best way to deal with the parts that I want.

The comment is like: "standard text ABCDE : $99.99" but can have multiple "ABCDE"s, e.g. "standard text ABCDE FGH IJKL $999.99" and I found some that had duplicates like "standard text standard text...".
I want to be able to report in SSRS 2005 by grouping the "ABCDE", "FGH", "IJKL" items.

Any ideas? Please be specific as I'm still learning.

View 1 Replies View Related

Calculated Field

May 14, 2008

 HiI am using Management Studio with SQL Server 2005 Express. I am trying to use the Calculated Column Specification by entering a formula.Every attempt results in the same error 'Error Validating the formula'Lets say I have 3 columns a,b, and cI wish to put a formula into c so that it becomes a/bCan anyone either help me with the syntax or point me a resource. I have googled without success. There seems to little or nothing out there on this topic.Thanks,Bill 

View 5 Replies View Related

Calculated Field

Jul 25, 2005

I am a newbie to SQL Server using SQL Server 2000.

I am trying to use a calculated field in a table, what I want is for the result to be shown as an integer (int)?
However the fields that I base the calculation on are of the type (money), and the calculted field although giving me the correct result makes the field of type (money).

The fields that I am basing the calculation on are:-

ColumnName Type Size Allow Nulls
PurchasePrice money 8 0
LoanSize money 8 0



this is the code for the calculated field


([loansize] / [PurchasePrice] * 100)


and this is the description of the calculated field that is forced in the designer



ColumnName Type Size Allow Nulls
LTV money 8 1



and the designer does not allow me to change the type.

I would be grateful of any pointers

Regards

Tony

View 1 Replies View Related

IF In A Calculated Field

Nov 8, 2014

How is the below formula written in a calculated field in SQL?

If TransactionType = 3 then
10% * ValueTransaction else
1.5% * ValueTransaction
end if

View 1 Replies View Related

Calculated Field

Apr 11, 2006

I have the following fields in table A:

GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI
===================================================
1000|liability | -10,000.00 | 08 | 2005
===================================================
1001| asset | 20,000.00 | 08 | 2005
===================================================
1000|liability | -9,000.00 | 09 | 2005

the fields above have the following datatype:

Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger


The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Description Amount
asset 20,000.00
liability (10,000.00)
===========
Net Asset 10,000.00
===========

The above report would list 2 columns as Description & Amount, next it would sort the Description
column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability.

Guys, hope someone out there can help me with the sql command for the above report?

View 3 Replies View Related

Calculated Field (GPA)

Sep 26, 2007

I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO.

here is a sample table:








Term

ID

LastName

FirstName

CRN

Subject

LetterGrade

Calc GPA


20072

1

Doe

John

1234

CHEM

B




20072

1

Doe

John

3214

BIO

A




20072

1

Doe

John

4321

LAW

B




20072

2

Bauer

Jack

1234

CHEM

A




20072

2

Bauer

Jack

3214

BIO

C




20072

2

Bauer

Jack

5467

FIN

B




A = 4.0
B = 3.0
C = 2.0
D = 1.0

View 10 Replies View Related

Calculated Field

Apr 20, 2007



This may be an extremely simple question, but I am trying to combine two text fields (last name, comma, space and first name) into a new field that can be used as a GROUP in my report.



What is the simplest way to accomplish this?

View 1 Replies View Related

How 2 Change Collation On Ntext Field

Jul 22, 2002

The title says it all. I've used ALTER DATABASE, and ALTER TABLE...ALTER COLUMN to change all my character fields from 'Compatibility_42_409_30003' to the default I want ('Latin1_General_CI_AI') on databases converted from SQL7. Now I just have these ntext fields to change...

Al

View 1 Replies View Related

How To Append Data To Ntext Field?

Feb 27, 2006

Hi,
Can anybody tell me how to append data to ntext field?
Joydeep

View 9 Replies View Related

How To Replace Char In Ntext Field

Jun 16, 2006

I need help on replace char in ntext data type

Here is the example data
<qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off
20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree
</qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh

I want result look like this
First Column:The AE Understands what conditions the Account Manager is allowed to sign-off.
Second Column: Strongly Disagree Disagree Agree Strongly Agree

This is what i had so far
Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion

my result:
The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.

I have problem with replace '%20' and how to make the second column.
Any Help?
Thanks
Shan

View 1 Replies View Related

Field Too Small; Cannot Use Text Or NText; Ugh, Little Help.

Sep 9, 2006

My Stored Proc runs through a loop and concats the contents of each field into one big nvarchar. Procedure works fine on a smaller scale but now it is being implemented on a very large table and the results of the sequel overflow the nvarchar limits. I looked into using text and ntext but both cannot be declared locally. Does anyone know how I can work aroudn this limitation?

Summary:The problem is that the temporary variable I am using (nvarchar) is too small to contain the robust size that the SQL is concating into it. The final field it winds up in is a text field and will be able to handle the amount of data, its just getting the data there is the issue..... Your thoughts please....

View 7 Replies View Related

Calculated Field In SQL 2000

Sep 12, 2007

I need a calculated field C with several CASES. If (field A is 'daily' or 'half day' or 'hourly') and (field B is NULL) then C= D-50 If (field A is hourly and field B is NULL then C= 850I don't know sql server 2000 well enough to create this query.thanksMilton

View 6 Replies View Related

Calculated Field In SQL Server 6.5

Mar 7, 2001

I am used to working in MS Access where you can return a value as in:
[date1]-[date2]=X

It will calculate that value provided "date1" and "date2" are fields in the recordset. One calc for each record.
I am getting an error message in SQL Server saying that neither "date1" nor "date2" are not contained in an aggregate function and there is no "group by" clause.
In Access this would not be a problem.
Can you help?
Thank you.

View 1 Replies View Related

Use Calculated Field In Same Query

Jun 25, 2004

Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example:

SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total

Obviously that doesn't work, but what could I do to get that basic thing to work?

Thanks!

View 4 Replies View Related

Calculated Field And Conditions

Nov 3, 2014

I have a Vehicle/Driver Inspection Database and I'm trying to create a Traffic Violation Point System, its a system when drivers commit certain traffic offences they will incur driving-offence points.

System: The points for violations that all occurred within the last 12 months of ONE ANOTHER are added together to calculate point total. If the accumulated points is reached 14 points driver is suspended the points will be REMOVED or minus 14 points after the suspension has been served.

Example:

Date of Offence - Number of Points
Aug. 6, 2013 - 6
Feb. 4, 2014 - 4
Apr. 25, 2014 - 4
- 2
May 8, 2014 - 2

Explanation:

On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4) and suspension is carried out then 14 points is removed. The 2nd offence on Apr. 25, 2014 will be carried forward and added to May 8, 2014 points (total 4 points).

Below are DB Tables:

tbl_Driver
DriverID DriverLicense FirstName LastName

tbl_Event
EventID EventTIme DriverID CarID

tbl_EventViolation
EventViolatinID EventID ViolationID Status (1 = no, 2 = yes radio buttons)

tbl_Violation
ViolationID ViolationName Points

tbl_ViolationClass
ViolationClassID ClassName

I made an SQL Code but to sum the accumulated points only but not the whole condition. See below:

SELECTa.DriverId,
a.DriverLicense,
a.FirstName + ' ' + a.LastName as DriverName,
a.FirstName,
a.LastName,
a.DriverMobileNo1,

[Code] .....

View 2 Replies View Related

Using Calculated Field In Where Clause

Jul 23, 2007

Dear All;

i want to use a calculated field in where clause but this does'nt works: e.g.;

select (colA + colB) as calcField
from tbl
where calcField > 100

what is the right way to do it, as i m getting error;
it works like :
select (colA + colB) as calcField
from tbl
where (colA + colB) > 100

but i m using a complex query as i m getting value from a function and then i hav to use that in where clause as well ;


Thanx in advance,


--
Muhammad Zeeshan

View 1 Replies View Related

AttribDescription As Calculated Field

Jul 23, 2005

Hiin T-SQL,(how) is it possible to concatenate 3 (varchar) fields into one; eitherin a SQL query or through a calculated field (or using a view, ifanybody can explain to me how to use views), according to the followingrules:{first 30 chars of Trim(AttributeVal1)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal2)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal3)}=> define as new field StockItemDescriptionideally I would like SQL Server to do this processing rather thanbuilding all these answer strings on the client side.tiaAxel

View 5 Replies View Related

How To Generate Calculated Field

Jul 20, 2005

I need to run a data report that will query an Access_Table that has thefollowing Project info:My Form has a command_button with the following code:Private Sub Command1_Click()Set db = New Connectiondb.CursorLocation = adUseClientdb.Open "PROVIDER=MSDataShape;DataPROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &"Access_Table.mdb;"Set adoAge = New RecordsetadoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date,IIf([Invoice_Date]<Now()-30 And[Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30,IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROMCust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimisticSet DataReport1.DataSource = adoAgeDataReport1.ShowEnd SubMy DataReport1 has the following RptTextbox:RptTextbox Datafield1 CustomerName2 OrderNo3 Invoice_Date4 Greaterthan305 Greaterthan60Problem arise when trying to display the above report that has an errormessage that says "Datafield Greaterthan30 not found"Is this an SQL Query limitation where Invoice_Date Datafield can only bequeried once & if we have more than one Datafieldwith the same fieldname (Invoice_Date) within the same Data report,thereport will fail to run? Should there be one moreNew Recordset to hold a second Datafield (Invoice_Date)?can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Sort A Calculated Field

Oct 10, 2007



I have a quick question that i have just a simple calc field.

Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.

View 1 Replies View Related

Calculated Field Issue

Jan 22, 2008



Hi I am having a problem with something that was relatively simple in Crystal and i am guessing it is purely my inexperience that is the issue here!

Calculated Field


=iif(Fields!Fun_Debt_Status.Value <> "debt","",

iif(max(Fields!Fun_Days_Overdue.Value)>30,"On Stop",

iif(max(Fields!Fun_Days_Overdue.Value)>0,"Overdue",

iif(sum(Fields!Fun_OS_Amount.Value)> Fields!CREDIT_LIMIT.Value,"Over Credit Limit","ok"))))

Want i am trying to do as you can probably see is create a "Account Status" field depending upon overdue days for payment etc..

My report just produces an error (internal) when i try and run - the calculated field above isn't even on the report.

Please let me know what i am doing wrong! I maybe going at it in completely the wrong way altogether,

Thanks in Advance!

View 5 Replies View Related

My Problem With Calculated Field

Nov 11, 2006

I have a table for tracking the movement of cash register it has the fields

ID
Date
Amount_Debit
Amount_Credit
Explanation

I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I

used this but it gave me an error

sum(amount_debit-amount_credit) where id<=id

the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show

the balance of it

can any one help me to get the desired result in the best way possible

additional question if possible can I get correct cash balance if I drop the ID field depending on date


I use SQL Server 2005 Express

View 3 Replies View Related

Calculated Field In A Csv File

Nov 13, 2007



I have a fairly simple SQL code that includes a calculated field (date difference) in Oracle. I am bring the data in a csv format, and its giving me nothing for that calculation. I checked the results in TOAD and they look fine. I know, i am missing something for the format but have tried everything.
I am new to this so any help is appreciated.

Thanks


select rpt.emc_last_run_date "Report Run Date",

(rpt.EMC_ACCEPT_DATE_TIME - rpt.EMC_QUEUED_DATE_TIME * 24 * 60) "Time To Accept(mins)"

View 3 Replies View Related

Replace A String In An NTEXT Field In Sql Server

Oct 15, 2006

I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.

View 8 Replies View Related

Problem In Displaying NTEXT Field From Database?

Jun 20, 2007

Hello,
 I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.
 I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.
any ideas really appriciated.
 Thanks
Ram

View 4 Replies View Related

Ntext Field Limiting Input To 64,999 Characters

May 24, 2002

I am having an interesting error. I have a bunch of data that I am updating to a field in my sql server 7.0 database. It updates the data, however only puts the first 64,999 characters into the field. The datatype is ntext. From what I understand, ntext datatype can hold much more than 65,000 characters. Am I right in this? The data I'm putting into the field is html tags/text. I've tried several different data sets. I'm updating the data using Coldfusion/SQL commands.

Here is my SQL syntax:

UPDATE htmltest
SET html_offline = '#form.html#'
WHERE htmlid = #form.htmlid#

View 1 Replies View Related

STORED PROC To UPDATE Ntext Field -- PLEASE!

Jul 20, 2005

Hi, I've been reading all sorts of info on the ntext field. I needthis to store xml documents in sql server via a stored proc.Because of its size, I apparently can not use SET (as in UPDATE)therefore I'm trying to do an INSERT of the row with this field (afterdeleting the old row).CREATE PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50)@strWF ntext@varCust varchar(50)@varAssy varchar(50))ASINSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES(@varWO, @varCust, @varAssy, @strWF)I'm using MSDE so I can't tell what's wrong...it just won't save theproc.PLEASE HELP!Thanks, Kathy

View 2 Replies View Related

Copy Ntext Field In Stored Procedure

Oct 24, 2007

Hello everybody,

i have a problem with copying (read it from one row and set it to another) a ntext field in a stored procedure. I know how
to get a pointer to the data and how to read from it.

But i want to copy the whole data.

Does anybody know how to do that?

Thanks in advance

A.Gempp


-------------------------------
I'm using SQL Server 2000

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved