1748219 Members
4619 Online
108759 Solutions
New Discussion юеВ

Ask for SQL statement

 
SOLVED
Go to solution
Printaporn_1
Esteemed Contributor

Ask for SQL statement

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.
enjoy any little thing in my life
8 REPLIES 8
LBertoglio
Advisor

Re: Ask for SQL statement

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.
Ryan Kogelheide
Frequent Advisor
Solution

Re: Ask for SQL statement

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
Printaporn_1
Esteemed Contributor

Re: Ask for SQL statement

thanks Ryan , it work.
but this case still open for more ideas.
enjoy any little thing in my life
Printaporn_1
Esteemed Contributor

Re: Ask for SQL statement

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.
enjoy any little thing in my life
Ryan Kogelheide
Frequent Advisor

Re: Ask for SQL statement

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...)





Ryan Kogelheide
Frequent Advisor

Re: Ask for SQL statement

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
Ryan Kogelheide
Frequent Advisor

Re: Ask for SQL statement

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)

Printaporn_1
Esteemed Contributor

Re: Ask for SQL statement

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



enjoy any little thing in my life