The Old Joel on Software Forum: Part 3 (of 5)

Stupid SQL Tricks

I know this probably isn't the best forum for this question, but here goes anyway...

I've got a table in my Oracle 8.1 database that has three columns: FieldName, FieldValue and ItemID.

The table holds arbitrary data and allows us to write queries such as:

SELECT FieldValue FROM tblData WHERE FieldName='Some Field'

Now the fun part...Several rows could be grouped together via the ItemID column. For instance, we might have one row where the FieldName='Field1' and another row where the FieldName='Field2', but both rows would have the same ItemID.

What we need to be able to do is run a query that would show the values for rows that have a FieldName of 'Field1' or 'Field2', but combine this result into a single row for the rows that have the same ItemID.

Does this make any sense?

Not a DBA
Tuesday, October 7, 2003

Why do you need a single row for the result?

Rick Childress
Tuesday, October 7, 2003

Can't help you in Oracle, but my first inclination with MSSQL (which may translate) would be:

1. Do a select to find your appropriate ID.
2. Do a select using the ID as the WHERE to find all the column names and valus.
3. Create a temp table, one column for each of the column names that came back, and populate the value.
4. Then select * from the temp table as your result.

Sort of an odd architecture, no? What motivated it?

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, October 7, 2003

Because they're trying hard to avoid using Oracle as a relational database. Someone had the bright idea that storing multiple two-dimensional relations in a one-dimensional structure would be more 'flexible', instead of doing:

CREATE TABLE ItemID1 (Field1, Field2, ...)
CREATE TABLE ItemID2 (Field1, Field2, ...)

which would give all sorts of benefits like joins and referential integrity, etc.

Common Sense
Tuesday, October 7, 2003

If I understand correctly, they should have a single table with fields
Field1
Field2
Field3
ItemID

Then they could just group on ItemID and have no problem. Sounds like someone got bit by the metadata bug, and now they're learning why metadata can really suck ass.

Philo

Philo
Tuesday, October 7, 2003

Tough crowd.

The reasoning that has been given is because they wouldn't always know what the fieldnames could be, so it's impossible to create a table that has the fieldnames as columns.

Their idea was to create a system that could store an arbitrary number of fields about a single entity, then return this data as a single row. Again, the fieldnames are not known, they could be anything.

Imagine if you wanted to store information about a person; furthermore let's say that you wanted to classify it under categories such as "Appearance", "Personality" and "Skills"
Now, under each category there could be any infinite number of fields and values added. Someone might add "eye color" with a value of "blue" under Appearance while someone else adds a field "C++" with a value of "low" under Skills. Then someone would be able to retrieve all the fields with their values about a particular person.

