Then we join with words, to start an exploration from each cell in each of the eight directions for all of the search words. Here we join cells with directions – giving us one row to process for each cell for each of the eight directions. The real heart of the query is this piece: from ( select c1.*, hor, ver, horizontal, vertical from cells c1 cross join directions ) cross join wordsstart with cell = substr(words.word,1,1)connect by prior col-hor = col and prior seq-ver = seq and prior hor = hor and prior ver = ver and level < length(words.word)+1 and cell = substr(words.word, level,1) Now that all preparations have been made – the inline views that prepared all data used in the query (and note that we do not query any data at all from any database table or view) – we can start looking for answers. Here of course we use the same ‘trick’ as before of generating rows on the fly by dynamically, inside the query, instantiating a collection and accessing it through the TABLE operator. We inject these names in the following way into our query:, words as ( select column_value word from table( word_list('italy', 'holland', 'poland' ,'france' ,'canada', 'japan','india','china','togo','peru' ) ) ) ![]() In the example, we are looking for the names of countries. The final in-line view we use is the one that provides the list of words to search for. Note that the directions in-line view returns all eight routes, both expressed in -1/0/1 notation as well as human-readable format. ![]() , direction as ( select distinct sign(rownum - 2) dir from ( select count(*) from dual group by cube (1,2) ) ), directions as ( select h.dir hor, v.dir ver, case h.dir when 1 then 'left' when -1 then 'right' end horizontal, case v.dir when -1 then 'down' when 1 then 'up' end vertical from direction h inner join direction v on ( not (h.dir=0 and v.dir=0) ) ) These directions are generated in another in-line view in a somewhat contrived way here is definitely room for improving the query. Note that they cannot both be 0, as that would mean no exploration at all. The parameters can be -1, 0 or 1 – meaning and respectively. Directions are defined by two parameters that specify the movement along the horizontal and the vertical axis. Going forward, we have to explore eight directions. This is relatively simple:, cells as ( select distinct seq, col, substr(txt, col, 1) cell from matrix cross join ( select rownum col from matrix ) ) The key to implementing this algoritm is an hierarchical query – as we shall see in a moment.įor the matrix in line view demonstrated above, our next step is an in-line view to visit all individual cells (letters) in the matrix.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |