sql-problem 
Author Message
 sql-problem

Hi together,

I have a problem creating a query. Here is it:

I got a table with the following structure:

art-nr  pos     text
-----------------------------------
123     1       text1
123     2       text2
123     3       other
234     1       first
345     1       first
345     2       second
345     4       fourth

Now I have to create a query that should bring the following result:

art-nr  text1   text2   text3   text4
-------------------------------------------------------------
123     text1   text2   other
234     first
345     first   second          fourth

We use Informix SE 7.2.
In the end a Visual-Basic-Programm should run the query (via ODBC) and
write the result to an ascii-file.

Has anybody an idea for solving my problem?

Thanks

Eberhard



Sat, 20 Mar 2004 17:04:23 GMT
 sql-problem

For IDS-9.x this problem has a pretty elegant solution!

However for SE-7.2 you've to write a stored procedure.



Quote:
> Hi together,

> I have a problem creating a query. Here is it:

> I got a table with the following structure:

> art-nr pos text
> -----------------------------------
> 123 1 text1
> 123 2 text2
> 123 3 other
> 234 1 first
> 345 1 first
> 345 2 second
> 345 4 fourth

> Now I have to create a query that should bring the following result:

> art-nr text1 text2 text3 text4
> -------------------------------------------------------------
> 123 text1 text2 other
> 234 first
> 345 first second fourth

> We use Informix SE 7.2.
> In the end a Visual-Basic-Programm should run the query (via ODBC) and
> write the result to an ascii-file.

> Has anybody an idea for solving my problem?

> Thanks

> Eberhard



Sat, 20 Mar 2004 17:38:11 GMT
 sql-problem

comment embedded.

Quote:

>Hi together,

>I have a problem creating a query. Here is it:

>I got a table with the following structure:

>art-nr      pos     text
>-----------------------------------
>123 1       text1
>123 2       text2
>123 3       other
>234 1       first
>345 1       first
>345 2       second
>345 4       fourth

>Now I have to create a query that should bring the following result:

>art-nr      text1   text2   text3   text4
>-------------------------------------------------------------
>123 text1   text2   other
>234 first
>345 first   second          fourth

>We use Informix SE 7.2.
>In the end a Visual-Basic-Programm should run the query (via ODBC) and
>write the result to an ascii-file.

>Has anybody an idea for solving my problem?

Why not just do this progamatically instead of trying to make the database do
wierd things.

--kind of pseudo code start
Open cursor
fetch
last_art_nr=art-nr
print art_nr
while there are still records
do
  if art-nr!=last_art_nr
    print newline(or whatever)
    print art_nr
  fi
  print text1
  fetch
done
--kind of pseudo code end

Hope this helps
Will

+++
"I am not laughing at you mom, I am laughing with Will" -Celena Cox
+++



Sun, 21 Mar 2004 04:01:53 GMT
 sql-problem

Now I get to do something I really hate, ammend my post...(one step added)

P.S. I really do feel I trimmed everything which was not required to
understand my reply...

Will

Quote:

>comment embedded.

>>Hi together,

>>I have a problem creating a query. Here is it:

>>I got a table with the following structure:

>>art-nr  pos     text
>>-----------------------------------
>>123     1       text1
>>123     2       text2
>>123     3       other
>>234     1       first
>>345     1       first
>>345     2       second
>>345     4       fourth

>>Now I have to create a query that should bring the following result:

>>art-nr  text1   text2   text3   text4
>>-------------------------------------------------------------
>>123     text1   text2   other
>>234     first
>>345     first   second          fourth

>>We use Informix SE 7.2.
>>In the end a Visual-Basic-Programm should run the query (via ODBC) and
>>write the result to an ascii-file.

>>Has anybody an idea for solving my problem?

>Why not just do this progamatically instead of trying to make the database do
>wierd things.

do a select ordered by art-nr

- Show quoted text -

Quote:

>--kind of pseudo code start
>Open cursor
>fetch
>last_art_nr=art-nr
>print art_nr
>while there are still records
>do
>  if art-nr!=last_art_nr
>    print newline(or whatever)
>    print art_nr
>  fi
>  print text1
>  fetch
>done
>--kind of pseudo code end



Sun, 21 Mar 2004 05:01:25 GMT
 sql-problem

