Hi expert, I would like to ask regarding the UPPER function in SQL Query. I was tryin' to create a scipt that will give me a result of all the names that are in UPPER case format, but when I tried to execute the script the result is not right, it also retrieves all the records that are in PROPER case.
SQL Script:
SELECT id, name FROM table_1 WHERE UPPER(name) LIKE 'DAR%'
Result:
ID NAME
-- -------
1 Darren
2 DARREN
Why aren't we getting proper MAX(DOJ) value in Weird_Maxgroup Column in the below query ? Similar thing happens with MIN() function as well.
Is there something that I am missing here ?
;WITH CTE AS
(
SELECT 1 AS ID, 10000 AS SALARY,GETDATE() AS DOJ
UNION ALL
SELECT 1, 13000,GETDATE() - 10
UNION ALL
SELECT 3, 190000,GETDATE() + 10
[Code] ....
Hi
Is there a way that I can sum the value of a field in a text box and use where clause with it..
For Example
I want
=Sum(Fields!TO_DATE_AMT_PL.Value) where date<=20040509
What is the syntax as I know in a text box, you can not use where the way I wrote it above...Thanks
I am facing issue in generating total sum and daily sum from table ThresholdData.
DailyTransactionAmount should be sum of todays amount in the table
TransactionAmount should be sum of all amount in the table.
Basically,
1. I don't want to scan ThresholdData table twice.
2. I don't want to create temporary table/table variable/CTE for this.
3. Is there is any way to make it done in single query.
I hope, where criteria is not possible in partition function. I am trying query something as given below,
SELECT TransactionDate,
TransactionAmount,
ROW_NUMBER() over (order by TransactionDate) AS TransactionCount,
SUM(TransactionAmount) over (partition by id ) AS TransactionAmount,
SUM(TransactionAmount) over (partition by id ,CONVERT (DATE, @TodaysTransactionDate)) AS DailyTransactionAmount
FROM ThresholdData
WHERE id = @id
AND transactiondate >= dateadd(d,-@TransactionDaysLimit,@TodaysTransactionDate)
Hi,
Could some one help me how to do
insert into test2(id,name) values ((select max(id) from test1),'user1')
in MS SQL Server
its throwing "Subqueries are not allowed in this context. Only sc
alar expressions are allowed" Exception. Help is appreciated.
Thanks,
Murali
Hello,
when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ?
GROUP BY Panel_Position)
i get this error returned:
0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.
I don't know where inside the sql string a conversion is necessary/fails.
Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ?
GROUP BY Panel_Position)
The only difference between the 2 statements is the ABS() function inside the sub query.
More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.
I would appreciate it very much if someone out there knows an answer or a hint and could tell me.
With kind regards,
Andre
While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?
Msg 8120, Level 16, State 1, Line 1
Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
<code>
select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,[Database Name]= DB_NAME(database_id),
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,
[code]...
I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?
Here is my code so far:
selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,
[Code] ....
Hi!
I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:
Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34
Incorrect syntax near the keyword 'option'.
Here is the function:
create FUNCTION [dbo].[ExpandedCTE]
(
@p_id int
)
RETURNS TABLE
AS
RETURN
(
with tbl_cte (id, tbl_id, lvl)
as
(
select
id, tbl_id, 0 lvl
from
tbl
where
id = @p_id
union all
select
t.id, t.tbl_id, lvl + 1
from
tbl_cte
inner join tbl t
on rnr.tbl_id = tbl_cte.id
)
select
id, tbl_id, lvl
from
tbl_cte
option (maxrecursion 0)
)
Please help!
Alexander.
P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.
How to encrypt the java application code using the 'with encryption' clause from sql server stored procedure or function.
View 3 Replies View RelatedHi
I want to return distinct values from a table in uppercase
Have tried
SELECT UPPER DISTINCT fieldname FROM tablename
But returns an error, what is the correct syntax.
Thanks
Is there a way I can have all entries in a table automatically in caps once entered by user, import or any other way ?
Thanks a lot in advance.
-PFD
Is there a method for converting the first character of a account name to uppercase and the the remaining characters to lower case? I've used the substring procedure but for a name like 'MY NEW COMPANY', how could I convert it to 'My New Company' ?
Thanks,
Terry
Folks,
what script must I use, as a part of CREATE TABLE, to automatically convert characters to UPPER case on insert?
I wrote <CHECK (country = UPPER (country)> in the CREATE TABLE, which was wrong,
because the values were still in the lower case.
The sample script is:
CREATE TABLE address
(street varchar(40),
city varchar(20),
state char (2),
zip varchar (10),
country varchar (20))
When a user types "Canada", I want the inserted value be "CANADA"
Any help is greatly appreciated.
I was wondering what more experienced DBAs have observed with regard to the capacity of a MSSQL DB. Is there an upper threshold of rows where performance becomes unacceptable? I have a fairly slow, but constant input rate of approximately 2,000 rows every 60 seconds or so (that is a little high, but I'm interested in worse case scenario here). That is up 172,800 rows a day. (I'm being overly pessimistic here.) We'd like to be able to keep all of this around as long as possible.
Or would a more heavy duty DB be in order for these sorts of data rates?
Table :Employee
name Age
'ARUN KUMAR' 30
name column should be displayed
as Arun Kumar
'A' and 'K' need to be upper can i know what function to use??
I am new to SQL and, unfortunately, actually do my work in Access 2003, but I have a question.
This is an example of what the data I am working with looks like:
AALADIN
AA-TACH EH65X, EH65V (V-Twin)
AA-TACH w/Robin 20 & 20.5 h.p. OHV
AA-TACH w/Wisc.-Robin EY21
ABI CONTRACTOR w/Honda 20 h.p. (V-Twin)
The all caps text strings at the beginning of the field need to end up in a separate field than the mixed strings, and the mixed strings need to stay together. The field length varies, as do the lengths of the all caps text strings. There are a lot of records, so I would be interested to know if there was a way to proceed without manually editing each line.
The
Hi;
I have a stored procedure :
<code>
Create Procedure ControlDept
(
@DeptID nvarchar(10)
)
As
If Exists
(
Select DeptName From Departments Where
DeptID LIKE @DeptID
)
Return 1
Else
Return 0
Now I want to apply replace and upper functions to DeptID in database before saying
"DeptID LIKE @DeptID".
for example the parameter is :"D&V"
DeptID in database is:"d & v" //there are spaces
if I say DeptID LIKE @DeptID nothing is found because of character nonmatching
So I have to apply replace & upper functions to the column DeptID in database
but how?
can you help me please??
We want to covert all reserved words
in procedures to upper case,any suggestions ?
My SQL Server database is not case sensetive.
How can I compare like cluase with search for capital and small letter?
For example
SELECT add1 from xcty_all where add1 like '%AL'%'
I need only
...................
10 ltncewwod way AL
456 Ruio St. AL
NOT
Duci Ral Rd Mexico
Albi Road Hawai CA
I want to ingore this bold letter on search
Sanjeev Shrestha
12/17/1971
Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional.
Thanks in advance for the help.
Php95saj
There may/may not be an upper limit for the number of rows in a table, but is there any performance-related limit?
I'm designing a database that stores results that have been acquired from a number of devices. Each device provides a set of data measurements every 10 minutes. Therefore each year a device will produce 52000 sets of results.
If I design a table to store a row for each set of measurements from a device (PK is based on the timestamp and the deviceID), and if there are 100 devices recording for 5 years, there will be 52000x100x5 rows. Would I get a performance increase by separating this data into one table per year? Perhaps the year could be appended to the table name to identify the particular tables.
A secondary issue is some devices can also be configured to produce a different set of measurements every 10 seconds. In this case there will be hundreds of millions of rows over a 5 year period. Therefore I am considering bulking the results into an array for a 10 minute period, and storing this array as a blob each 10 minutes. Is this going to be faster or slower than having hundreds of millions of rows?
Thanks in advance for any advice,
Mark.
Hello, we've an Oracle transition in the pipeline and want to convertall our database objects to upper case. Any one got a script ortechnique (other than manual) to do it?Many thanks, Kevin.
View 2 Replies View RelatedHi,
I have a problem. I need to rename all columns of a database to uppercase. Since SQL SERVER 2005 does not support changing system tables is there a smooth way to do this? Has anyone ideas for a script? point me to the right direction.
I have found the stored procedure sp_rename which could be useful (or would it be better to alter the tables)...
So any help would be appriciated very much...
Regards
fb
Almost all of our character fields are stored in upper-case. Is there an easy way to force SQL Server char and varchar fields to upper-case? Something I can do in SQL Server instead of in the client? It needs to apply to any new records.
There are some exceptions (email addresses for one). I don't mind going through each field and changing something.
Thanks!
Hi There,
I have a column which contain alphanumeric values:
aab123add234cdf423dej553edg543
If I try to return records between these values 'a' and 'e' it will only go as far as d. (first letter)
aab123add234cdf423dej553
This is true if I use where value between a and e
Or if I use greater than equal to operators
Any help would be great.
Thanks
Stuart
hi i want to select * from table1 where name =petter?now if there is many type of petter in table linke PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard
View 4 Replies View RelatedI have several tables in a deployed database in which the primary key is of type int, and autoincrements by 1 each time a record is added. My question is, since ints are 32-bit, what happens when its value reaches 4,294,967,296? I know that seems like an extrememly large amount of records, but when we imported the data into the database it started at key value 1,000,000. I don't know how to make it use lower numbers which are currently not being used (numbers below 1,000,000), and I am worried I will have problems when I reach the upper bound. What kind of problems could this cause? Should I change the primary key's type?Thanks!
View 2 Replies View RelatedOne of our database is approaching the gigabyte size. I know that microsoft claims to support terabyte databases with sql server 7.0. I was wondering if anyone could tell me about the max size of database they have used on an OLTP site without running into problems. ofcourse with SQL Server.
thanks,
rachna.
Hi,
Can we set the Uper/Lower case in server wide in SQL server 2000 as like case sensitive.
Thanks,
Ravi
Currently i have 2 type of data
A and a
But when i try to:
select * from tableA where col = 'a'
then all record with A and a will be selected, any way to avoid it and select only record with col ='a'?
Hi, all:I'd heard that the upper row limit in SQL of 6080 bytes may have beenincreased with SP3.Can anyone confirm/deny this? Is this still a 'carved-in-stone' uppercap?Thanks,DW.
View 1 Replies View RelatedWhile creating a table, can we default a column to be UPPER. So that even if we insert a lower case, it should be converted to UPPER case.
View 4 Replies View Related