Fetch Data from Multiple Records

You can use function to fetch data from multiple records into one field. If for example we have table users and hobbies.


user_id
user_name
address
1 John Walker Bangladesh
2 Frank McLane Nepal
3 Leon Kennedy USA


user_id
hobby
1Reading
1 Sleeping
1 Walking
2 Swimming
3 Help People

If we're asked to display the user's name along with his/her hobbies, first we create a function to fetch the hobbies of a user into a single column. The function is as follows


CREATE FUNCTION [dbo].[get_hobbies]

(
@usr_id int
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @hobbies varchar(255)
DECLARE @temp varchar(255)

SET @hobbies = ''
SET @temp = ''

DECLARE HbCur CURSOR FOR SELECT hobby FROM users inner join hobbies on users.[user_id] = hobbies.[user_id] WHERE users.[user_id]=@usr_id

OPEN HbCur
FETCH next FROM HbCur INTO @temp
WHILE @@Fetch_Status = 0
BEGIN
SET @hobbies = @hobbies + @temp + ', '
FETCH next FROM HbCur INTO @temp
END


CLOSE HbCur
DEALLOCATE HbCur

--remove the last unnecessary comma
SET @hobbies = substring(@hobbies, 1, len(@hobbies)-1)

RETURN @hobbies
END


The function is quite self-explanatory. The body of the function starts after the keyword BEGIN and end at the END keyword. It takes the user_id as a parameter which will be used to select all hobbies of that user. Here, two variables are declared, @hobbies and @temp. These two variables are used to store the result of the hobbies queried. Next, we declare a CURSOR that will hold the result hobbies from the query, and then can be iterated to get each hobby. Next, there is a loop which will fetch every single hobby to the @temp variable which will then be appended to @hobbies variable that will hold all of the hobbies separated by comma. Last, the function returns all of the hobbies.


To test the function you can run the following sql


SELECT [dbo].[get_hobbies] (1)


This is the output when I run the query.




Read more...

ORACLE: Load Data from a File

If you're asked to load data from a file in Oracle, you can use the external tables. External tables allow you to query data from a file and treat it almost like a table (there are some limitations to external tables such as you can't create index, you can't INSERT or UPDATE an external tables). Let's practice through an example.


Let's say that we have this csv file named records.csv



76,10,0110,0.00,460008
77,10,0210,350000.00,460009
78,30,0110,0.00,430084
79,10,0110,500000.00,430085
80,30,0110,0.00,497014



The first step is to determine which folder you want this file to be put into. What you should concern here is that Oracle should have the read and write access right to the file. For this example, we create a new directory in 'C:\Data'. After physically creating the directory, we then create a directory in Oracle


CREATE DIRECTORY records_dir AS 'C:\Data';


Here, records_dir is the unique name of the directory, you can name you directory to something else.


Then we create the table which defines the external table. In the CREATE TABLE statement, we define what fields are in the external table, where is the directory for the external table, the filename, etc.. For this example we create a table named records_table


CREATE TABLE records_table (

record_no int,

code int,

message varchar2(4),

amount number(8,2),

message2 varchar(6)

)

ORGANIZATION EXTERNAL (

DEFAULT DIRECTORY records_dir

ACCESS PARAMETERS (

records delimited by newline

fields terminated by ','

)

LOCATION ('records.csv')

);


Because the file is a csv file, we define the terminating character for each field as ','. External table can also load data from a fixed column file.


We can then select the data from the external table by using the SELECT statement (you can also add the WHERE clause)


SELECT * FROM records_table


Here is the output example run from toad





You can then insert the data from an external table to your table.


CREATE TABLE in_records_table as

(SELECT * FROM records_table);


You can also set the REJECT LIMIT parameter of the external table. If Oracle found any error while processing a record, the record will be rejected. And if the number of rejected records exceeded the REJECT LIMIT parameter, your select statement will fail. You can change the REJECT LIMIT parameter by the statement


ALTER TABLE table_name REJECT LIMIT value


After processing the select statement of an external table, Oracle creates some files which are the bad file, discard file and the log file. The bad file contains all of the rejected records, while in the log file you can see Oracle activity when processing the external table like why a record was rejected. By default, these files are created in the same directory with the input file.


Another way to load data from a file to Oracle is by using the sqlldr command. You can see here for how to use the command.



Read more...

Findstr Command





Sometimes we need to count how many lines are there in a file. This could be easy if we only have to count all of the line/records in the file. But, we might need to count only records containing a certain string. We can do this with the DOS findstr and find command.


The syntax of the findstr command is


findstr "certain string" file.txt


Above is the simplest syntax to use findstr. You can see the full options list by typing /?. You can use /N to display also the line number in front of each line. You can use the /C command to search for contiguous string that contains space character. At the example above, the findstr will find every line that contain "search" or "string" in file.txt. If you use /C:"certain string", the findstr will find "certain string" in the file. You can also pipe the output to another command or redirect the output to another file.



I was once asked to count the number of records in a file of which transaction record is 200, the amount is zero, etc. Because the file is large, doing it manually will be tedious. Here's the findstr command form that I use.


findstr " 200 " file.txt | findstr /N " 0.00 " > out.txt


At the command above, I first search for the lines containing the string " 200 ", then I redirect the output to another findstr command that will search lines containing " 0.00 ". So, all the commands do is searching for lines that contain both " 200 " and " 0.00 ". In the end, the output is redirected to the out.txt file. Which after that, I can open out.txt to check for the result. With the /N option, the lines in out.txt will have line number.


I redirect the output to a file so that I can check and process further the output for another purpose. If you're sure all of the output lines are the line you want, you can pipe the output from findstr command to the find command by using the option /C.


findstr " 200 " file.txt | findstr " 0.00 " | find /C "200"



Read more...

SQL: Selecting Columns from Different Rows

There might be times when you have to show values from different records as a row. This could for reporting need and based on the design of the table in the database. Let's say that you have a table that looks like the following:

NameTax Type Number1Number2
Roy Raphael HousingNULL5000
Sam Murphy Medical1000NULL
Sam Muprhy HousingNULL3000
Roy Raphael Medical800NULL

If you want to display the sum all of the tax types for a single person in a row, one way to do this is by using the case keyword. This article assume that there's only two tax types in the table. So, no query for dynamic number of columns is covered. The expected result could be like the following:

NameHousingMedical
Roy Raphael 5000800
Sam Murphy 30001000


By using the case keyword, we can filter which data will be put to which column.


select sum(case when col1 = 'value' then col2 end) as ps-column

In the case above, if the Tax Type value is 'Housing' we put it in pseudo-column named Housing and so we do the same for the Medical tax. The end result of the query might be:


select [name], sum(case when [Tax Type] = 'Housing' then isnull(Cost, 0) end) as Housing, sum(case when [Tax Type] = 'Medical' then isnull(Cost, 0) end) as Medical from taxes group by [name]

The above sql language is a specific sql for SQL server. The isnull function is used so that the sum will just treat null values as 0.



Read more...
top