Quote:

> I have a problem creating a query. Here is it:
> I got a table with the following structure:

> art-nr  pos     text
> -----------------------------------
> 123     1       text1
> 123     2       text2
> 123     3       other
> 234     1       first
> 345     1       first
> 345     2       second
> 345     4       fourth

> Now I have to create a query that should bring the following result:

> art-nr  text1   text2   text3   text4
> -------------------------------------------------------------
> 123     text1   text2   other
> 234     first
> 345     first   second          fourth

> We use Informix SE 7.2.
> In the end a Visual-Basic-Programm should run the query (via ODBC) and
> write the result to an ascii-file.

> Has anybody an idea for solving my problem?

I'd probably do it programmatically, using ACE or whatever.  However, if
you insist on using SQL and as long as there are never more than 4 text
columns, then you can try:

SELECT t1.art_nr, t1.text, t2.text, t3.text, t4.text
    FROM TableA t1, OUTER TableA t2, OUTER TableA t3, OUTER TableA t4
   WHERE t1.art_nr = t2.art_nr AND t2.pos = 2
       AND t1.art_nr = t3.art_nr AND t3.pos = 3
       AND t1.art_nr = t4.art_nr AND t4.pos = 4
       AND t1.pos = 1
   ORDER BY t1.art_nr;

That hasn't been past an SQL optimizer, so it could have a syntax error
(or worse) in it, but the basic idea is correct.

Maybe Sergey can be persuaded to reveal his mysterious elegant solution
that works with a 9.x server.

--

Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
#include <disclaimer.h>



Sun, 21 Mar 2004 12:27:36 GMT
 sql-problem
Hi,

Thank you for your tips.
Unfortunately I got not four, but seven text-columns.
I think that's the reason why
the posted select-statements doesn't work.
Now I will try to write my first stored procedure - maybe I need your
help later once again ; )

Greetings
Eberhard

On Wed, 03 Oct 2001 04:27:36 GMT, Jonathan Leffler

Quote:


>> I have a problem creating a query. Here is it:
>> I got a table with the following structure:

>> art-nr  pos     text
>> -----------------------------------
>> 123     1       text1
>> 123     2       text2
>> 123     3       other
>> 234     1       first
>> 345     1       first
>> 345     2       second
>> 345     4       fourth

>> Now I have to create a query that should bring the following result:

>> art-nr  text1   text2   text3   text4
>> -------------------------------------------------------------
>> 123     text1   text2   other
>> 234     first
>> 345     first   second          fourth

>> We use Informix SE 7.2.
>> In the end a Visual-Basic-Programm should run the query (via ODBC) and
>> write the result to an ascii-file.

>> Has anybody an idea for solving my problem?

>I'd probably do it programmatically, using ACE or whatever.  However, if
>you insist on using SQL and as long as there are never more than 4 text
>columns, then you can try:

>SELECT t1.art_nr, t1.text, t2.text, t3.text, t4.text
>    FROM TableA t1, OUTER TableA t2, OUTER TableA t3, OUTER TableA t4
>   WHERE t1.art_nr = t2.art_nr AND t2.pos = 2
>       AND t1.art_nr = t3.art_nr AND t3.pos = 3
>       AND t1.art_nr = t4.art_nr AND t4.pos = 4
>       AND t1.pos = 1
>   ORDER BY t1.art_nr;

>That hasn't been past an SQL optimizer, so it could have a syntax error
>(or worse) in it, but the basic idea is correct.

>Maybe Sergey can be persuaded to reveal his mysterious elegant solution
>that works with a 9.x server.



Sat, 27 Mar 2004 22:02:15 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. SQL problem, MSDTC Problem or VB.NET problem?

2. sql problem SA problem urgent

3. SQL Problem: DISTINCT problem

4. datagrid problem or SQL problem?

5. SQL Server 6.5 latest service pack SQL problems

6. Access -> SQL Server SQL Problem

7. Oracle/SQL-Server SQL-Problem

8. Cool SQL problem - SQL for Temporal Aggregation

9. embedded SQL:Problem during compilation in embedded sql(MSSQL) with VC++

10. SQL or T-SQL problem

11. sql-problem ADO - MS Sql Server


 
Powered by phpBB® Forum Software