Re: Simple SQL Question

From: Pavan Muzumdar <pmuzumdar_at_mvsoftware.com>
Date: Tue, 20 Feb 2001 16:47:46 GMT
Message-ID: <Scxk6.10241$0u5.2463793@news1.rdc1.mi.home.com>

"Jeff Anderson" <jeanderso9_at_yahoo.com> wrote in message news:raji6.127909$9v2.1661044_at_quark.idirect.com...
> Is there a simple way to query a table whose records are formatted as
> follows:
>
> FIELD1 VARCHAR(12) - Ex. Data: NORTH
> FIELD2 VARCHAR(12) - Ex. Data: 1000
> FIELD3 VARCHAR(12) - Ex. Data: 2000
> FIELD4 VARCHAR(12) - Ex. Data: 3000
>
> with a result set that contains multiple records in the following format:
>
> NORTH 1000
> NORTH 2000
> NORTH 3000
With the disclaimer that I haven't tried this out, here's one way this might work:

Create a table "Counter" with one field:

    index Number

Insert into this table 3 rows, with values 1, 13, and 25 respectively.

In your select query, make sure that the where clause in your select retrieves all the rows that you need from the original table. So for example, in your case, the select query would return the one row:

NORTH, 1000, 2000, 3000. Now change the select statement and add to the From clause as follows:

SELECT
    Field1,
    RTRIM(SUBSTR

                    (RPAD(FIELD2, 12)||(RPAD(FIELD3, 12)||(RPAD(FIELD4,
12)),
                    Counter.index,
                    12))

FROM
    //other tables in list,
    Table1,
    Counter
WHERE
    ....

Basically you are joining with the "Counter" table, to get three rows returned per record in your original table. Then you are using the values in the counter table to get the appropriate substring of the concatenated values of the three other fields.

You need to check the syntax of the string functions since I haven't used them in a while. RPAD is used to convert your varchar(12) into a 12 character long string. RTRIM trims the extra spaces from the result.

Hope that helps,

Pavan Muzumdar Received on Tue Feb 20 2001 - 10:47:46 CST