|
i want a transact sql for select queries which returns
all rows that include something like here:
table1
code, name, address, ?? , ??
1 , john , toronto , ..
2 , mike , new york , ..
3 , alec , john city, ..
...
select * from table1 where includes 'john'
result is:
1 , john , toronto
3 , alec , john city
[note1: i do not know field names]
[note2: fields are in different data types]
[ like : int, numeric, float, char, text, image ....]
[note3: full text indexing is appropriate for string fields,not for all types]
|
|
|
|
|
amraouf wrote: [note1: i do not know field names]
Then you will need to find out.
amraouf wrote: [note2: fields are in different data types]
It is a bit difficult to do a string match on a non-string column.
amraouf wrote: [note3: full text indexing is appropriate for string fields,not for all types]
Then why not create a string column with all the searchable stuff in it (including string representations of non-string column) and index that. If you make the column name consistent across all tables then you don't need to know each individual column name, because all tables have it.
|
|
|
|
|
ooops!
its a bit difficult to change the structure of a database for such a query !
|
|
|
|
|
amraouf wrote: its a bit difficult to change the structure of a database
How come?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
amraouf wrote: its a bit difficult to change the structure of a database for such a query !
No it isn't. This is a simple change.
1. Add the extra column on each table that requires it.
2. Add triggers to each table to update the new column each time there is an insert or update
If you have difficulty figuring out how to change the database structure from one thing to another then you might want to buy a book on refactoring databases. There is a very good one published by Addison Wesley.
|
|
|
|
|
Colin Angus Mackay wrote: This is a simple change.
1. Add the extra column on each table that requires it.
2. Add triggers to each table to update the new column each time there is an insert or update
Thank you, I was too lazy to go to that extent earlier 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thank you very much
Of course I designed the database and I am the first one who can change or update it
but you know
in this phase of project i can not push my time on this matter
|
|
|
|
|
amraouf wrote: Of course I designed the database and I am the first one who can change or update it
but you know
in this phase of project i can not push my time on this matter
When I put a project together I figure out what are the bigger risks and do them up front so I've got as much time as possible to make changes if my ideas don't work out. It sounds to me that you need to be identifying those risks before it gets too late.
If the feature is not a "Must have" then it can be dropped. If the feature is required then the solution I gave is probably the quickest route to it being implemented.
|
|
|
|
|
select * from TableName where name + address like '%john%'
I Love SQL
|
|
|
|
|
And this acounts for the numeric types how exactly? Also, it is hardly an optimised solution - The database is forced to scan the entire table each time.
|
|
|
|
|
I want a create a constraint such that if value of cola='Y'
then value of colb cannot be null.Please help
|
|
|
|
|
to my knowledge this can be done only using trigger.
Regards
KP
|
|
|
|
|
i have a table called loginfo in the server which is sql server is windows authenticated . i have user account in the server and if i have to connect to the server(loginfo) via an application do i have to give my uerid and password to the connection string ? how far is this secure..
|
|
|
|
|
Hi all,
I want join in 5tables in my sqlserver database.
How I join 5tables,empno is a primary key for all tables.
thanks
|
|
|
|
|
select *
from
tab1 inner join
tab2 on tab1.empno=tab2.empno inner join
tab3 on tab2.empno=tab3.empno inner join
tab4 on tab3.empno=tab4.empno inner join
tab5 on tab4.empno=tab5.empno
where
1=1
|
|
|
|
|
hi amraouf
Thanks for your reply,
|
|
|
|
|
hey i am trying to make a select query on a dataset
with the .select() method which equals
to the following lines writen in sql:
select *
from CA_ORDERS
where SUPPLIER in
(
select sup_name
from SUPPLIER
where SUP_RELATION = 'Drop shipping' )
well i may say i dont know the specific format of the .select() method
on datasets , cause it seems to be diferent from SQL lang.
how can i implement the 'IN' Filter?
Net
|
|
|
|
|
Do you mean DataTable.Select() ? This link describes available syntax: Expression Syntax[^]
|
|
|
|
|
hi
i want to be able to give the user the option to select either Top 10, Top 20 or Top 30 from a result set.
In my page they select from a drop down list and it must produce the graph accordingly, I would like to make 'Top' figure to be a variable so i only have to do one stored procedure.Not one for each Top 10, Top 20 or Top 30.
Is this possible?
Or do i have to create a stored procedure for each and then just call the appropriate procedure in my code?
Thanks
|
|
|
|
|
You can have a stored procedure which builds dynamic sql according to user input and executes it. There can be an easier and/or more convenient way but I'm not aware of.
|
|
|
|
|
Giorgi Dalakishvili wrote: There can be an easier and/or more convenient way but I'm not aware of.
I don't think there is. He can just have a parameter into the stored proc to specify how many top values he wants to retrieve. That would be my approach.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Paul Conrad wrote: He can just have a parameter into the stored proc to specify how many top values he wants to retrieve. That would be my approach.
I also thought about that but top clause doesn't accepts variables, you can only specify integers
|
|
|
|
|
I forgot about TOP not allowing any variables.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Just found out that in sql server 2005 TOP supports variables so you can write a parametrized sproc.
|
|
|
|
|
That is interesting, because I tried and it complained that it wasn't valid.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
