Wildcards are useful when searching for items using LIKE clause. MSSQL allows a few wildcard specifier. The most commonly used is the % character. There are some other wildcards available in MSSQL. The underscore character `_` is used for a single character wildcard. the `[]` wildcard indicates that any single character set or range specified in it will be a wild card. If the first character is ^ in the `[]` set or range, it is equivalent to single character `not`matching.
Now we do have literal characters that uses the wildchard character. How do we defile the literal characters in the search? it is quite simple. Simply enclose the character between `[]`. That will include the `[` and `]` also. Another character is the single quote. Well, simply add another single quote to it. Finally there is an ESCAPE clause where you could define a escape character. Normally the escape character is a backslash. just add ESCAPE `\' to the end of the where clause.
In Oracle, it has quite similar syntax except that it does not have `[]`. It does uses ESCAPE clause exactly like MSSQL. I think we should use the ESCAPE clause as standard except under special circumstances then we use the `[]` wildcard.
Monday, August 16, 2010
Recursive search
In MSSQL 2005 there is a recursive search facility. The clause used is [with]. Example is below.
with PartSearch (Part_number,Replacement_part, level) as {
select Part_number,Replacement_part, 0 as level from wfm_parts p
union all
select p.Part_number, p.Replacement_part, ph.level+1 from wfm_parts p
inner join PartSearch ph on ph.part_number = p.Replacement_part }
select Part_number,Replacement_part, level from PartSearch where level > 0
Obviously, you have to use stored_procedures to do it.
PartSearch is a pseudo name created. It can be any name. The above is not tested as I do not have a good database sample to run the example. The syntax should be correct.
with PartSearch (Part_number,Replacement_part, level) as {
select Part_number,Replacement_part, 0 as level from wfm_parts p
union all
select p.Part_number, p.Replacement_part, ph.level+1 from wfm_parts p
inner join PartSearch ph on ph.part_number = p.Replacement_part }
select Part_number,Replacement_part, level from PartSearch where level > 0
Obviously, you have to use stored_procedures to do it.
PartSearch is a pseudo name created. It can be any name. The above is not tested as I do not have a good database sample to run the example. The syntax should be correct.
Output Clause in MSSQL 2005
In MSSQL when you insert or delete a record, you don`t get a returned recordset. It is always a headache to first determine what is the record that was deleted or inserted when there is a auto increment index present.
Starting fom MSSQL 2005 there is an interesting command which resembles MYSQL mysql_insert_id() function. It has more functionality than MYSQL. It can be used in INSERT, DELETE, UPDATE clause to get the id involved.
For example
insert into tbl_test_insert (case_id) output INSERTED.ID, INSERTED.case_id values(`123456789`)
The query is the same as any INSERT query except there is some extra clause inserted in between. The clause is OUTPUT INSERTED.ID, INSERTED.case_id. This clause tells the query to output values defined by INSERTED. The value is actually the record or the data that was inserted/deleted into the table.
It comes very handy if you have to insert/delete a main table and then insert into a sub table that has the same id as the main record. The id in the main record is auto increment identity column so that it could be an index. You will not know what is the value till the record is inserted. By inserting/deleting a record in the main table you will need to get the id value so that you could perform insert/delete on the sub table.
I used to insert a UUID when inserting the main table then query the main table to get the id by finding the UUID. Now I can just use the insert statement then simply do a odbc_fetch_row() to get the id with just one query. Neat.
Just to let you know this- use DELETED.xx if you are doing delete query. For UPDATE you use INSERTED for those fields you updated and use DELETED for those fields you replaced.
Starting fom MSSQL 2005 there is an interesting command which resembles MYSQL mysql_insert_id() function. It has more functionality than MYSQL. It can be used in INSERT, DELETE, UPDATE clause to get the id involved.
For example
insert into tbl_test_insert (case_id) output INSERTED.ID, INSERTED.case_id values(`123456789`)
The query is the same as any INSERT query except there is some extra clause inserted in between. The clause is OUTPUT INSERTED.ID, INSERTED.case_id. This clause tells the query to output values defined by INSERTED. The value is actually the record or the data that was inserted/deleted into the table.
It comes very handy if you have to insert/delete a main table and then insert into a sub table that has the same id as the main record. The id in the main record is auto increment identity column so that it could be an index. You will not know what is the value till the record is inserted. By inserting/deleting a record in the main table you will need to get the id value so that you could perform insert/delete on the sub table.
I used to insert a UUID when inserting the main table then query the main table to get the id by finding the UUID. Now I can just use the insert statement then simply do a odbc_fetch_row() to get the id with just one query. Neat.
Just to let you know this- use DELETED.xx if you are doing delete query. For UPDATE you use INSERTED for those fields you updated and use DELETED for those fields you replaced.
SQL FIRST aggregate function
As you all knows very well that the FIRST aggregate function does not exists in sql server.
There are actually tweaks to make it work. Heres how.
select case_id, RowNumber from (select case_id, ROW_NUMBER() over (partition by case_id order by subcase_id) as RowNumber from tbl_case) as tblcase where RowNumber=1
The above sql actually returns the first subcase information since i use ORDER BY SUBCASE_ID in PARTITION BY.
This is one of those odd cases where you do a select from a derived table.
This query is particularly useful when you happen to have duplicate subcase_id. It will return unique rows.
Obviously, if you just do like the example above, it is kind of going the long way. A simple DISTINCT and a WHERE clause can do much better job than that. But if you have duplicate subcase_id then your DISTINCT,WHERE method may not work as per expected. Moreover, you could make use of the derived table to do MAX and MIN aggregate function on other fields across all subcases.
PARTITION BY works similar to BRIOQUERY SUM BY method. It does not require a GROUP BY which is somtimes very obstructing to the AGGREGATE function. For example you want to do an aggregate but also want to include the case_title field. That is impossible for normal query with GROUP BY since case_title is almost unique to each case. After getting the aggregated results then you use the above SELECT FROM DERIVED TABLE method to do further filtering to get only the results you want.
There are actually tweaks to make it work. Heres how.
select case_id, RowNumber from (select case_id, ROW_NUMBER() over (partition by case_id order by subcase_id) as RowNumber from tbl_case) as tblcase where RowNumber=1
The above sql actually returns the first subcase information since i use ORDER BY SUBCASE_ID in PARTITION BY.
This is one of those odd cases where you do a select from a derived table.
This query is particularly useful when you happen to have duplicate subcase_id. It will return unique rows.
Obviously, if you just do like the example above, it is kind of going the long way. A simple DISTINCT and a WHERE clause can do much better job than that. But if you have duplicate subcase_id then your DISTINCT,WHERE method may not work as per expected. Moreover, you could make use of the derived table to do MAX and MIN aggregate function on other fields across all subcases.
PARTITION BY works similar to BRIOQUERY SUM BY method. It does not require a GROUP BY which is somtimes very obstructing to the AGGREGATE function. For example you want to do an aggregate but also want to include the case_title field. That is impossible for normal query with GROUP BY since case_title is almost unique to each case. After getting the aggregated results then you use the above SELECT FROM DERIVED TABLE method to do further filtering to get only the results you want.
SQL SERVER AGGREGTE OVER CLAUSE
Just discovered a new way of doing max and min on different date like brioquery (sum by) syntax.
select distinct case_id, max(close_date) over (partition by case_id) as maxdate, min(open_date) over (partition by case_id) as mindate from wfm_subcase
Previously I had to use a derived table to get each of the two dates. Now it can be done in a single clean sql statement.
There is a catch if you uses sql server 2005. You will get error message if you use query designer to edit the query. Since the query designer mode is always the default mode when you create a view, just create a normal query without the over clause then save the view. Next, open the view in edit mode (not design mode) and change the query to a over clause query.
select distinct case_id, max(close_date) over (partition by case_id) as maxdate, min(open_date) over (partition by case_id) as mindate from wfm_subcase
Previously I had to use a derived table to get each of the two dates. Now it can be done in a single clean sql statement.
There is a catch if you uses sql server 2005. You will get error message if you use query designer to edit the query. Since the query designer mode is always the default mode when you create a view, just create a normal query without the over clause then save the view. Next, open the view in edit mode (not design mode) and change the query to a over clause query.
Find duplicate cases in SQL sever
Many a times we want to check if there are duplicates in a table.
select case_id, count(case_id) as casecnt from wfm_case group by case_id having (count(case_id) >1)
select case_id, count(case_id) as casecnt from wfm_case group by case_id having (count(case_id) >1)
Derived Table
`Derived table` in mssql is equivalent to `inline view` in Oracle or `subselect` in Postgres. Its purpose is to let us use a `select` query as a source with an alias. Example
Select * from (select case_id,open_date from tbl_data) AS S
It looked stupid to have such a query which does not help in anything. However, its very much more useful than just that.
For example. You wish to get the last close date from a subcase while retrieving a main case data. This could only be achieved if you create a view to get the last close date or create a temporary table to get the last close date. Once you can get the data from the view or temp table, you join it to main case to get the final result.
Creating temp table means you have to do a number of steps to achieve it and have to remember to remove the temp table. Creating a view is easier as it will not need maintenance after that. However, you have to do one query in two places and you cannot create the query on the run. Is it at all possible to use just one query to get the desired results?
`Derived table` is the answer. The following is a working example from wfm (our server) database.
SELECT M.Case_id, S.close_date
FROM dbo.T_CASE AS M LEFT OUTER JOIN
(SELECT case_acc_id, MAX(close_date) AS close_date
FROM dbo.T_SUBCASE
GROUP BY case_acc_id) AS S ON M.Case_id = S.case_acc_id
Select * from (select case_id,open_date from tbl_data) AS S
It looked stupid to have such a query which does not help in anything. However, its very much more useful than just that.
For example. You wish to get the last close date from a subcase while retrieving a main case data. This could only be achieved if you create a view to get the last close date or create a temporary table to get the last close date. Once you can get the data from the view or temp table, you join it to main case to get the final result.
Creating temp table means you have to do a number of steps to achieve it and have to remember to remove the temp table. Creating a view is easier as it will not need maintenance after that. However, you have to do one query in two places and you cannot create the query on the run. Is it at all possible to use just one query to get the desired results?
`Derived table` is the answer. The following is a working example from wfm (our server) database.
SELECT M.Case_id, S.close_date
FROM dbo.T_CASE AS M LEFT OUTER JOIN
(SELECT case_acc_id, MAX(close_date) AS close_date
FROM dbo.T_SUBCASE
GROUP BY case_acc_id) AS S ON M.Case_id = S.case_acc_id
Subscribe to:
Posts (Atom)