[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference orarep::nomahs::sql

Title:	SQL notes

Moderator:	NOVA::SMITHI

Created:	Wed Aug 27 1986
Last Modified:	Thu Jun 05 1997
Last Successful Update:	Fri Jun 06 1997
Number of topics:	3895
Total number of notes:	17726

3865.0. "problem with CONTAINING" by M5::PTAKEO () Thu Feb 20 1997 16:41

  Hi,

	I have a customer that is using Rdb V6.0, Sql, DEC MCS and 
  some latin characters (like �, �, �) in insert. But she has a 
  problem when she is trying to use "containing predicate" in select
  statement. Sql Ref.Manual Part 1 "Containing Predicate syntax" 
  show us that if I issue "containing 'c'", I will get 'c', 'C' and 
  '�', but it isn't happening, I get only 'c' and 'C'. What is it wrong?

	Any ideas ?

  thanks...Pedro

T.R	Title	User	Personal Name	Date	Lines
3865.1		NOVA::SMITHI	Don't understate or underestimate Rdb!	`Thu Feb 20 1997 22:58`	4
	Do you have a collating sequence defined? Can you give us a SQL script that produces the error? Ian
3865.2	return script	M5::PTAKEO		`Fri Feb 21 1997 08:42`	131
	Hi Ian, Thanks for your help. > Do you have a collating sequence defined? > Can you give us a SQL script that produces the error? No, I don't have collating and this note has a script below. Thanks...Pedro -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Database Definition File -- -------------------------------------------------------------------------------- set verify; set language ENGLISH; set default date format 'SQL92'; set quoting rules 'SQL92'; set date format DATE 001, TIME 001; -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Physical Database Definition -- -------------------------------------------------------------------------------- create database filename '[]ptakeo.RDB' dictionary is NOT REQUIRED protection is ACL number of users 50 number of cluster nodes 16 buffer size is 6 blocks number of buffers 20 number of recovery buffers 20 adjustable lock granularity ENABLED global buffers are DISABLED (number is 250, user limit 5) carry over locks are ENABLED lock timeout interval is 0 seconds statistics collection is ENABLED system index compression is DISABLED No restricted access snapshot is ENABLED IMMEDIATE -- read write storage area locking is row level page size is 2 blocks allocation is 603 pages extent is (minimum 99, maximum 9999, percent growth 20) snapshot allocation is 100 pages snapshot extent is (minimum 99, maximum 9999, percent growth 20) ; -- end create database -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Collating Sequences -- -------------------------------------------------------------------------------- -- no collating sequences defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Domain Definitions -- -------------------------------------------------------------------------------- -- no domains defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Function Definitions -- -------------------------------------------------------------------------------- -- no functions defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Table Definitions -- -------------------------------------------------------------------------------- create table TNAMES ( CNAME CHAR (30)); commit work; -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Index Definitions -- -------------------------------------------------------------------------------- -- no indices defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Storage Map Definitions -- -------------------------------------------------------------------------------- -- no storage maps defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- View Definitions -- -------------------------------------------------------------------------------- -- no views defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Trigger Definitions -- -------------------------------------------------------------------------------- -- no triggers defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Module Definitions -- -------------------------------------------------------------------------------- -- no modules defined -- RMU/EXTRACT for DEC Rdb V6.0-1 21-FEB-1997 09:47:47.38 -- -- Query Outline Definitions -- -------------------------------------------------------------------------------- -- no outlines defined insert into tnames values ('a'); insert into tnames values ('�'); insert into tnames values ('�'); insert into tnames values ('c'); insert into tnames values ('�'); commit; select * from tnames where cname containing 'c'; select * from tnames where cname containing 'a';
3865.3	you need a collating sequence	NOVA::SMITHI	Don't understate or underestimate Rdb!	`Fri Feb 21 1997 13:06`	48
	~ > Do you have a collating sequence defined? ~ > Can you give us a SQL script that produces the error? ~ ~ No, I don't have collating and this note has a script below. You need to define a collating sequence. The default in the database is ASCII. I modified your script to create and use an MCS collating sequence. You see the collating sequence tells us that the various characters are related. enjoy Ian SQL> -- Multinational already exists in the default NCS library SQL> create collating sequence dec_mcs Multinational; SQL> SQL> create domain mcs_name CHAR (30) collating sequence dec_mcs; SQL> SQL> create table TNAMES ( cont> CNAME cont> mcs_name); SQL> SQL> insert into tnames values ('a'); 1 row inserted SQL> insert into tnames values ('�'); 1 row inserted SQL> insert into tnames values ('�'); 1 row inserted SQL> insert into tnames values ('c'); 1 row inserted SQL> insert into tnames values ('�'); 1 row inserted SQL> SQL> select * from tnames where cname containing 'c'; CNAME c � 2 rows selected SQL> select * from tnames where cname containing 'a'; CNAME a � � 3 rows selected SQL>