query output format

[sarahw]

I have a number of tables from which I need to select. To try to explain myself simply, I have a table which holds data about PCs ie location, number. I have another table which holds data about each PC. However, different types of data ie IP Address, Make, Model etc are held within the same column within this table. How do I represent these as column headings within my query.

This is my code, which seems to select the right data but in an inappropriate format.

select distinct unit.unitid,name,itval,lrundate from unit,infoname,infotxt where
(unit.domainid=infotxt.domainid and unit.unitid=infotxt.unitid) and
(infotxt.domainid=infoname.domainid and infotxt.itnameid=infoname.itnameid)
and infotxt.itnameid in (72,93,216)
order by unit.unitid,name,itval,lrundate

If anyone can give me a clue I will be very grateful.

Kind regards

 

[grega]

Do you mean you need to split one database column into a number of separate report columns? If so, I guess there's some sort of [tt]substr(...)[/tt] command for your DB server ... so you could try something along the lines of ...
[tt]
select substr(CompCol, 1, 10) IP_Address, substr(CompCol, 11, 10) Location, substr(CompCol, 21, 5) Location

...

and so on
[/tt]

Hope that's some help. Obviously the neat solution would be to redesign your database so you don't have composite columns like that!

Greg.

 

[sarahw]

Unfortunately that won't work. Perhaps I haven't explained myself properly (although I'm not sure that I'll fair any better this time). The column name is itval,within the infotxt table, a PC will have 1 entry in the unit table and many entries in the infotxt table. The value of within itval is dependent on itnameid within infotxt. Infoname is a lookup table used to determine the type of data ie IP Address, Make, Model.

Thanks anyway - much appreciated

 

[grega]

ahhhh ... so you want multiple itval returned for each row of your query results (one for each unitid?

Don't think that can be done (at least I haven't seen it done so far) with straight SQL :-( Stuff like that .. I have to use temporay tables/cursors etc. Not Nice!

Greg.

 

[MalcolmW]

I suspect it may be possible with a derived table, but I am still not convinced I clearly understand the table structure.
Often it is easiest to communicate this type of information in DDL (database definition language) - it also is a curtesey to people who might work on the problem (they can quickly create some temp tables with the DDL to test their theories).
So - if you can define the problem in DDL, I'll be happy to look at it. Otherwise, I'd rather not spend time "solving" a problem I don't understand correctly. Assuming of course that I could solve the problem...

 

[MikeLacey]

ok.... assuming I understand the question correctly

how about something like this

Select PC_LOCATION, TBL_A.DESCRIPTION, TBL_B.DESCRIPTION
From
UNIT,
(
Select UNIT_CODE, DESCRIPTION
From UNIT_DETAILS
Where DETAILS_TYPE = 'A'
) TBL_A,
(
Select UNIT_CODE, DESCRIPTION
From UNIT_DETAILS
Where DETAILS_TYPE = 'B'
) TBL_B
Where UNIT.UNIT_CODE = TBL_A.UNIT_CODE
And UNIT.UNIT_CODE = TBL_B.UNIT_CODE

This is assuming two tables with a structure like this:

Table UNIT (
UNIT_CODE Number Primary Key,
PC_LOCATION Varchar2(80)
)

Table UNIT_DETAILS (
UNIT_CODE Number Foreign Key -> UNIT.UNIT_CODE,
DETAILS_TYPE Varchar2(1),
DESCRIPTION Varchar2(255)
) -- table is unique on UNIT_CODE and DETAILS_TYPE

This trick is called "virtual tables" I think and is quite useful. Oracle will build a temporary table for you based upon the select statement you define in the from list -- yuo can then join to that.

Quite resource hungry though.

Mike
michael.j.lacey@ntlworld.com

http://www.cargill.com/

 

[sarahw]

Mike

Thanks very much - that is exactly what I was looking for.

Sarah

 

[MikeLacey]

no probs <smile>

Mike
michael.j.lacey@ntlworld.com

http://www.cargill.com/