David

I signed up with dadoherty@...

It looks pretty good, but you are right, there has to be someone to share
with

David

On Thu, Nov 12, 2009 at 10:33 PM, David Gallardo <david@...> wrote:

>
>
> OK, I got some clarification on how many Skoot accounts I can give out
> for free, and it seems I have some discretion.
>
> First of all, if you are a current ST-J list member, sign up for a
> free trial account at http://www.skootit.com, send me the email
> address you signed up with, and I'll get it upgraded to a free
> permanent account.
>
> Second, since Skoot is really only useful if you know other people
> using it, I'm extending the offer to your friends and family, so you
> can share photos & videos amongst yourselves. If you get a free
> account, you will be able to invite up to 5 of your relatives or best
> friends to get a free account as well.
>
> @D
>
>
> On Thu, Nov 12, 2009 at 12:49 PM, David Gallardo
<dgallardo@...<dgallardo%40gmail.com>>
> wrote:
> > This week my team & I finally squeezed out the last few painful bugs
> > in the project we've been working on for the past year, our Skoot 3.0
> > client, and we released the Windows version last night!
> >
> > I think I've mentioned Skoot here in the past, but to remind everyone,
> > it basically is a file sharing program that lets you create
> > workspaces, invite people to them, and share files in them. It's sort
> > of like peer-to-peer, except it's buffered on the server-side, so you
> > don't have to be online at the same time.
> >
> > The new client is completely re-architected and features a
> > cross-platform synchronization core written in Java (the part I
> > designed & worked on). The UI is a native Windows component that
> > communicates with the core via HTTP/REST.
> >
> > One cool thing (I think anyway) is that we're going to publish the
> > core's REST API in the near future, so anybody will be able to use it
> > to build file transfer/sync applications or add file sync capabilities
> > to existing applications.
> >
> > If you're interested in checking it out, you can read about it and get
> > a free trial account here:
> >
> >   <http://www.skootit.com>
> >
> > If you like it, and think you might use it, send me an email and can
> > get the account upgraded to a free permanent account. If response is
> > overwhelming (by which I mean it might piss off my management) I'll
> > probably limit this to the first 10 folks who reply, with preference
> > given to those who post regularly to this list.
> >
> > (One caveat: I recommend avoiding the current OS X 2.3 client for now.
> > We'll be releasing a new 3.0 OS X version in the next month or two.)
> >
> > Also, if you are interested in checking out the API, contact me
> > privately and I can give you a preview. I'd be interested in getting
> > some feedback as we clean it up a little before going public.
> >
> > @D
> >
> > --
> > Follow me on twitter: djgallardo
> >
>
> --
> Follow me on twitter: djgallardo
>
>


[Non-text portions of this message have been removed]

Password requirements are not compatible with password maker :(

On Thu, Nov 12, 2009 at 7:33 PM, David Gallardo <david@...> wrote:

>
>
> OK, I got some clarification on how many Skoot accounts I can give out
> for free, and it seems I have some discretion.
>
> First of all, if you are a current ST-J list member, sign up for a
> free trial account at http://www.skootit.com, send me the email
> address you signed up with, and I'll get it upgraded to a free
> permanent account.
>
> Second, since Skoot is really only useful if you know other people
> using it, I'm extending the offer to your friends and family, so you
> can share photos & videos amongst yourselves. If you get a free
> account, you will be able to invite up to 5 of your relatives or best
> friends to get a free account as well.
>
> @D
>
>
> On Thu, Nov 12, 2009 at 12:49 PM, David Gallardo
<dgallardo@...<dgallardo%40gmail.com>>
> wrote:
> > This week my team & I finally squeezed out the last few painful bugs
> > in the project we've been working on for the past year, our Skoot 3.0
> > client, and we released the Windows version last night!
> >
> > I think I've mentioned Skoot here in the past, but to remind everyone,
> > it basically is a file sharing program that lets you create
> > workspaces, invite people to them, and share files in them. It's sort
> > of like peer-to-peer, except it's buffered on the server-side, so you
> > don't have to be online at the same time.
> >
> > The new client is completely re-architected and features a
> > cross-platform synchronization core written in Java (the part I
> > designed & worked on). The UI is a native Windows component that
> > communicates with the core via HTTP/REST.
> >
> > One cool thing (I think anyway) is that we're going to publish the
> > core's REST API in the near future, so anybody will be able to use it
> > to build file transfer/sync applications or add file sync capabilities
> > to existing applications.
> >
> > If you're interested in checking it out, you can read about it and get
> > a free trial account here:
> >
> >   <http://www.skootit.com>
> >
> > If you like it, and think you might use it, send me an email and can
> > get the account upgraded to a free permanent account. If response is
> > overwhelming (by which I mean it might piss off my management) I'll
> > probably limit this to the first 10 folks who reply, with preference
> > given to those who post regularly to this list.
> >
> > (One caveat: I recommend avoiding the current OS X 2.3 client for now.
> > We'll be releasing a new 3.0 OS X version in the next month or two.)
> >
> > Also, if you are interested in checking out the API, contact me
> > privately and I can give you a preview. I'd be interested in getting
> > some feedback as we clean it up a little before going public.
> >
> > @D
> >
> > --
> > Follow me on twitter: djgallardo
> >
>
> --
> Follow me on twitter: djgallardo
>
>
>



--
Curtis Cooley
curtis.cooley@...
home:http://curtiscooley.com
blog:http://ponderingobjectorienteddesign.blogspot.com
===============
Leadership is a potent combination of strategy and character. But if you
must be without one, be without the strategy.
-- H. Norman Schwarzkopf


[Non-text portions of this message have been removed]

The website also looks a little funky in Chromium. Not bad, but some of the
paragraphs get cut off at the bottom.

BTW, I signed up with my gmail address, curtis.cooley@...

On Fri, Nov 13, 2009 at 8:12 AM, Curtis Cooley <curtis.cooley@...>wrote:

> Password requirements are not compatible with password maker :(
>
>
> On Thu, Nov 12, 2009 at 7:33 PM, David Gallardo <david@...>wrote:
>
>>
>>
>> OK, I got some clarification on how many Skoot accounts I can give out
>> for free, and it seems I have some discretion.
>>
>> First of all, if you are a current ST-J list member, sign up for a
>> free trial account at http://www.skootit.com, send me the email
>> address you signed up with, and I'll get it upgraded to a free
>> permanent account.
>>
>> Second, since Skoot is really only useful if you know other people
>> using it, I'm extending the offer to your friends and family, so you
>> can share photos & videos amongst yourselves. If you get a free
>> account, you will be able to invite up to 5 of your relatives or best
>> friends to get a free account as well.
>>
>> @D
>>
>>
>> On Thu, Nov 12, 2009 at 12:49 PM, David Gallardo
<dgallardo@...<dgallardo%40gmail.com>>
>> wrote:
>> > This week my team & I finally squeezed out the last few painful bugs
>> > in the project we've been working on for the past year, our Skoot 3.0
>> > client, and we released the Windows version last night!
>> >
>> > I think I've mentioned Skoot here in the past, but to remind everyone,
>> > it basically is a file sharing program that lets you create
>> > workspaces, invite people to them, and share files in them. It's sort
>> > of like peer-to-peer, except it's buffered on the server-side, so you
>> > don't have to be online at the same time.
>> >
>> > The new client is completely re-architected and features a
>> > cross-platform synchronization core written in Java (the part I
>> > designed & worked on). The UI is a native Windows component that
>> > communicates with the core via HTTP/REST.
>> >
>> > One cool thing (I think anyway) is that we're going to publish the
>> > core's REST API in the near future, so anybody will be able to use it
>> > to build file transfer/sync applications or add file sync capabilities
>> > to existing applications.
>> >
>> > If you're interested in checking it out, you can read about it and get
>> > a free trial account here:
>> >
>> >   <http://www.skootit.com>
>> >
>> > If you like it, and think you might use it, send me an email and can
>> > get the account upgraded to a free permanent account. If response is
>> > overwhelming (by which I mean it might piss off my management) I'll
>> > probably limit this to the first 10 folks who reply, with preference
>> > given to those who post regularly to this list.
>> >
>> > (One caveat: I recommend avoiding the current OS X 2.3 client for now.
>> > We'll be releasing a new 3.0 OS X version in the next month or two.)
>> >
>> > Also, if you are interested in checking out the API, contact me
>> > privately and I can give you a preview. I'd be interested in getting
>> > some feedback as we clean it up a little before going public.
>> >
>> > @D
>> >
>> > --
>> > Follow me on twitter: djgallardo
>> >
>>
>> --
>> Follow me on twitter: djgallardo
>>
>>
>>
>
>
>
> --
> Curtis Cooley
> curtis.cooley@...
> home:http://curtiscooley.com
> blog:http://ponderingobjectorienteddesign.blogspot.com
> ===============
> Leadership is a potent combination of strategy and character. But if you
> must be without one, be without the strategy.
> -- H. Norman Schwarzkopf
>
>


--
Curtis Cooley
curtis.cooley@...
home:http://curtiscooley.com
blog:http://ponderingobjectorienteddesign.blogspot.com
===============
Leadership is a potent combination of strategy and character. But if you
must be without one, be without the strategy.
-- H. Norman Schwarzkopf


[Non-text portions of this message have been removed]

Hey,
I'm trying to track down who owns javanet.staxutils? Does anyone have
any ideas? I ask the list as I see a FishEye repository lists this
package and there are some Atlassian guys on the list.

Simon Mac Donald
http://hi.im/simonmacdonald

Guys,

I have run accross an XML encoding issue I don't know how to resolve. We are
needing to pass '<' and '>' in the data. We encode it as I would have
expected:
<Code>.DW9&lt;</Code>

What we are running into, is if I send XML as <Code>.DW9&lt;</Code> the
service receiving fails to recognize and convert the &lt; to a '<'.
If we send as <Code>.DW9<</Code>, the service receiving fails as it reads
the '<' as part of the tag.

I think the receiving service is messed up as they should read and convert
the &lt; to a '<'. Am I right or am I offbase and need to do something else?

Thanks,
Mica


[Non-text portions of this message have been removed]

Typically, when I run into this type of problem, I wrap the actual
text in CDATA.
I have not run into a situation where this did not work yet.

Rob Diana


On Sat, Nov 14, 2009 at 11:12 AM, Mica Cooper <mica.cooper@...> wrote:
> Guys,
>
> I have run accross an XML encoding issue I don't know how to resolve. We are
> needing to pass '<' and '>' in the data. We encode it as I would have
> expected:
> <Code>.DW9&lt;</Code>
>
> What we are running into, is if I send XML as <Code>.DW9&lt;</Code> the
> service receiving fails to recognize and convert the &lt; to a '<'.
> If we send as <Code>.DW9<</Code>, the service receiving fails as it reads
> the '<' as part of the tag.
>
> I think the receiving service is messed up as they should read and convert
> the &lt; to a '<'. Am I right or am I offbase and need to do something else?
>
> Thanks,
> Mica
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>
>

It sounds to me like the receiving service is just wrong if it isn't
'unencoding' the <.  This seems like basic functionality in any XML
parser.  Out of curiosity, have you tried any other encodings (like
&)?  I'd also experiment with adding spaces before/after the
encoding.

Bill

On Nov 14, 2009, at 11 59 0 , Robert Diana wrote:

> Typically, when I run into this type of problem, I wrap the actual
> text in CDATA.
> I have not run into a situation where this did not work yet.
>
> Rob Diana
>
> On Sat, Nov 14, 2009 at 11:12 AM, Mica Cooper
> <mica.cooper@...> wrote:
> > Guys,
> >
> > I have run accross an XML encoding issue I don't know how to
> resolve. We are
> > needing to pass '<' and '>' in the data. We encode it as I would
> have
> > expected:
> > <Code>.DW9&lt;</Code>
> >
> > What we are running into, is if I send XML as <Code>.DW9&lt;</
> Code> the
> > service receiving fails to recognize and convert the &lt; to a '<'.
> > If we send as <Code>.DW9<</Code>, the service receiving fails as
> it reads
> > the '<' as part of the tag.
> >
> > I think the receiving service is messed up as they should read and
> convert
> > the &lt; to a '<'. Am I right or am I offbase and need to do
> something else?
> >
> > Thanks,
> > Mica
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > _______________________________________________________
> > ST-J Wiki: http://tarasis.net/STWiki/
> > ST-J Members Map: http://www.frappr.com/stj
> >
> >
> >
> > .Yahoo! Groups Links
> >
> >
> >
> >
>
>



[Non-text portions of this message have been removed]

Clearly, the receiver is doing something wrong.
The XML code looks fine. Are they using some regexp-approach to parsing XML?

Rob's suggestion of using CData might help, but even then, it's still
only a workaround. You can't wrap every part of the data that might
possibly contain a '<' (or a single quote) in CData.

Geert Van Damme

Mica Cooper wrote:
>
>
> Guys,
>
> I have run accross an XML encoding issue I don't know how to resolve.
> We are
> needing to pass '<' and '>' in the data. We encode it as I would have
> expected:
> <Code>.DW9&lt;</Code>
>
> What we are running into, is if I send XML as <Code>.DW9&lt;</Code> the
> service receiving fails to recognize and convert the &lt; to a '<'.
> If we send as <Code>.DW9<</Code>, the service receiving fails as it reads
> the '<' as part of the tag.
>
> I think the receiving service is messed up as they should read and convert
> the &lt; to a '<'. Am I right or am I offbase and need to do something
> else?
>
> Thanks,
> Mica
>
> [Non-text portions of this message have been removed]
>
>


[Non-text portions of this message have been removed]

Why not? Just inspect every output line for offending characters and wrap
those in CDATA.
Also, multi-line fields would require the same.

On Sun, Nov 15, 2009 at 4:32 PM, Geert Van Damme
<geert.vandamme@...>wrote:

> Clearly, the receiver is doing something wrong.
> The XML code looks fine. Are they using some regexp-approach to parsing
> XML?
>
> Rob's suggestion of using CData might help, but even then, it's still
> only a workaround. You can't wrap every part of the data that might
> possibly contain a '<' (or a single quote) in CData.
>
> Geert Van Damme
>
> Mica Cooper wrote:
> >
> >
> > Guys,
> >
> > I have run accross an XML encoding issue I don't know how to resolve.
> > We are
> > needing to pass '<' and '>' in the data. We encode it as I would have
> > expected:
> > <Code>.DW9&lt;</Code>
> >
> > What we are running into, is if I send XML as <Code>.DW9&lt;</Code> the
> > service receiving fails to recognize and convert the &lt; to a '<'.
> > If we send as <Code>.DW9<</Code>, the service receiving fails as it reads
> > the '<' as part of the tag.
> >
> > I think the receiving service is messed up as they should read and
> convert
> > the &lt; to a '<'. Am I right or am I offbase and need to do something
> > else?
> >
> > Thanks,
> > Mica
> >
> > [Non-text portions of this message have been removed]
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>
>


[Non-text portions of this message have been removed]

The project page is here, which lists some owners:
https://stax-utils.dev.java.net/
http://wiki.java.net/bin/view/Javawsxml/StaxUtilsProject

It doesn't look like it has really been touched in the last two and a
half years?
http://fisheye5.cenqua.com/changelog/stax-utils/

=Matt

On Sat, Nov 14, 2009 at 6:18 AM, Simon MacDonald
<simon.macdonald@...> wrote:
> Hey,
> I'm trying to track down who owns javanet.staxutils? Does anyone have
> any ideas? I ask the list as I see a FishEye repository lists this
> package and there are some Atlassian guys on the list.
>
> Simon Mac Donald
> http://hi.im/simonmacdonald
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>
>

Thanks Matt.  I initially got confused as we have a version so old that the
classes we are using are no longer part of the package.  It is the
https://stax-utils.dev.java.net/ that originally created the package we are
using.   Looks like I get to re-write some code without looking :)

Simon Mac Donald
http://hi.im/simonmacdonald


On Sun, Nov 15, 2009 at 8:24 PM, Matt Quail <spud@...> wrote:

>
>
> The project page is here, which lists some owners:
> https://stax-utils.dev.java.net/
> http://wiki.java.net/bin/view/Javawsxml/StaxUtilsProject
>
> It doesn't look like it has really been touched in the last two and a
> half years?
> http://fisheye5.cenqua.com/changelog/stax-utils/
>
> =Matt
>
>
> On Sat, Nov 14, 2009 at 6:18 AM, Simon MacDonald
> <simon.macdonald@... <simon.macdonald%40gmail.com>> wrote:
> > Hey,
> > I'm trying to track down who owns javanet.staxutils? Does anyone have
> > any ideas? I ask the list as I see a FishEye repository lists this
> > package and there are some Atlassian guys on the list.
> >
> > Simon Mac Donald
> > http://hi.im/simonmacdonald
> >
> >
> > ------------------------------------
> >
> > _______________________________________________________
> > ST-J Wiki: http://tarasis.net/STWiki/
> > ST-J Members Map: http://www.frappr.com/stj
> >
> >
> >
> > .Yahoo! Groups Links
> >
> >
> >
> >
>
>
>


[Non-text portions of this message have been removed]

Anyone know what's the correct magic Java generics incantation to use to
get this to compile?


import java.util.List;
import java.util.SortedMap;
import java.util.TreeMap;

public class GenericsProblem<K> {

	 public GenericsProblem() {
		 SortedMap<K,List<String>> shardToServerMappings = new
TreeMap<K,List<String>>();
		 SortedMap<String,List<K>> serverToShardMappings = new
TreeMap<String,List<K>>();

		 performCommonOperation(shardToServerMappings);
		 performCommonOperation(serverToShardMappings);
	 }

	 private void performCommonOperation(SortedMap<?, List<?>> test) {
	 }
}


Thanks,

DR

On 11/18/09 10:48 AM, David Rosenstrauch wrote:
> Anyone know what's the correct magic Java generics incantation to use to
> get this to compile?
>
>
> import java.util.List;
> import java.util.SortedMap;
> import java.util.TreeMap;
>
> public class GenericsProblem<K>  {
>
>  public GenericsProblem() {
> 	 SortedMap<K,List<String>>  shardToServerMappings = new
> TreeMap<K,List<String>>();
> 	 SortedMap<String,List<K>>  serverToShardMappings = new
> TreeMap<String,List<K>>();
>
> 	 performCommonOperation(shardToServerMappings);
> 	 performCommonOperation(serverToShardMappings);
>  }
>
>  private void performCommonOperation(SortedMap<?, List<?>>  test) {
>  }
> }


This works for me:

public class GenericsProblem {

	 public GenericsProblem() {
		 SortedMap shardToServerMappings = new TreeMap();
		 SortedMap serverToShardMappings = new TreeMap();

		 performCommonOperation(shardToServerMappings);
		 performCommonOperation(serverToShardMappings);
	 }

	 private void performCommonOperation(SortedMap test) {
	 }
}


[Sorry, I couldn't resist]

Declaring the method like this works for me:

private <L, M> void performCommonOperation(SortedMap<L, List<M>> test) {
}

@D

On Wed, Nov 18, 2009 at 8:30 AM, Eric Rizzo <eric.rizzo.stj@...>wrote:

>
>
> On 11/18/09 10:48 AM, David Rosenstrauch wrote:
> > Anyone know what's the correct magic Java generics incantation to use to
> > get this to compile?
> >
> >
> > import java.util.List;
> > import java.util.SortedMap;
> > import java.util.TreeMap;
> >
> > public class GenericsProblem<K> {
> >
> > public GenericsProblem() {
> > SortedMap<K,List<String>> shardToServerMappings = new
> > TreeMap<K,List<String>>();
> > SortedMap<String,List<K>> serverToShardMappings = new
> > TreeMap<String,List<K>>();
> >
> > performCommonOperation(shardToServerMappings);
> > performCommonOperation(serverToShardMappings);
> > }
> >
> > private void performCommonOperation(SortedMap<?, List<?>> test) {
> > }
> > }
>
> This works for me:
>
> public class GenericsProblem {
>
> public GenericsProblem() {
> SortedMap shardToServerMappings = new TreeMap();
> SortedMap serverToShardMappings = new TreeMap();
>
> performCommonOperation(shardToServerMappings);
> performCommonOperation(serverToShardMappings);
> }
>
> private void performCommonOperation(SortedMap test) {
> }
> }
>
> [Sorry, I couldn't resist]
>
>
>



--
Follow me on twitter: djgallardo


[Non-text portions of this message have been removed]

You are the man!!!!  Thanks so much!

DR

On 11/18/2009 11:52 AM, David Gallardo wrote:
> Declaring the method like this works for me:
>
> private <L, M> void performCommonOperation(SortedMap<L, List<M>> test) {
> }
>
> @D
>
> On Wed, Nov 18, 2009 at 8:30 AM, Eric Rizzo <eric.rizzo.stj@...>wrote:
>
>>
>> On 11/18/09 10:48 AM, David Rosenstrauch wrote:
>>> Anyone know what's the correct magic Java generics incantation to use to
>>> get this to compile?
>>>
>>>
>>> import java.util.List;
>>> import java.util.SortedMap;
>>> import java.util.TreeMap;
>>>
>>> public class GenericsProblem<K> {
>>>
>>> public GenericsProblem() {
>>> SortedMap<K,List<String>> shardToServerMappings = new
>>> TreeMap<K,List<String>>();
>>> SortedMap<String,List<K>> serverToShardMappings = new
>>> TreeMap<String,List<K>>();
>>>
>>> performCommonOperation(shardToServerMappings);
>>> performCommonOperation(serverToShardMappings);
>>> }
>>>
>>> private void performCommonOperation(SortedMap<?, List<?>> test) {
>>> }
>>> }

--- In straight_talking_java@yahoogroups.com, Eric Rizzo <eric.rizzo.stj@...>
wrote:
>
> I went to a Spring presentation at the local JUG a couple of months ago.
> During the part about spring MVC (which looks a lot nicer to use than
> Struts BTW), the SpringSource guy made a big deal about using
> annotations to define what URLs map to what action methods. I was like,
> "Really? You think hard-coding URLs into your compiled classes is better
> than an external definition? You really want to recompile to alter a URL
> path?" He kind of looked at me like I had green and purple lizards
> crawling out of ears when I suggested that there are environments were
> that will never fly. He finally admitted that there was another way to
> map URLs to action methods, but refused to talk about how to do that and
> insisted that "99% of users prefer annotations for that."
>

Coming to this almost a month past ;)  That Spring guy is a doofus.  I've never
used annotations for Spring MVC and agree with you Eric.  And I don't think that
99% of users prefer annotations for that given that doing annotations for that
is relatively new for Spring MVC vs. the age of Spring itself.  The mapping for
the MultiActionController is easy.  The guy should have showed that *first* and
then mentioned the annotations.

Annotations have their place to be certain....that isn't one of them.

Jason

I finally have invites.  Contact me directly if you are still want to get on
the wave.

Simon Mac Donald
http://hi.im/simonmacdonald


On Tue, Oct 6, 2009 at 2:21 PM, Gary Udstrand <gudstrand.list@...>wrote:

>
>
> Not trying to be impatient, but I guess maybe I am. :-) Has anyone that
> was added to the invite list received anything from Google? Just anxious to
> kick the tires of Wave....
>
> --
> Gary
> http://www.twigsandtracks.com
> Twigs snap and tracks fade, a photograph reacquaints
>
> [Non-text portions of this message have been removed]
>
>
>


[Non-text portions of this message have been removed]

On Wed, Nov 25, 2009 at 8:22 AM, Simon MacDonald
<simon.macdonald@...>wrote:

> I finally have invites.  Contact me directly if you are still want to get
> on
> the wave.
>
>
I have some, too. If anyone still needs one, you can hit me up as well.

Joey

--
Blog: http://joeygibson.com
Twitter: http://twitter.com/joeygibson
FriendFeed: http://friendfeed.com/joeygibson
Facebook: http://facebook.com/joeygibson


[Non-text portions of this message have been removed]

I've got a table that acts like a name/value map of values for Products
in the domain. It looks like this:

ProductID | FieldName | FieldValue
---------------------------------------------
1  | WEB1  | A Fancy Widget
1  | WEB2  | 5.00
1  | WEB3  | 4.8 x 12.0 x 6.2
2  | WEB1  | A Not-so-fancy Widget
2  | WEB2  | 3.99
2  | WEB3  | 8 x 3.4 x 5.0
etc...

ProductID is a foreign key that uniquely identifies a Product. Each
Product has multiple rows in this table, one for each FieldName.
In this example data, WEB1 means "Product Description," WEB2 means
"Price," and WEB3 means "Dimensions."
(Don't ask why the database is this way; it's way out of my control and
can't change the structure)

I need a query that will load all Products, selecting each of the 3
kinds of fields for each unique ProductID as one row in the result set.
For example, I'd like to get this as a result set:

1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'

I need a query that will produce that result set, but this is waaaay
beyond my humble SQL skills; an hour's worth of Googling hasn't helped much.

Thanks in advance,
Eric

On 11/25/2009 04:40 PM, Eric Rizzo wrote:
> I've got a table that acts like a name/value map of values for Products
> in the domain. It looks like this:
>
> ProductID | FieldName | FieldValue
> ---------------------------------------------
> 1  | WEB1  | A Fancy Widget
> 1  | WEB2  | 5.00
> 1  | WEB3  | 4.8 x 12.0 x 6.2
> 2  | WEB1  | A Not-so-fancy Widget
> 2  | WEB2  | 3.99
> 2  | WEB3  | 8 x 3.4 x 5.0
> etc...
>
> ProductID is a foreign key that uniquely identifies a Product. Each
> Product has multiple rows in this table, one for each FieldName.
> In this example data, WEB1 means "Product Description," WEB2 means
> "Price," and WEB3 means "Dimensions."
> (Don't ask why the database is this way; it's way out of my control and
> can't change the structure)
>
> I need a query that will load all Products, selecting each of the 3
> kinds of fields for each unique ProductID as one row in the result set.
> For example, I'd like to get this as a result set:
>
> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
>
> I need a query that will produce that result set, but this is waaaay
> beyond my humble SQL skills; an hour's worth of Googling hasn't helped much.
>
> Thanks in advance,
> Eric

Not sure off the top of my head either.  Record aggregation like this is
not very easy to do in SQL - at least not with a single SQL statement.
So probably the easiest way to do this is to use a temp table - with the
caveat that it would be multiple SQL statements.  (Don't know if that
matters or not.)  i.e.,:

CREATE TABLE TEMP_TABLE (
	 ProductID int,
	 Web1 varchar(50),
	 Web2 varchar(50),
	 Web3 varchar(50)
)

INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
FROM other_table WHERE FieldName = "WEB1"

UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")

UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")

SELECT * FROM TEMP_TABLE;

DROP TABLE TEMP_TABLE;


Or some such.

HTH,

DR

Honestly, the simplest approach is going to be getting the data in the
basic table format
and then converting it into your business object on the Java side. SQL
is terrible at this
kind of thing and most of the time people will create a denormalized
version of the table
in order to do the single query.

Is there a possibility that you can convert to a flatter table using a
db trigger or nightly
batch process?

Rob Diana


On Wed, Nov 25, 2009 at 4:58 PM, David Rosenstrauch <darose@...> wrote:
> On 11/25/2009 04:40 PM, Eric Rizzo wrote:
>> I've got a table that acts like a name/value map of values for Products
>> in the domain. It looks like this:
>>
>> ProductID     | FieldName     | FieldValue
>> ---------------------------------------------
>> 1             | WEB1          | A Fancy Widget
>> 1             | WEB2          | 5.00
>> 1             | WEB3          | 4.8 x 12.0 x 6.2
>> 2             | WEB1          | A Not-so-fancy Widget
>> 2             | WEB2          | 3.99
>> 2             | WEB3          | 8 x 3.4 x 5.0
>> etc...
>>
>> ProductID is a foreign key that uniquely identifies a Product. Each
>> Product has multiple rows in this table, one for each FieldName.
>> In this example data, WEB1 means "Product Description," WEB2 means
>> "Price," and WEB3 means "Dimensions."
>> (Don't ask why the database is this way; it's way out of my control and
>> can't change the structure)
>>
>> I need a query that will load all Products, selecting each of the 3
>> kinds of fields for each unique ProductID as one row in the result set.
>> For example, I'd like to get this as a result set:
>>
>> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
>> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
>>
>> I need a query that will produce that result set, but this is waaaay
>> beyond my humble SQL skills; an hour's worth of Googling hasn't helped much.
>>
>> Thanks in advance,
>> Eric
>
> Not sure off the top of my head either.  Record aggregation like this is
> not very easy to do in SQL - at least not with a single SQL statement.
> So probably the easiest way to do this is to use a temp table - with the
> caveat that it would be multiple SQL statements.  (Don't know if that
> matters or not.)  i.e.,:
>
> CREATE TABLE TEMP_TABLE (
>        ProductID int,
>        Web1 varchar(50),
>        Web2 varchar(50),
>        Web3 varchar(50)
> )
>
> INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
> FROM other_table WHERE FieldName = "WEB1"
>
> UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
>
> UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
>
> SELECT * FROM TEMP_TABLE;
>
> DROP TABLE TEMP_TABLE;
>
>
> Or some such.
>
> HTH,
>
> DR
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>
>

Is this MySQL?

In that case, I guess you can simply use group-Concat()
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-co\
ncat

Maybe you need to create a view first that concatenates the FieldName
and FieldValue in a single column.


I think SQL server has a similar function.


Geert Van Damme

Eric Rizzo wrote:
>
>
> I've got a table that acts like a name/value map of values for Products
> in the domain. It looks like this:
>
> ProductID | FieldName | FieldValue
> ---------------------------------------------
> 1 | WEB1 | A Fancy Widget
> 1 | WEB2 | 5.00
> 1 | WEB3 | 4.8 x 12.0 x 6.2
> 2 | WEB1 | A Not-so-fancy Widget
> 2 | WEB2 | 3.99
> 2 | WEB3 | 8 x 3.4 x 5.0
> etc...
>
> ProductID is a foreign key that uniquely identifies a Product. Each
> Product has multiple rows in this table, one for each FieldName.
> In this example data, WEB1 means "Product Description," WEB2 means
> "Price," and WEB3 means "Dimensions."
> (Don't ask why the database is this way; it's way out of my control and
> can't change the structure)
>
> I need a query that will load all Products, selecting each of the 3
> kinds of fields for each unique ProductID as one row in the result set.
> For example, I'd like to get this as a result set:
>
> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
>
> I need a query that will produce that result set, but this is waaaay
> beyond my humble SQL skills; an hour's worth of Googling hasn't helped
> much.
>
> Thanks in advance,
> Eric
>
>


[Non-text portions of this message have been removed]

Hi Eric,
      Do you really have to make this into a result set?  Can't you
have the code querying the database build the records you need after
the query (perhaps taking advantage of sorting to simplify that)?  I
agree with David about this not being possible in a single statement,
unless the DB offers some non-standard functionality to help with
that.  It hurts my head to think about it.  Sorry, not much help.

Bill

On Nov 25, 2009, at 11 59 0 , Eric Rizzo wrote:

> I've got a table that acts like a name/value map of values for
> Products
> in the domain. It looks like this:
>
> ProductID | FieldName | FieldValue
> ---------------------------------------------
> 1  | WEB1  | A Fancy Widget
> 1  | WEB2  | 5.00
> 1  | WEB3  | 4.8 x 12.0 x 6.2
> 2  | WEB1  | A Not-so-fancy Widget
> 2  | WEB2  | 3.99
> 2  | WEB3  | 8 x 3.4 x 5.0
> etc...
>
> ProductID is a foreign key that uniquely identifies a Product. Each
> Product has multiple rows in this table, one for each FieldName.
> In this example data, WEB1 means "Product Description," WEB2 means
> "Price," and WEB3 means "Dimensions."
> (Don't ask why the database is this way; it's way out of my control
> and
> can't change the structure)
>
> I need a query that will load all Products, selecting each of the 3
> kinds of fields for each unique ProductID as one row in the result
> set.
> For example, I'd like to get this as a result set:
>
> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
>
> I need a query that will produce that result set, but this is waaaay
> beyond my humble SQL skills; an hour's worth of Googling hasn't
> helped much.
>
> Thanks in advance,
> Eric
>
>
>



[Non-text portions of this message have been removed]

I would write a function in PL/SQL that did the transformation into a
'temp_table' (i.e. a set of variables) and populated a 'type' which is a
data structure which can be returned as a table from the function.

This function can often be wired up in hibernate to look like a normal
table. Or you can just call it with raw SQL over JDBC.

Here's an example function that shows how to

1. take arguments
2. return a 'type'
3. use a cursor and a rowtype
4. declare some variables
5. loop around each row in the cursor and read into the row type.
6.read the rows into the 'type'
(type sms_details_table which is what is returned is declared as a type of
sms_details)

finally I would shoot the person who designed the table as a fsking map.
what a retarded data structure, even in OO-land.




function f_student_list            (p_aos_code        in      varchar2,
                                     p_aos_period      in      varchar2,
                                     p_acad_period     in      varchar2)
return sms_details_table  PIPELINED
is

cursor c_students_details is

select distinct b.student_id, b.forename, b.surname, b.mobile_no,
b.email_address
from dummy_sms_details b;

r_students_details   c_students_details%rowtype;

v_rec_type      varchar2(8) := 'Student';
v_student_id    varchar2(11);
v_forename      varchar2(50);
v_surname       varchar2(50);
v_mobile        varchar2(15);
v_email         varchar2(80);


begin

     open c_students_details;
     loop
     fetch c_students_details into r_students_details;
     exit when c_students_details%notfound;

         v_student_id    := r_students_details.student_id;
         v_forename      := r_students_details.forename;
         v_surname       := r_students_details.surname;
         v_mobile        := r_students_details.mobile_no;
         v_email         := r_students_details.email_address;

     PIPE ROW (sms_details(v_student_id, v_forename, v_surname, v_mobile,
v_email, v_rec_type));
     end loop;
     close c_students_details;
end f_student_list;
end sms_pkg;





2009/11/26 David Rosenstrauch <darose@...>

> On 11/25/2009 04:40 PM, Eric Rizzo wrote:
> > I've got a table that acts like a name/value map of values for Products
> > in the domain. It looks like this:
> >
> > ProductID     | FieldName     | FieldValue
> > ---------------------------------------------
> > 1             | WEB1          | A Fancy Widget
> > 1             | WEB2          | 5.00
> > 1             | WEB3          | 4.8 x 12.0 x 6.2
> > 2             | WEB1          | A Not-so-fancy Widget
> > 2             | WEB2          | 3.99
> > 2             | WEB3          | 8 x 3.4 x 5.0
> > etc...
> >
> > ProductID is a foreign key that uniquely identifies a Product. Each
> > Product has multiple rows in this table, one for each FieldName.
> > In this example data, WEB1 means "Product Description," WEB2 means
> > "Price," and WEB3 means "Dimensions."
> > (Don't ask why the database is this way; it's way out of my control and
> > can't change the structure)
> >
> > I need a query that will load all Products, selecting each of the 3
> > kinds of fields for each unique ProductID as one row in the result set.
> > For example, I'd like to get this as a result set:
> >
> > 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
> > 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
> >
> > I need a query that will produce that result set, but this is waaaay
> > beyond my humble SQL skills; an hour's worth of Googling hasn't helped
> much.
> >
> > Thanks in advance,
> > Eric
>
> Not sure off the top of my head either.  Record aggregation like this is
> not very easy to do in SQL - at least not with a single SQL statement.
> So probably the easiest way to do this is to use a temp table - with the
> caveat that it would be multiple SQL statements.  (Don't know if that
> matters or not.)  i.e.,:
>
> CREATE TABLE TEMP_TABLE (
>        ProductID int,
>        Web1 varchar(50),
>        Web2 varchar(50),
>        Web3 varchar(50)
> )
>
> INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
> FROM other_table WHERE FieldName = "WEB1"
>
> UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
>
> UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
>
> SELECT * FROM TEMP_TABLE;
>
> DROP TABLE TEMP_TABLE;
>
>
> Or some such.
>
> HTH,
>
> DR
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>
>


--
let x=x - http://crazymcphee.net/x/
xray dubs - http://autonomous.org/music/


[Non-text portions of this message have been removed]

create table  product_data (
  productid int,
  description varchar2(50),
  price varchar2(50),
  dimensions varchar2(50)
)


>
> INSERT INTO product_data (ProductID, description) SELECT ProductID,
> FieldValue
> FROM other_table WHERE FieldName = "WEB1"
>
> UPDATE product_data t SET price = (SELECT FieldValue FROM other_table o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
>
> UPDATE product_data t SET dimensions = (SELECT FieldValue FROM other_table
> o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
>
> SELECT * FROM product_data;
>
> DROP TABLE ORIGINAL_TABLE_NAME;



That's much better.

--
let x=x - http://crazymcphee.net/x/
xray dubs - http://autonomous.org/music/


[Non-text portions of this message have been removed]

Try this:

SELECT w1.FieldValue as WEB1, w2.FieldValue as WEB2, w3.FieldValue as
WEB3, w4.FieldValue as WEB4
FROM mytable w1
   JOIN mytable w2 ON (w1.ProductID = w2.ProductID)
   JOIN mytable w3 ON (w2.ProductID = w3.ProductID)
   JOIN mytable w4 ON (w3.ProductID = w4.ProductID)
WHERE w1.FieldName = 'WEB1'
   AND w2.FieldName = 'WEB2'
   AND w3.FieldName = 'WEB3'
   AND w4.FieldName = 'WEB4'

Actually added a fourth column. Below the table it works with on MySQL
5.0.33.:
--
-- Table structure for table `mytable`
--

CREATE TABLE `mytable` (
   `ProductID` int(11) unsigned NOT NULL,
   `FieldName` char(4) collate latin1_general_ci NOT NULL,
   `FieldValue` varchar(64) collate latin1_general_ci NOT NULL,
   PRIMARY KEY  (`ProductID`,`FieldName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `mytable`
--

INSERT INTO `mytable` VALUES (1, 'WEB1', 'A fancy Widget');
INSERT INTO `mytable` VALUES (1, 'WEB2', '5.00');
INSERT INTO `mytable` VALUES (1, 'WEB3', '4.8 x 12.0 x 6.2');
INSERT INTO `mytable` VALUES (1, 'WEB4', 'Comments can be helpful');
INSERT INTO `mytable` VALUES (2, 'WEB1', 'Another fancy Widget');
INSERT INTO `mytable` VALUES (2, 'WEB2', '15.00');
INSERT INTO `mytable` VALUES (2, 'WEB3', '14.8 x 18.0 x 16.2');
INSERT INTO `mytable` VALUES (2, 'WEB4', 'Some comments suck.');

HTH

Gunter

Robert Diana wrote:

>Honestly, the simplest approach is going to be getting the data in the
>basic table format
>and then converting it into your business object on the Java side. SQL
>is terrible at this
>kind of thing and most of the time people will create a denormalized
>version of the table
>in order to do the single query.
>
>Is there a possibility that you can convert to a flatter table using a
>db trigger or nightly
>batch process?
>
>Rob Diana
>
>
>On Wed, Nov 25, 2009 at 4:58 PM, David Rosenstrauch <darose@...> wrote:
>
>
>>On 11/25/2009 04:40 PM, Eric Rizzo wrote:
>>
>>
>>>I've got a table that acts like a name/value map of values for Products
>>>in the domain. It looks like this:
>>>
>>>ProductID     | FieldName     | FieldValue
>>>---------------------------------------------
>>>1             | WEB1          | A Fancy Widget
>>>1             | WEB2          | 5.00
>>>1             | WEB3          | 4.8 x 12.0 x 6.2
>>>2             | WEB1          | A Not-so-fancy Widget
>>>2             | WEB2          | 3.99
>>>2             | WEB3          | 8 x 3.4 x 5.0
>>>etc...
>>>
>>>ProductID is a foreign key that uniquely identifies a Product. Each
>>>Product has multiple rows in this table, one for each FieldName.
>>>In this example data, WEB1 means "Product Description," WEB2 means
>>>"Price," and WEB3 means "Dimensions."
>>>(Don't ask why the database is this way; it's way out of my control and
>>>can't change the structure)
>>>
>>>I need a query that will load all Products, selecting each of the 3
>>>kinds of fields for each unique ProductID as one row in the result set.
>>>For example, I'd like to get this as a result set:
>>>
>>>1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
>>>2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
>>>
>>>I need a query that will produce that result set, but this is waaaay
>>>beyond my humble SQL skills; an hour's worth of Googling hasn't helped much.
>>>
>>>Thanks in advance,
>>>Eric
>>>
>>>
>>Not sure off the top of my head either.  Record aggregation like this is
>>not very easy to do in SQL - at least not with a single SQL statement.
>>So probably the easiest way to do this is to use a temp table - with the
>>caveat that it would be multiple SQL statements.  (Don't know if that
>>matters or not.)  i.e.,:
>>
>>CREATE TABLE TEMP_TABLE (
>>       ProductID int,
>>       Web1 varchar(50),
>>       Web2 varchar(50),
>>       Web3 varchar(50)
>>)
>>
>>INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
>>FROM other_table WHERE FieldName = "WEB1"
>>
>>UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
>>WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
>>
>>UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
>>WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
>>
>>SELECT * FROM TEMP_TABLE;
>>
>>DROP TABLE TEMP_TABLE;
>>
>>
>>Or some such.
>>
>>HTH,
>>
>>DR
>>
>>
>>------------------------------------
>>
>>_______________________________________________________
>>ST-J Wiki: http://tarasis.net/STWiki/
>>ST-J Members Map: http://www.frappr.com/stj
>>
>>
>>
>>.Yahoo! Groups Links
>>
>>
>>
>>
>>
>>
>
>
>------------------------------------
>
>_______________________________________________________
>ST-J Wiki: http://tarasis.net/STWiki/
>ST-J Members Map: http://www.frappr.com/stj
>
>
>
>.Yahoo! Groups Links
>
>
>
>
>
>
>



[Non-text portions of this message have been removed]

How about this:
Select w1.product_id, w1.field_value, w2.field_value, w3.field_value
from
Products w1, products w2, poducts w3
Where w1.product_id=w2.product_id
And w1.product_id=w3.product_id
And w1.field_name='WEB1'
And w2.field_name='WEB2'
And w3.field_name='WEB3'

This assumes all records have all 3 field names. Otherwise you will
need to do an outer join.

Regards Victor

On Nov 25, 2009, at 4:58 PM, David Rosenstrauch <darose@...>
wrote:

> On 11/25/2009 04:40 PM, Eric Rizzo wrote:
>> I've got a table that acts like a name/value map of values for
>> Products
>> in the domain. It looks like this:
>>
>> ProductID    | FieldName    | FieldValue
>> ---------------------------------------------
>> 1        | WEB1        | A Fancy Widget
>> 1        | WEB2        | 5.00
>> 1        | WEB3        | 4.8 x 12.0 x 6.2
>> 2        | WEB1        | A Not-so-fancy Widget
>> 2        | WEB2        | 3.99
>> 2        | WEB3        | 8 x 3.4 x 5.0
>> etc...
>>
>> ProductID is a foreign key that uniquely identifies a Product. Each
>> Product has multiple rows in this table, one for each FieldName.
>> In this example data, WEB1 means "Product Description," WEB2 means
>> "Price," and WEB3 means "Dimensions."
>> (Don't ask why the database is this way; it's way out of my control
>> and
>> can't change the structure)
>>
>> I need a query that will load all Products, selecting each of the 3
>> kinds of fields for each unique ProductID as one row in the result
>> set.
>> For example, I'd like to get this as a result set:
>>
>> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
>> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
>>
>> I need a query that will produce that result set, but this is waaaay
>> beyond my humble SQL skills; an hour's worth of Googling hasn't
>> helped much.
>>
>> Thanks in advance,
>> Eric
>
> Not sure off the top of my head either.  Record aggregation like
> this is
> not very easy to do in SQL - at least not with a single SQL statement.
> So probably the easiest way to do this is to use a temp table - with
> the
> caveat that it would be multiple SQL statements.  (Don't know if that
> matters or not.)  i.e.,:
>
> CREATE TABLE TEMP_TABLE (
>    ProductID int,
>    Web1 varchar(50),
>    Web2 varchar(50),
>    Web3 varchar(50)
> )
>
> INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
> FROM other_table WHERE FieldName = "WEB1"
>
> UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
>
> UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
> WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
>
> SELECT * FROM TEMP_TABLE;
>
> DROP TABLE TEMP_TABLE;
>
>
> Or some such.
>
> HTH,
>
> DR
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>

You could also use a UNION of three selects and then follow that up with
a GROUP BY ProductID, but the JOIN/OUTER JOIN thing that Gunter and
Victor posted is probably faster.

I've seen table designs like this frequently, it's not that advanced I
think. Take a look at any legacy application and you will find stuff
that's actually a lot worse than this.

Best regards,

Nils

On Wed, 25 Nov 2009 18:07 -0500, "Victor Grazi" <vgrazi@...>
wrote:
> How about this:
> Select w1.product_id, w1.field_value, w2.field_value, w3.field_value
> from
> Products w1, products w2, poducts w3
> Where w1.product_id=w2.product_id
> And w1.product_id=w3.product_id
> And w1.field_name='WEB1'
> And w2.field_name='WEB2'
> And w3.field_name='WEB3'
>
> This assumes all records have all 3 field names. Otherwise you will
> need to do an outer join.
>
> Regards Victor
>
> On Nov 25, 2009, at 4:58 PM, David Rosenstrauch <darose@...>
> wrote:
>
> > On 11/25/2009 04:40 PM, Eric Rizzo wrote:
> >> I've got a table that acts like a name/value map of values for
> >> Products
> >> in the domain. It looks like this:
> >>
> >> ProductID    | FieldName    | FieldValue
> >> ---------------------------------------------
> >> 1        | WEB1        | A Fancy Widget
> >> 1        | WEB2        | 5.00
> >> 1        | WEB3        | 4.8 x 12.0 x 6.2
> >> 2        | WEB1        | A Not-so-fancy Widget
> >> 2        | WEB2        | 3.99
> >> 2        | WEB3        | 8 x 3.4 x 5.0
> >> etc...
> >>
> >> ProductID is a foreign key that uniquely identifies a Product. Each
> >> Product has multiple rows in this table, one for each FieldName.
> >> In this example data, WEB1 means "Product Description," WEB2 means
> >> "Price," and WEB3 means "Dimensions."
> >> (Don't ask why the database is this way; it's way out of my control
> >> and
> >> can't change the structure)
> >>
> >> I need a query that will load all Products, selecting each of the 3
> >> kinds of fields for each unique ProductID as one row in the result
> >> set.
> >> For example, I'd like to get this as a result set:
> >>
> >> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
> >> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
> >>
> >> I need a query that will produce that result set, but this is waaaay
> >> beyond my humble SQL skills; an hour's worth of Googling hasn't
> >> helped much.
> >>
> >> Thanks in advance,
> >> Eric
> >
> > Not sure off the top of my head either.  Record aggregation like
> > this is
> > not very easy to do in SQL - at least not with a single SQL statement.
> > So probably the easiest way to do this is to use a temp table - with
> > the
> > caveat that it would be multiple SQL statements.  (Don't know if that
> > matters or not.)  i.e.,:
> >
> > CREATE TABLE TEMP_TABLE (
> >    ProductID int,
> >    Web1 varchar(50),
> >    Web2 varchar(50),
> >    Web3 varchar(50)
> > )
> >
> > INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
> > FROM other_table WHERE FieldName = "WEB1"
> >
> > UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
> > WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
> >
> > UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
> > WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
> >
> > SELECT * FROM TEMP_TABLE;
> >
> > DROP TABLE TEMP_TABLE;
> >
> >
> > Or some such.
> >
> > HTH,
> >
> > DR
> >
> >
> > ------------------------------------
> >
> > _______________________________________________________
> > ST-J Wiki: http://tarasis.net/STWiki/
> > ST-J Members Map: http://www.frappr.com/stj
> >
> >
> >
> > .Yahoo! Groups Links
> >
> >
> >
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>
--
==================================
nils@...

Solutions like that work ok, but of course only if you know the list of
FieldNames
If that is not known, or might change later, you can't use this solution.

And I guess the probability that new fieldNames might be added later is
high, that's probably the reason for choosing such a design.

I just tried it in MySQL and it works fine like this:

select product_id, group_concat(field_value) from Products goup by
product_id

Or, if the fieldName is relevant as well:

select product_id, group_concat(concat(field_name,":",field_value)) from
Products group by product_id


Geert

Victor Grazi wrote:
>
>
> How about this:
> Select w1.product_id, w1.field_value, w2.field_value, w3.field_value
> from
> Products w1, products w2, poducts w3
> Where w1.product_id=w2.product_id
> And w1.product_id=w3.product_id
> And w1.field_name='WEB1'
> And w2.field_name='WEB2'
> And w3.field_name='WEB3'
>
> This assumes all records have all 3 field names. Otherwise you will
> need to do an outer join.
>
> Regards Victor
>
> On Nov 25, 2009, at 4:58 PM, David Rosenstrauch <darose@...
> <mailto:darose%40darose.net>>
> wrote:
>
> > On 11/25/2009 04:40 PM, Eric Rizzo wrote:
> >> I've got a table that acts like a name/value map of values for
> >> Products
> >> in the domain. It looks like this:
> >>
> >> ProductID | FieldName | FieldValue
> >> ---------------------------------------------
> >> 1 | WEB1 | A Fancy Widget
> >> 1 | WEB2 | 5.00
> >> 1 | WEB3 | 4.8 x 12.0 x 6.2
> >> 2 | WEB1 | A Not-so-fancy Widget
> >> 2 | WEB2 | 3.99
> >> 2 | WEB3 | 8 x 3.4 x 5.0
> >> etc...
> >>
> >> ProductID is a foreign key that uniquely identifies a Product. Each
> >> Product has multiple rows in this table, one for each FieldName.
> >> In this example data, WEB1 means "Product Description," WEB2 means
> >> "Price," and WEB3 means "Dimensions."
> >> (Don't ask why the database is this way; it's way out of my control
> >> and
> >> can't change the structure)
> >>
> >> I need a query that will load all Products, selecting each of the 3
> >> kinds of fields for each unique ProductID as one row in the result
> >> set.
> >> For example, I'd like to get this as a result set:
> >>
> >> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
> >> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
> >>
> >> I need a query that will produce that result set, but this is waaaay
> >> beyond my humble SQL skills; an hour's worth of Googling hasn't
> >> helped much.
> >>
> >> Thanks in advance,
> >> Eric
> >
> > Not sure off the top of my head either. Record aggregation like
> > this is
> > not very easy to do in SQL - at least not with a single SQL statement.
> > So probably the easiest way to do this is to use a temp table - with
> > the
> > caveat that it would be multiple SQL statements. (Don't know if that
> > matters or not.) i.e.,:
> >
> > CREATE TABLE TEMP_TABLE (
> > ProductID int,
> > Web1 varchar(50),
> > Web2 varchar(50),
> > Web3 varchar(50)
> > )
> >
> > INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
> > FROM other_table WHERE FieldName = "WEB1"
> >
> > UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
> > WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
> >
> > UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
> > WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
> >
> > SELECT * FROM TEMP_TABLE;
> >
> > DROP TABLE TEMP_TABLE;
> >
> >
> > Or some such.
> >
> > HTH,
> >
> > DR
> >
> >
> > ------------------------------------
> >
> > _______________________________________________________
> > ST-J Wiki: http://tarasis.net/STWiki/ <http://tarasis.net/STWiki/>
> > ST-J Members Map: http://www.frappr.com/stj <http://www.frappr.com/stj>
> >
> >
> >
> > .Yahoo! Groups Links
> >
> >
> >
>
>


[Non-text portions of this message have been removed]

If and when new fields are added, you can revise your SQL. Otherwise, you
are paying in performance for each query "just in case" fields might someday
change.

On Thu, Nov 26, 2009 at 3:50 AM, Geert Van Damme
<geert.vandamme@...>wrote:

> Solutions like that work ok, but of course only if you know the list of
> FieldNames
> If that is not known, or might change later, you can't use this solution.
>
> And I guess the probability that new fieldNames might be added later is
> high, that's probably the reason for choosing such a design.
>
> I just tried it in MySQL and it works fine like this:
>
> select product_id, group_concat(field_value) from Products goup by
> product_id
>
> Or, if the fieldName is relevant as well:
>
> select product_id, group_concat(concat(field_name,":",field_value)) from
> Products group by product_id
>
>
> Geert
>
> Victor Grazi wrote:
> >
> >
> > How about this:
> > Select w1.product_id, w1.field_value, w2.field_value, w3.field_value
> > from
> > Products w1, products w2, poducts w3
> > Where w1.product_id=w2.product_id
> > And w1.product_id=w3.product_id
> > And w1.field_name='WEB1'
> > And w2.field_name='WEB2'
> > And w3.field_name='WEB3'
> >
> > This assumes all records have all 3 field names. Otherwise you will
> > need to do an outer join.
> >
> > Regards Victor
> >
> > On Nov 25, 2009, at 4:58 PM, David Rosenstrauch <darose@...
> > <mailto:darose%40darose.net <darose%2540darose.net>>>
> > wrote:
> >
> > > On 11/25/2009 04:40 PM, Eric Rizzo wrote:
> > >> I've got a table that acts like a name/value map of values for
> > >> Products
> > >> in the domain. It looks like this:
> > >>
> > >> ProductID | FieldName | FieldValue
> > >> ---------------------------------------------
> > >> 1 | WEB1 | A Fancy Widget
> > >> 1 | WEB2 | 5.00
> > >> 1 | WEB3 | 4.8 x 12.0 x 6.2
> > >> 2 | WEB1 | A Not-so-fancy Widget
> > >> 2 | WEB2 | 3.99
> > >> 2 | WEB3 | 8 x 3.4 x 5.0
> > >> etc...
> > >>
> > >> ProductID is a foreign key that uniquely identifies a Product. Each
> > >> Product has multiple rows in this table, one for each FieldName.
> > >> In this example data, WEB1 means "Product Description," WEB2 means
> > >> "Price," and WEB3 means "Dimensions."
> > >> (Don't ask why the database is this way; it's way out of my control
> > >> and
> > >> can't change the structure)
> > >>
> > >> I need a query that will load all Products, selecting each of the 3
> > >> kinds of fields for each unique ProductID as one row in the result
> > >> set.
> > >> For example, I'd like to get this as a result set:
> > >>
> > >> 1, 'A Fancy Widget', 5.00, '4.8 x 12.0 x 6.2'
> > >> 2, 'A Not-so-fancy Widget', 3.99, '8 x 3.4 x 5.0'
> > >>
> > >> I need a query that will produce that result set, but this is waaaay
> > >> beyond my humble SQL skills; an hour's worth of Googling hasn't
> > >> helped much.
> > >>
> > >> Thanks in advance,
> > >> Eric
> > >
> > > Not sure off the top of my head either. Record aggregation like
> > > this is
> > > not very easy to do in SQL - at least not with a single SQL statement.
> > > So probably the easiest way to do this is to use a temp table - with
> > > the
> > > caveat that it would be multiple SQL statements. (Don't know if that
> > > matters or not.) i.e.,:
> > >
> > > CREATE TABLE TEMP_TABLE (
> > > ProductID int,
> > > Web1 varchar(50),
> > > Web2 varchar(50),
> > > Web3 varchar(50)
> > > )
> > >
> > > INSERT INTO TEMP_TABLE (ProductID, Web1) SELECT ProductID, FieldValue
> > > FROM other_table WHERE FieldName = "WEB1"
> > >
> > > UPDATE TEMP_TABLE t SET Web2 = (SELECT FieldValue FROM other_table o
> > > WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB2")
> > >
> > > UPDATE TEMP_TABLE t SET Web3 = (SELECT FieldValue FROM other_table o
> > > WHERE o.ProductID = t.ProductID AND o.FieldName = "WEB3")
> > >
> > > SELECT * FROM TEMP_TABLE;
> > >
> > > DROP TABLE TEMP_TABLE;
> > >
> > >
> > > Or some such.
> > >
> > > HTH,
> > >
> > > DR
> > >
> > >
> > > ------------------------------------
> > >
> > > _______________________________________________________
> > > ST-J Wiki: http://tarasis.net/STWiki/ <http://tarasis.net/STWiki/>
> > > ST-J Members Map: http://www.frappr.com/stj <http://www.frappr.com/stj
> >
> > >
> > >
> > >
> > > .Yahoo! Groups Links
> > >
> > >
> > >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> _______________________________________________________
> ST-J Wiki: http://tarasis.net/STWiki/
> ST-J Members Map: http://www.frappr.com/stj
>
>
>
> .Yahoo! Groups Links
>
>
>
>


[Non-text portions of this message have been removed]