|
|
|
Junior Member
 
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
Is there anything like Like all or Like any command...
if we include more than one field inside the sql.
for eg:
where
first_name like ('ad%', 'w%', 'e%')
Do we need to include ANY OR ALL....
first_name like all('ad%', 'w%', 'e%')
first_name like any('ad%', 'w%', 'e%')
if so what is difference between having an All and Any??
-Thank you
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
It is just like you say:
where
first_name like ANY ('ad%', 'w%', 'e%')
will find adrian, william and eleanor
first_name like ALL ('ad%', 'w%', 'e%')
will not find anything because you cannot build a string which starts with AD, W and E!
But first name like ALL ('%an%','%w%, '%e%')
will find Andrew but not adrian, william or Eleanor.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
To add more to Jimm reply...
The ALL and ANY is quantifiers for 'LIKE' operator, these quantifiers are used to indicate how many string patterns must be found in the string expression.
ANY - one or more pattern should match in the string expression
ALL - All patterns in the list should be match.
Regards,
Balamurugan
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/11/2009 1:12:32 PM
Posts: 14,
Visits: 39
|
|
thank you both!!
related to this i also got another doubt..
the query which im executing is kinda big..so i need to create a global temp table or derived table...
how do i do it...
do i need to include the database name before the table name??
for example...for the following query..how to i create a temp table or derived table....
sel e.firstname, c.year, e.month, sum(c.salary)
from employers e
join customers c
where c.month_key = e.month_key
on month like any (%m%, %S%, %j%)
group by 1,2,3
Also, do i need to run the gtt every time before running the main query in which gtt is included??
-Thank you
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: Yesterday @ 7:15:08 AM
Posts: 7,
Visits: 67
|
|
Just create the global temporary table with the same definition as your select columns and index
ex:
CREATE global temporary table table1( A INTEGER NOT NULL,
B INTEGER NOT NULL)
on commit preserve rows;
insert into table1(
your select query)
sel * from table1;
Thanks
Rajeev T
|
|
|
|