|
Hello,
I am new to t-sql programming (sql server 2005) and I have a small problem. The DB system is current deployed with case sensitivity on. What I need to accomplish is a simple query that finds a distinct list of names and return them in a case-insensitive order.
here is what I attempted:
SELECT DISTINCT Name
FROM MyTable
ORDER BY lower(Name)
This of course bombs on me because "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." I intend on keeping the returned case the same as it appears in my table. This prevents me from solving the issue with "SELECT DISTINCT lower(Name)" Any help is much appreciated!
~Adam
|
|
|
|
|
Try:
SELECT DISTINCT Name COLLATE Latin1_General_CI_AI
FROM MyTable
ORDER BY 1 The "collate" keyword transforms the string into a case-insensitve collation. The "order by 1" is a lazy way of saying sort by the first column.
|
|
|
|
|
Hi!
I have a function that returns a long quary, more then 4000 chars.
I am returning the answer as a where clause, the problem is that i am returning it to the main where and there i get an error that says that it is a CLOB and can not be handled.
can you please help me? do i need to convert it to string?
this is the function declare:
CREATE OR REPLACE FUNCTION getorgentitylistyos (company_name IN VARCHAR2) RETURN CLOB IS
This is the answer from the function:
1 PL/SQL block, executed in 0.563 sec.
(OWNER IN ('DEUTSCHE BANK SUISSE','PRODUCTION DIVISION','GLOBAL DEPARTMENT','RINAT DIVISION','RINATTEST_DEPARTMENT','TANYA DIVISION','TANYA DEPARTMENT','TEST DIVISION','RUBY DEP','SWIFT DEPARTMENT','TEST DEPARTMENT','WRDIV1','WRDEP1','WRDEP10','WRDEP100',
'WRDEP101','WRDEP102','WRDEP103','WRDEP104','WRDEP105','WRDEP106','WRDEP107','WRDEP108','WRDEP109','WRDEP11','WRDEP110','WRDEP111','WRDEP112','WRDEP113','WRDEP114','WRDEP115','WRDEP116','WRDEP117','WRDEP118','WRDEP119','WRDEP12','WRDEP120','WRDEP121','WRD
EP122','WRDEP123','WRDEP124','WRDEP125','WRDEP126','WRDEP127','WRDEP128','WRDEP129','WRDEP13','WRDEP130','WRDEP131','WRDEP132','WRDEP133','WRDEP134','WRDEP135','WRDEP136','WRDEP137','WRDEP138','WRDEP139','WRDEP14','WRDEP140','WRDEP141','WRDEP142','WRDEP14
3','WRDEP144','WRDEP145','WRDEP146','WRDEP147','WRDEP148','WRDEP149','WRDEP15','WRDEP150','WRDEP151','WRDEP152','WRDEP153','WRDEP154','WRDEP155','WRDEP156','WRDEP157','WRDEP158','WRDEP159','WRDEP16','WRDEP160','WRDEP161','WRDEP162','WRDEP163','WRDEP164','
WRDEP165','WRDEP166','WRDEP167','WRDEP168','WRDEP169','WRDEP17','WRDEP170','WRDEP171','WRDEP172','WRDEP173','WRDEP174','WRDEP175','WRDEP176','WRDEP177','WRDEP178','WRDEP179','WRDEP18','WRDEP180','WRDEP181','WRDEP182','WRDEP183','WRDEP184','WRDEP185','WRDE
P186','WRDEP187','WRDEP188','WRDEP189','WRDEP19','WRDEP190','WRDEP191','WRDEP192','WRDEP193','WRDEP194','WRDEP195','WRDEP196','WRDEP197','WRDEP198','WRDEP199','WRDEP2','WRDEP20','WRDEP200','WRDEP201','WRDEP202','WRDEP203','WRDEP204','WRDEP205','WRDEP206',
'WRDEP207','WRDEP208','WRDEP209','WRDEP21','WRDEP210','WRDEP211','WRDEP212','WRDEP213','WRDEP214','WRDEP215','WRDEP216','WRDEP217','WRDEP218','WRDEP219','WRDEP22','WRDEP220','WRDEP221','WRDEP222','WRDEP223','WRDEP224','WRDEP225','WRDEP226','WRDEP227','WRD
EP228','WRDEP229','WRDEP23','WRDEP230','WRDEP231','WRDEP232','WRDEP233','WRDEP234','WRDEP235','WRDEP236','WRDEP237','WRDEP238','WRDEP239','WRDEP24','WRDEP240','WRDEP241','WRDEP242','WRDEP243','WRDEP244','WRDEP245','WRDEP246','WRDEP247','WRDEP248','WRDEP24
9','WRDEP25','WRDEP250','WRDEP251','WRDEP252','WRDEP253','WRDEP254','WRDEP255','WRDEP256','WRDEP257','WRDEP258','WRDEP259','WRDEP26','WRDEP260','WRDEP261','WRDEP262','WRDEP263','WRDEP264','WRDEP265','WRDEP266','WRDEP267','WRDEP268','WRDEP269','WRDEP27','W
RDEP270','WRDEP271','WRDEP272','WRDEP273','WRDEP274','WRDEP275','WRDEP276','WRDEP277','WRDEP278','WRDEP279','WRDEP28','WRDEP280','WRDEP281','WRDEP282','WRDEP283','WRDEP284','WRDEP285','WRDEP286','WRDEP287','WRDEP288','WRDEP289','WRDEP29','WRDEP290','WRDEP
291','WRDEP292','WRDEP293','WRDEP294','WRDEP295','WRDEP296','WRDEP297','WRDEP298','WRDEP299','WRDEP3','WRDEP30','WRDEP300','WRDEP31','WRDEP32','WRDEP33','WRDEP34','WRDEP35','WRDEP36','WRDEP37','WRDEP38','WRDEP39','WRDEP4','WRDEP40','WRDEP41','WRDEP42','WR
DEP43','WRDEP44','WRDEP45','WRDEP46','WRDEP47','WRDEP48','WRDEP49','WRDEP5','WRDEP50','WRDEP51','WRDEP52','WRDEP53','WRDEP54','WRDEP55','WRDEP56','WRDEP57','WRDEP58','WRDEP59','WRDEP6','WRDEP60','WRDEP61','WRDEP62','WRDEP63','WRDEP64','WRDEP65','WRDEP66',
'WRDEP67','WRDEP68','WRDEP69','WRDEP7','WRDEP70','WRDEP71','WRDEP72','WRDEP73','WRDEP74','WRDEP75','WRDEP76','WRDEP77','WRDEP78','WRDEP79','WRDEP8','WRDEP80','WRDEP81','WRDEP82','WRDEP83','WRDEP84','WRDEP85','WRDEP86','WRDEP87','WRDEP88','WRDEP89','WRDEP9
','WRDEP90','WRDEP91','WRDEP92','WRDEP93','WRDEP94','WRDEP95','WRDEP96','WRDEP97','WRDEP98','WRDEP99','WRDIV10','WRDIV100','WRDIV101','WRDIV102','WRDIV103','WRDIV104','WRDIV105','WRDIV106','WRDIV107','WRDIV108','WRDIV109','WRDIV11','WRDIV110','WRDIV111','
WRDIV112','WRDIV113','WRDIV114','WRDIV115','WRDIV116','WRDIV117','WRDIV118','WRDIV119','WRDIV12','WRDIV120','WRDIV121','WRDIV122','WRDIV123','WRDIV124','WRDIV125','WRDIV126','WRDIV127','WRDIV128','WRDIV129','WRDIV13','WRDIV130','WRDIV131','WRDIV132','WRDI
V133','WRDIV134','WRDIV135','WRDIV136','WRDIV137','WRDIV138','WRDIV139','WRDIV14','WRDIV140','WRDIV141','WRDIV142','WRDIV143','WRDIV144','WRDIV145','WRDIV146','WRDIV147','WRDIV148','WRDIV149','WRDIV15','WRDIV150','WRDIV151','WRDIV152','WRDIV153','WRDIV154
','WRDIV155','WRDIV156','WRDIV157','WRDIV158','WRDIV159','WRDIV16','WRDIV160','WRDIV161','WRDIV162','WRDIV163','WRDIV164','WRDIV165','WRDIV166','WRDIV167','WRDIV168','WRDIV169','WRDIV17','WRDIV170','WRDIV171','WRDIV172','WRDIV173','WRDIV174','WRDIV175','W
RDIV176','WRDIV177','WRDIV178','WRDIV179','WRDIV18','WRDIV180','WRDIV181','WRDIV182','WRDIV183','WRDIV184','WRDIV185','WRDIV186','WRDIV187','WRDIV188','WRDIV189','WRDIV19','WRDIV190','WRDIV191','WRDIV192','WRDIV193','WRDIV194','WRDIV195','WRDIV196','WRDIV
197','WRDIV198','WRDIV199','WRDIV2','WRDIV20','WRDIV200','WRDIV201','WRDIV202','WRDIV203','WRDIV204','WRDIV205','WRDIV206','WRDIV207','WRDIV208','WRDIV209','WRDIV21','WRDIV210','WRDIV211','WRDIV212','WRDIV213','WRDIV214','WRDIV215','WRDIV216','WRDIV217','
WRDIV218','WRDIV219','WRDIV22','WRDIV220','WRDIV221','WRDIV222','WRDIV223','WRDIV224','WRDIV225','WRDIV226','WRDIV227','WRDIV228','WRDIV229','WRDIV23','WRDIV230','WRDIV231','WRDIV232','WRDIV233','WRDIV234','WRDIV235','WRDIV236','WRDIV237','WRDIV238','WRDI
V239','WRDIV24','WRDIV240','WRDIV241','WRDIV242','WRDIV243','WRDIV244','WRDIV245','WRDIV246','WRDIV247','WRDIV248','WRDIV249','WRDIV25','WRDIV250','WRDIV251','WRDIV252','WRDIV253','WRDIV254','WRDIV255','WRDIV256','WRDIV257','WRDIV258','WRDIV259','WRDIV26'
,'WRDIV260','WRDIV261','WRDIV262','WRDIV263','WRDIV264','WRDIV265','WRDIV266','WRDIV267','WRDIV268','WRDIV269','WRDIV27','WRDIV270','WRDIV271','WRDIV272','WRDIV273','WRDIV274','WRDIV275','WRDIV276','WRDIV277','WRDIV278','WRDIV279','WRDIV28','WRDIV280','WR
DIV281','WRDIV282','WRDIV283','WRDIV284','WRDIV285','WRDIV286','WRDIV287','WRDIV288','WRDIV289','WRDIV29','WRDIV290','WRDIV291','WRDIV292','WRDIV293','WRDIV294','WRDIV295','WRDIV296','WRDIV297','WRDIV298','WRDIV299','WRDIV3','WRDIV30','WRDIV300','WRDIV31'
,'WRDIV32','WRDIV33','WRDIV34','WRDIV35','WRDIV36','WRDIV37','WRDIV38','WRDIV39','WRDIV4','WRDIV40','WRDIV41','WRDIV42','WRDIV43','WRDIV44','WRDIV45','WRDIV46','WRDIV47','WRDIV48','WRDIV49','WRDIV5','WRDIV50','WRDIV51','WRDIV52','WRDIV53','WRDIV54','WRDIV
55','WRDIV56','WRDIV57','WRDIV58','WRDIV59','WRDIV6','WRDIV60','WRDIV61','WRDIV62','WRDIV63','WRDIV64','WRDIV65','WRDIV66','WRDIV67','WRDIV68','WRDIV69','WRDIV7','WRDIV70','WRDIV71','WRDIV72','WRDIV73','WRDIV74','WRDIV75','WRDIV76','WRDIV77','WRDIV78','WR
DIV79','WRDIV8','WRDIV80','WRDIV81','WRDIV82','WRDIV83','WRDIV84','WRDIV85','WRDIV86','WRDIV87','WRDIV88','WRDIV89','WRDIV9','WRDIV90','WRDIV91','WRDIV92','WRDIV93','WRDIV94','WRDIV95','WRDIV96','WRDIV97','WRDIV98','WRDIV99','anna division','sanitydiv','s
anitydep')) And this is the main SQL:
REATE OR REPLACE VIEW test (
msg_id
) AS
SELECT msg_id
FROM messages
WHERE getorgentitylistyos('DEUTSCHE BANK SUISSE')
WITH READ ONLY
i get this error:
ORA-00920: invalid relational operator
Why????
Thanks for your help 
|
|
|
|
|
I'm not good in Oracle.
however what i feel is resultant of function is not a Boolean value (True/False) where as the condition in the WHERE Clause should result in a boolean value.
Request you to please check for this.
Regards
KP
|
|
|
|
|
I have developed a small desktop application using c# and Ms Access 2002. Database is password protected and contains sensitive data. As many password retrieval tools are available, What should I do to protect Ms-Access (.MDB ) file? Is there any way through which I can hide database file.
poly
|
|
|
|
|
testmail_123 wrote: As many password retrieval tools are available
Yep, and this is exactly why you don't store sensitive data in an Access database. There is simply no way to protect it reliably.
testmail_123 wrote: What should I do to protect Ms-Access (.MDB ) file? Is there any way through which I can hide database file.
No, there isn't. Since Access is a file-based database, the .MDB file must be visible to the user using your application. This means he'she must have Read and Write access to the .MDB file. You can turn on the Hidden file attribute, but this is pathetically easy to defeat.
You can try to encrypt some of the database in the database, but you have HUGE performance problems and usability issues.
The only way to secure sensitive data is to use a real database engine, like SQL Server or Oracle. The security is MUCH better and easier to control.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
|
Try using MSDE (SQL 2005 Express Ed.) instead of MS Access. Even if the user can open the file by installing SQL Server Management Studio or Query Analyzer you can deny them permission to access the sensitive data.
|
|
|
|
|
Hello Friends,
I have never written a cursor before and I have a situation in which I need it. I have two days trying and still couldn't make it work...I need some help
I have three tables one for Items, one for Orders and one for OrderDetail. I want to check every X days that all the unpaid orders(which has items blocked on it, defined in OrderDetail) free their items and increase the amount on the Items table. Evey time a customer make an order, the items are reserved, so they need to be freed if the order is canceled or remains unpaid for a given time.
In this case I need a cursor that iterates through all the items belonging to the same unpaid order, then increase the amount in the Items table and delete the record in the OrderDetail table.
Please, could you help me in this problem??
I will appreciate any help
Thanks.
|
|
|
|
|
Most of the time, when I see somebody say they need a cursor I can't help thinking that they've missed out on the alternatives. This seems to be the case here. You probably don't need to use a cursor. What you could do is to create a temporary (or in memory) table that contains details of all of the unfilled orders. Then, once you have done this, you would write your update statements for the other tables based on this table.
What you will find is that this approach is more efficient because it allows the database engine to operate in a set based manner which is what DB engines are designed to do.
As an example (in pseudocode)
SELECT the missing records
FROM orderDetails
INTO a Temporary Table.
UPDATE order WITH THE itemTotals FROM Temporary Table
WHERE THE Item Id IN Temporary Table
MATCHES THE Id in my Item TABLE.
Do the other tables here... Anyway, that's what I would do.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks Pete,
I will be more specific, I tried your suggestion but still doesn't work...I'm not an SQL expert  )
I will rite here the involved tables and their main fields:
Orders(OrderId)
OrderDetail(OrderId, ProductSubCode, Quantity)
Products(ProductSubCode, Quantity)
When an order is canceled I want to get all the items on it and re-insert them in the Product table (summarizing with the amount already in that table)
If I do a select of all the items in a given order as you told me I will get something like this:
"SELECT * INTO #TMP FROM ORDERDETAIL WHERE ORDERID=30"
OrderId ProductSubCode Quantity
30 BD0002 10
30 BD0004 4
Now I need to re-insert those quantities into the Products table.
I can't figure out how I ca do that without a cursor since I need to iterate through the records in the temporary table.
Of course I can still be missing alternatives
Thanks a lot.
|
|
|
|
|
OK - here's a sample way to update the product table:
UPDATE Products
SET p.Quantity = p.Quantity + t.Quantity
FROM Products p, #Tmp t
WHERE p.ProductSubCode = t.ProductSubCode This doesn't involve any cursors and is completely set based.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks a lot Pete!!!!
That worked.
As you stated there was not necessary a cursor here!!
|
|
|
|
|
No problems. I'm glad that I could help.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
how to update feilds from two different tables
and insert updated values into orignal tables.
|
|
|
|
|
probably by writing two insert statements.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
hi all
i have 2 table province and distric is one_many
in province and distric have column "code" datatype is interger
data on column code Province is length = 3 Ex("101 or 302 or 343")
in the data on column code distric is length = 5 Ex 10101" or "30234")
The data both column code in 2 table have there character fist is same:
EX Province is NY: 101 so all distrcit into NY have there character fist is 101 and add two character is: 10101 , 10102, 10103
how can select * from distric and get column name on the province
?????
|
|
|
|
|
Well, you've broken the first normal form by storing two pieces of information inside one column. You need to split out the province's code from district's code in the district table. You can also duplicate the province's code in the district.
You can then join on the common province codes in each table. Once that is done, you can then write a statement like this:
SELECT province.name, district.* FROM district
INNER JOIN province ON province.code = district.province_code
|
|
|
|
|
Is it possible to select from 3 tables, and insert into a new table without using a where clause and using the SQL cursor. If so, how would I do that? Any code example?
|
|
|
|
|
The examples that come with the help for SQL Server show how to use a cursor. You can use a cursor on any select, across any number of tables. As you step through it, you an insert into a new table from each row.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I have to statements inside the StoredProcedure. If in the first satement occured Error I want exit from the StoredProcedure. For this Scenario, How to write StoredProcedure
Mohan Balal
|
|
|
|
|
You can use try-catch block to catch the exceptions..
See THIS
PS. you are asking basic questions...try and search for them on google before asking. Take an initiative yourself and we will help you in it, but if you want us to take an initiative for you YOU MISS-READ THE IDEA OF CODE PROJECT .
Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.
|
|
|
|
|
Thanks for your Reply and kind suggestions
Mohan balal
|
|
|
|
|
select count(*) from Emp
Select count(empid) from Emp
what is the different between above two sql statements
Mohan Balal
|
|
|
|
|
Count(*)
returns the number of items in a group, including NULL values
Count(fieldName)
returns the number of items in a group, excluding NULL values
Hope it helps
Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
