- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Ask for SQL statement
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-25-2002 11:43 PM
тАО03-25-2002 11:43 PM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 12:06 AM
тАО03-26-2002 12:06 AM
Re: Ask for SQL statement
Which db are you using? Are you using oracle and sqlplus?
If so you can format columns with something like:
column
and
column
Regards.
L.B.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 12:43 AM
тАО03-26-2002 12:43 AM
SolutionYou'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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 01:20 AM
тАО03-26-2002 01:20 AM
Re: Ask for SQL statement
but this case still open for more ideas.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 02:22 AM
тАО03-26-2002 02:22 AM
Re: Ask for SQL statement
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 03:14 AM
тАО03-26-2002 03:14 AM
Re: Ask for SQL statement
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...)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 03:28 AM
тАО03-26-2002 03:28 AM
Re: Ask for SQL statement
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 03:31 AM
тАО03-26-2002 03:31 AM
Re: Ask for SQL statement
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-26-2002 06:41 PM
тАО03-26-2002 06:41 PM
Re: Ask for SQL statement
Bon jour
Surprise that you know some of my language.
thanks a lot for your guideline , at least I can start.
Merci