Tag Archive for 'sql'

T-SQL – How to select multiple rows into a single row

I spend too long on finding the solution for this problem…

Basically I had a table which looked like this
table T SQL   How to select multiple rows into a single row

What I wanted was to select all as one row as a set of items separated with a comma, where an item was ‘id: name’. The items had to be sorted by the id. So the result was supposed to look as below:
result T SQL   How to select multiple rows into a single row

Continue reading ‘T-SQL – How to select multiple rows into a single row’

Summary of Communities to Communities (C2C) 2009 Conference

C2C is a history now. I attended .NET stream and one session on SQL. The conference as a whole was very interesting, well organised, and basically developing. It was a Polish conference so most sessions were conducted in Polish but there were a couple of speakers from abroad too, and they spoke in English.

Basically I’m happy I had a chance to attend the conference. Below are my notes and comments. Please note they are my comments; should I write something which is not true or accurate, please let me know, sometimes pace was fast so I might have skipped something icon smile Summary of Communities to Communities (C2C) 2009 Conference
Continue reading ‘Summary of Communities to Communities (C2C) 2009 Conference’

Exception – ORA-00604: error occurred at recursive SQL level 1 ORA-01003: no statement parsed

In my application data were collected from database (Oracle) using web services that called stored procedures. One of the stored procedure had SELECT defined similar to the one below:

WITH sth1 as 
(
	SELECT ...
),
sth2 as 
(
	SELECT ...
)
SELECT ...

This construction was the reason why in my application I was getting the following exception while invoking that web service:

Exception – ORA-00604: error occurred at recursive SQL level 1 ORA-01003: no statement parsed

Solution

The simplest solution for that is to create tables representing sth1 and sth2 created with WITH clause. Then instead of using WITH truncate those tables and fill with data. Finally query against them in the main SELECT.

Updated, 2008-10-22
It turns out if you use a regular table, you may have some problems with multiple calls to the stored procedure – the second call could overwrite the first call’s data with a second call to truncate.
Therefore use either:

 Exception   ORA 00604: error occurred at recursive SQL level 1 ORA 01003: no statement parsed

SQL: CONVERT to DATETIME

I’ve heard that SQL Server developers happen to have problems with CONVERT function when try to obtain a value of datetime type. Recently, I’ve had that problem during a Data Transformation Services (DTS) package implementation.

One of the task in that package was “Transform Data”, which transferred data between a source and destination. To retrieve that data, I used SQL query within which I needed to add a number of milliseconds to a given date. The following snippet shows what I really wanted to execute:

dateadd (millisecond, ?, convert(datetime,?))

In abovementioned snippet, both question marks (?) are different parameters used in that query. The former is sINTEGER (the number of millseconds) whereas the latter is the date kept as VARCHAR. The latter parameter needs convertion to DATETIME.

Unfortunately, I could’t achieve what I really wanted. When I put the abovemeantioned snippet in the query in “Transform Data Task Properties” window, I couldn’t even save it – got the following error: “… argument data type datetime is invalid for argument 2 of dateadd function”.

I spent some time analyzing what was going on. I ran thas query in Query Analyzer with hardcoded values and it was ok. Finally, I used Profiler tool to see what exactly happened when the task (and consequently that query) was run. That gave me the explanation: the second parameter was unnecessary converted by the engine which executed that package. After that convertion, the value of that parameter was wrong and finally was converted second time to a wrong DATE value.

The solution to that problem is quite easy: the described query can be put in a stored procedure which is executed during the transformation. The SQL engine had difficulties defining the data types of the parameters. Luckily, in a stored procedure, the data types are explicitly defined.

SELECT + JOIN

I learned SQL (basic and quite advanced stuff) at the univerity a few years ago. I’ve been using SQL commands quite often; it’s difficult to imagine an application or a web page without a database in the back-end.

Recently, I’ve discovered I remember little about using JOIN command in SELECTs. There are a few types of JOINs; however, I’ve been using mostly INNER JOIN. I decided to change that and refresh my knowledge in that field. This way, I created the examples queries and put them in the post you’re reading.

Continue reading ‘SELECT + JOIN’