(This is not what the system does. It's used for a totally different purpose, but my example is easier than what it really does.)

It's looks as if the current system won't work. I'm free to redesign it, but what are some suggestions as to how to order the data?

Not a DBA
Tuesday, October 7, 2003

Once again....the fields are not known at design time. So in my example "eye color" this field isn't known when we design the system. A user has the freedom to add new fields.

Not a DBA
Tuesday, October 7, 2003

Just about every database (including, I'm sure Oracle) has a way of querying the table for its columns and associated data types. You can create your queries on the fly as needed.

From what I can tell, there's absolutely nothing added by the architecture that was selected, and quite a few down-sides, when compared to simply creating the database without a fixed schema, and querying the schema at runtime.

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, October 7, 2003

Sounds like you want an Access CROSSTAB to me.
These can be perfomed using CASE statements if you know the possible values of FieldName upfront. Or you can be very clever and use SQL to generate a SQL statement based on the dynamic contents of this field.

(digging around)

This link will prove very useful: http://www.sqlteam.com/item.asp?ItemID=2955

Good luck!

Tim H
Tuesday, October 7, 2003

"The reasoning that has been given is because they wouldn't always know what the fieldnames could be, so it's impossible to create a table that has the fieldnames as columns. "

If there was proper analysis done at the time you would have captured 95% of the fields. Like everyone have said there is huge drawbacks in this type of database construct. No relational integrity, no joins therfore hard to write queries, and column information buried in another level of abstraction.

If you have a fairly well populated database, you could extract all the current "columns" and build a properly normalized database.

DJ
Tuesday, October 7, 2003

Ditto on the crosstab thing. Here's an alternate link that might illuminate things for you.
The code given in the SQL Team article is slicker and more general purpose, but the code in this link may clarify what's going on:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20322180.html

Herbert Sitz
Tuesday, October 7, 2003

It's been a while (years!!) since I used Oracle, but I'm pretty sure the answer is to use Decode.

Look up some examples of its use and one of them will most likely be what you want here, its quite a common thing.

Just thiink of the Decode function as a multi-purpose toolkit, it does a lot. Sorry I can't help with specific syntax examples, but a quick Google should help.

Steve Jones (UK)
Tuesday, October 7, 2003

Thanks for the info everyone! (Even the ones that were a bit surly..)

Not a DBA
Tuesday, October 7, 2003

The Oracle package DBMS_SQL is your friend, you want to create a dynamic SQL query and run that.

Run a query to find out the number of defined fieldnames
and their given fieldnames for a given ITEM_ID.

The fieldnames for a given ITEM_ID needs to go in the dynamic query. Also you need the cout of defined fieldnames for a given ITEM_ID.

You want to create a query along the lines of this:

SELECT I1.FIELDNAME, I2.FIELDNAME , I3.FIELDNAME ....
FROM ITEMS I1, ITEMS I2, ITEMS I3....ITEMS In

So instead of selecting the column from the table multiple times, you create a query that joins with itself.

Next up you need to add a where clause that contains valid joins between the number of tables you get from
the fieldcount.

There is some PL/SQL fiddling involved to dynamically create a query, but it is certainly doable.

You want your query to look something like this:

SELECT I1.FIELDNAME, I2.FIELDNAME , I3.FIELDNAME
FROM ITEMS I1, ITEMS I2, ITEMS I3
WHERE I1.ITEMID=I2.ITEMID
AND I2.ITEMID=I3.ITEMID
AND I1.ITEMID=<item id>
AND I1.FIELDNAME='<your fieldname>'
AND I2.FIELDNAME='<your fieldname>'
AND I3.FIELDNAME='<your fieldname>'

HTH,

Patrik
Tuesday, October 7, 2003

I don't know if this will help, but it's a T-SQL procedure I wrote to convert a single-column select statement into comma-separated values. It sounds like you could do something similar with your setup:

/*
Outputs a comma-separated list from the input of a select statement.
The select statement must return only a single column

Use it like this:
declare @comma_list varchar(7900)
exec util_ToCSV 'SELECT top 100 brand from brands', 0, @comma_list OUTPUT
select @comma_list

-or-

exec util_ToCSV 'SELECT top 100 brand from brands', 1

*/
ALTER proc util_ToCSV
@select_statement varchar(7900)
, @return_as_recordset int = 0
, @comma_list varchar(7900) = NULL OUTPUT
AS
SET NOCOUNT ON
--DECLARE @comma_list varchar(7900)
create table #input_table (field_one varchar(7900))
insert #input_table exec (@select_statement)

SELECT @comma_list = COALESCE(@comma_list + ', ', '') + field_one
FROM #input_table

drop table #input_table
SET NOCOUNT OFF
IF @return_as_recordset = 1
SELECT @comma_list as comma_list

Troy King
Tuesday, October 7, 2003

I ask the same question as Rick Childress: Why do you need the result as a tablerow?

Can't you just add an abstraction layer on top of the rows which makes this resultset look like one object instance?

Thomas Eyde
Tuesday, October 7, 2003

Would a "pivot query" help?

check out>>
http://asktom.oracle.com/pls/ask/f?p=4950:8:9271425822190226391::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:766825833740,

will
Tuesday, October 7, 2003

This askTom "pivot table" first example (decode) is typical solution for problems like this. And typically this query constructed on the fly.

Second one (scalartype on) need better support from client-side app (object types support). Means more work usually.

Do not use lookup functions (proposed by reviewer) - this ususally slows things down.

msc
Tuesday, October 7, 2003

I actually had to code something relatively similar today. If you can create a temp table this isn't that difficult, otherwise you can use a REF cursor.

Basically your query (pl/sql, can't do it in straight sql) will run through and grab a list of all the ids, then use those ids one by one to grab the corresponding fields. You can use a varchar2(32767) to hold the result set per line and keep appending to it.

Alternatively if you can create a temp table (mind you you'll have to know the maximum number of instances of an ID) you can just populate it instead of compiling that result string per ID.

Its really hairy and can take a good amount of time. My code currently runs in approximately 1.5 minutes (yikes!) but I should be able to simplify it with some REF cursors to reduce the number of loops and get it down to 10 seconds or so.

Good luck.

Lou
Tuesday, October 7, 2003

You can create a nested table and store it in oracle. Have your nested table act as an associative array (hashtable) with name/value pairs. Then you can just get/set you values and display however you want.

Tom Vu
Tuesday, October 7, 2003

The universal answer is to create the required sql dynamically and then execute it.

If your db does not do crosstab queries, you will have to do a selfjoin for with the single column table a number of times equal to the number of columns desired.

You can still have good ref integrity with this kind of design and it allows for instance for historical values.

Karel
Wednesday, October 8, 2003

Why not store you metadata in the system tables and execute ADD COLUMN when you want the user to add a column?

John Ridout
Wednesday, October 8, 2003

"Can't you just add an abstraction layer on top of the rows which makes this resultset look like one object instance? "

Yes we can, and that is probably the route that we will take. It would be nice to get a single row just because that makes things a bit easier for us.

"Why not store you metadata in the system tables and execute ADD COLUMN when you want the user to add a column? "

For our system, that would be messy. We know the total number of potential fields; around 85,000 of them. So we could technically add them if we wanted, but we don't want to do that for a relatively small system. Likewise, adding the fields at runtime isn't any better because that requires a tighter coupling than what we wanted.

As I mentioned, I think we are going to go the route of just adding an abstraction layer on top of relatively simple queries. Just a few simple tests shows that this will be faster since we aren't torturing the database with our convoluted queries. With an abstraction layer, we can index one of the columns, write simple selects to grab a group of records and then have our code piece them together. All in all, that seems to be simpler and faster than other ways.

Once again, thanks to everyone who took the time to post and steer us in the right direction.

Not a DBA
Wednesday, October 8, 2003

I had a very similiar problem and someone was kind enough to show me how to do it:

select
max( decode( fieldname, 'fieldvalue1', value, null ) ) fieldvalue1,
max( decode( fieldname, 'fieldvalue2', value, null ) ) fieldvalue2,
max( decode( fieldname, 'fieldvalue3', value, null ) ) fieldvalue3
from tablename
where XXX = XXX
group by itemid

This will aggregate all of the rows where the fieldname column is equal to 'fieldvalue1','fieldvalue2','fieldvalue3'. Works like a champ for what we needed.

Mark Hoffman
Wednesday, October 8, 2003