topic Re: Ask for SQL statement in Operating System - HP-UX

Ask for SQL statement

Printaporn_1 — Tue, 26 Mar 2002 07:43:07 GMT

Hi,

I have data like this:
SN PN COMP_DATA_ITEM COMP_DATA_VALUE
===============================================TWWW2 PN1 DATECODE DATE1
TWWW2 PN1 LOTCODE LOT1
TWWW2 PN1 SUPPLY_CODE SUP1
TWWW2 PN1 PART_SERIAL PART1
TWWW2 PN2 DATECODE DATE2
TWWW2 PN2 LOTCODE LOT2
TWWW2 PN2 SUPPLY_CODE SUP2
TWWW2 PN2 PART_SERIAL PART2

and I want this output from sql:
SN PN DATECODE LOTCODE SUPPLY_CODE PART_SERIAL
==================================================
TWWW2 PN1 DATE1 LOT1 SUP1 PART1
TWWW2 PN2 DATE2 LOT2 SUP2 PART2

How can I write SQL , I am new with it.
appreciate your help and point will given.

Re: Ask for SQL statement

LBertoglio — Tue, 26 Mar 2002 08:06:23 GMT

Hi,
Which db are you using? Are you using oracle and sqlplus?
If so you can format columns with something like:
column format a20 (for varchar)
and
column format 99.9 (for numbers)
Regards.
L.B.

Re: Ask for SQL statement

Ryan Kogelheide — Tue, 26 Mar 2002 08:43:31 GMT

I'll assume an Oracle 8 or later DB.

You've got overnormalized data. So denormalize it first, then do an ordinary join.

Select MA.SN, MA.PN, DT.DATECODE,
LT.LOTCODE, SU.SUPPLY_CODE, PA.PART_SERIAL
From
(Select distinct SN, PN
From OVERN) MA,
(Select SN, PN, COMP_DATA_VALUE DATECODE
From OVERN
Where COMP_DATA_ITEM = 'DATECODE' ) DT,
(Select SN, PN, COMP_DATA_VALUE LOTCODE
From OVERN
Where COMP_DATA_ITEM = 'LOTCODE' ) LT,
(Select SN, PN, COMP_DATA_VALUE SUPPLY_CODE
From OVERN
Where COMP_DATA_ITEM = 'SUPPLY_CODE' ) SU,
(Select SN, PN, COMP_DATA_VALUE PART_SERIAL
From OVERN
Where COMP_DATA_ITEM = 'PART_SERIAL' ) PA
Where
MA.SN = DT.SN (+) and
MA.PN = DT.PN (+) and
MA.SN = LT.SN (+) and
MA.PN = LT.PN (+) and
MA.SN = SU.SN (+) and
MA.PN = SU.PN (+) and
MA.SN = PA.SN (+) and
MA.PN = PA.PN (+)

This assumes that the keys are SN and PN.

It's not going to be quick however. If you need it to be fast, do it one step at a time using insert for the MA and update after...

Have fun... ;-)

Ryan

Re: Ask for SQL statement

Printaporn_1 — Tue, 26 Mar 2002 09:20:52 GMT

thanks Ryan , it work.
but this case still open for more ideas.

Re: Ask for SQL statement

Printaporn_1 — Tue, 26 Mar 2002 10:22:12 GMT

Hi,

SOlution from Ryan is work at this current time
but in near future , if there is more than 4 COMP_DATA_ITEM other than DATECODE ,LOTCODE SUPPLY_CODE and PART_SERIAL , like user can add another COMP_DATA_ITEM and value.
then we need to change Ryan query ,
Do we have other solution not only limit to query.

thanks and appreciate.

Re: Ask for SQL statement

Ryan Kogelheide — Tue, 26 Mar 2002 11:14:00 GMT

I think I know what you mean, but you'll face a fundamental relational database issue in that the column selection of a view cannot be dynamic.

If you are using this query in an application rather than in a view, what you need to do is create a meta-query that creates the second query, or process the data into a crosstab using the native application code.

You could also redefine a view using some sort of periodic batch process.

Aside: In general it is not advisable to let users modify the attributes of application data. This is essentially what you are doing as each COMP_DATA_ITEM represents an attribute. I suspect that eventually the data will become "dirty" as users add attributes that were not intended by the designer. This could get especially bad if the users start adding attributes that have a many-to-one relationship with your key. (but then again, who knows...)

Re: Ask for SQL statement

Ryan Kogelheide — Tue, 26 Mar 2002 11:28:21 GMT

wadeekup...

There's another option we used for a similar problem in one of our applications.

Create a PL/SQL stored function (myvaluefunc) that takes your key as parameters and returns a string usable by your application code.

This string is the concatenation of all the possible fields (sorted how you like, perhaps with a sort field in another code table that controls the values of comp_data_item).

You also would need another function that would return the header fields. Then, create your view or select like:

Select
1 DISP_ORD, 'key1', 'key2', myheaderfunc(key1,key2) MyValues
From
Dual
Union Select
2 DISP_ORD, key1, key2, myvaluefunc(key1,key2) MyValues
From
OverNorm
Order by
DISP_ORD

Re: Ask for SQL statement

Ryan Kogelheide — Tue, 26 Mar 2002 11:31:38 GMT

khortoadkup...

the second part of the union in the above view needs only select the distinct values of overnorm, so instead of

from
overnorm

use

from
(select distinct key1, key2 from overnorm)

Re: Ask for SQL statement

Printaporn_1 — Wed, 27 Mar 2002 02:41:00 GMT

Hi Ryan,
Bon jour
Surprise that you know some of my language.
thanks a lot for your guideline , at least I can start.
Merci