Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
Looking for ideas - how to create result set with 'extra records'. |
Wed, Jun 5 2013 3:06 AM | Permanent Link |
Adam H. | Hi,
I've got a request from a customer and I'm not sure quite how to go about it. I'm wondering if it's possible to do in SQL, and if not - what recommendations anyone might have. My customers are loading containers onto a train. The train is broken up into individual wagons, and the wagons have individual slot numbers. The wagons on a train may differ from day to day. My customers have a lookup table with wagon numbers. Amongst this data is the number of slots per wagon. (Some wagons have 2 slots, others have 3) Let's call this the WAGON table, and it will look something like this (Wagon Lookup table) WAGON MAXSLOTS WAGON1 2 WAGON2 2 WAGON3 3 WAGON4 2 WAGON5 3 .... etc My clients have a second table where they load the container information into their system. On this table, they allocate the wagon number, and the slot number that the container is loaded into... (Container Table) CONTAINER TRAIN WAGON SLOT CONT1 TRAIN1 WAGON1 1 CONT2 TRAIN1 WAGON1 2 CONT3 TRAIN1 WAGON2 1 CONT4 TRAIN1 WAGON3 2 CONT5 TRAIN1 WAGON3 3 .... etc Now the task I have been assigned is to develop a result set that shows a list of all wagons and available slots for a particular train - regardless of whether that slot is used or not. So, for instance with the data above you can see that WAGON 2 has 2 slots available, but only 1 container has been loaded (into slot 1) in the 2nd table. Likewise WAGON3 has 3 slots available, but only slots 2 and 3 have been allocated containers. My client would like the data to show as follows: TRAIN WAGON SLOT CONTAINER TRAIN1 WAGON1 1 CONT1 TRAIN1 WAGON1 2 CONT2 TRAIN1 WAGON2 1 CONT3 TRAIN1 WAGON2 2 TRAIN1 WAGON3 1 TRAIN1 WAGON3 2 CONT4 TRAIN1 WAGON3 3 CONT4 The problem I face is that I don't currently have any source data that holds one record per slot - only those that are either filled, or records showing the total number of slots available. Some how I need to be able to use the wagon lookup table to generate a record per available slot. If I wasn't using SQL I guess I could create a temporary table, go through the Wagon Lookup Table, and perform a loop per wagon to add a record for wagon and slot until I reach the max number of wagons, and then move to the next record. I'm just wondering if I can do something similar in SQL? Cheers Adam. |
Wed, Jun 5 2013 8:14 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Adam
Bearing in I'm using DBISAM3 (yes 3), the following SQL seems to do what you want. ================================ DROP TABLE IF EXISTS MEMORY Slots ; CREATE TABLE MEMORY Slots (Wagon VARCHAR(10), Slot INTEGER) ; INSERT INTO MEMORY Slots SELECT Wagon, 1 FROM Wagons ; INSERT INTO MEMORY Slots SELECT Wagon, 2 FROM Wagons ; INSERT INTO MEMORY Slots SELECT Wagon, 3 FROM Wagons WHERE MaxSlots > 2 ; SELECT C.Train, S.Wagon, S.Slot, C.Container FROM MEMORY Slots S LEFT OUTER JOIN Containers C ON (C.Wagon = S.Wagon AND C.Slot = S.Slot) WHERE NOT C.Train IS NULL ORDER BY C.Train, S.Wagon, S.Slot, C.Container ================ Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Adam H." <ahairsub5@removeme.jvxp.com> wrote in message news:33F07D20-6649-49C1-A15E-1B588E15ABA6@news.elevatesoft.com... > Hi, > > I've got a request from a customer and I'm not sure quite how to go about > it. I'm wondering if it's possible to do in SQL, and if not - what > recommendations anyone might have. > > My customers are loading containers onto a train. The train is broken up > into individual wagons, and the wagons have individual slot numbers. > > The wagons on a train may differ from day to day. > > My customers have a lookup table with wagon numbers. Amongst this data is > the number of slots per wagon. > > (Some wagons have 2 slots, others have 3) > > Let's call this the WAGON table, and it will look something like this > > (Wagon Lookup table) > WAGON MAXSLOTS > WAGON1 2 > WAGON2 2 > WAGON3 3 > WAGON4 2 > WAGON5 3 > ... etc > > > My clients have a second table where they load the container information > into their system. On this table, they allocate the wagon number, and the > slot number that the container is loaded into... > > (Container Table) > CONTAINER TRAIN WAGON SLOT > CONT1 TRAIN1 WAGON1 1 > CONT2 TRAIN1 WAGON1 2 > CONT3 TRAIN1 WAGON2 1 > CONT4 TRAIN1 WAGON3 2 > CONT5 TRAIN1 WAGON3 3 > ... etc > > > > Now the task I have been assigned is to develop a result set that shows a > list of all wagons and available slots for a particular train - regardless > of whether that slot is used or not. > > So, for instance with the data above you can see that WAGON 2 has 2 slots > available, but only 1 container has been loaded (into slot 1) in the 2nd > table. > > Likewise WAGON3 has 3 slots available, but only slots 2 and 3 have been > allocated containers. > > My client would like the data to show as follows: > > TRAIN WAGON SLOT CONTAINER > TRAIN1 WAGON1 1 CONT1 > TRAIN1 WAGON1 2 CONT2 > TRAIN1 WAGON2 1 CONT3 > TRAIN1 WAGON2 2 TRAIN1 WAGON3 1 TRAIN1 WAGON3 2 CONT4 > TRAIN1 WAGON3 3 CONT4 > > > The problem I face is that I don't currently have any source data that > holds one record per slot - only those that are either filled, or records > showing the total number of slots available. > > Some how I need to be able to use the wagon lookup table to generate a > record per available slot. > > If I wasn't using SQL I guess I could create a temporary table, go through > the Wagon Lookup Table, and perform a loop per wagon to add a record for > wagon and slot until I reach the max number of wagons, and then move to > the next record. I'm just wondering if I can do something similar in SQL? > > Cheers > > Adam. |
Thu, Jun 6 2013 2:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
As far as I can see you only have a link between trains and wagons when at least one slot is allocated. If I'm right then on that basis you can not identify totally empty wagons eg if wagon 4 is on train 1 you have no way of knowing. You need an extra table to say which wagons are on which trains. Unless I'm missing something as things stand there is no way you can give the client what they want. Roy Lambert [Team Elevate] |
Thu, Jun 6 2013 5:26 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi Adam
I have read and agree with Roy's post about linking Wagons to Trains - I assumed that you had simplified the scenario for the sake of the newsgroup question. However, with a "Trains" table that listed the Wagons in each Train it should be easy enough to generate a "Slots" temporary table for a particular train (or all trains) using the same method as in my suggested SQL. Even simpler if the "Wagons" table has a column for the train that the wagon is currently on. How nice to be able to play with trains and get paid for it!!! Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Jeff Cook" <jeffc@aspect.co.nz> wrote in message news:7D67BB7C-3067-466B-AEA9-AEE25C476C91@news.elevatesoft.com... > Hi Adam > > Bearing in I'm using DBISAM3 (yes 3), the following SQL seems to do what > you want. > > ================================ > DROP TABLE IF EXISTS MEMORY Slots > ; > CREATE TABLE MEMORY Slots > (Wagon VARCHAR(10), Slot INTEGER) > ; > INSERT INTO MEMORY Slots > SELECT Wagon, 1 FROM Wagons > ; > INSERT INTO MEMORY Slots > SELECT Wagon, 2 FROM Wagons > ; > INSERT INTO MEMORY Slots > SELECT Wagon, 3 FROM Wagons WHERE MaxSlots > 2 > ; > SELECT C.Train, S.Wagon, S.Slot, C.Container > FROM MEMORY Slots S > LEFT OUTER JOIN Containers C ON (C.Wagon = S.Wagon AND C.Slot = S.Slot) > WHERE NOT C.Train IS NULL > ORDER BY C.Train, S.Wagon, S.Slot, C.Container > ================ > Cheers > > Jeff > > -- > Jeff Cook > Aspect Systems Ltd > www.aspect.co.nz > > "Adam H." <ahairsub5@removeme.jvxp.com> wrote in message > news:33F07D20-6649-49C1-A15E-1B588E15ABA6@news.elevatesoft.com... >> Hi, >> >> I've got a request from a customer and I'm not sure quite how to go about >> it. I'm wondering if it's possible to do in SQL, and if not - what >> recommendations anyone might have. >> >> My customers are loading containers onto a train. The train is broken up >> into individual wagons, and the wagons have individual slot numbers. >> >> The wagons on a train may differ from day to day. >> >> My customers have a lookup table with wagon numbers. Amongst this data is >> the number of slots per wagon. >> >> (Some wagons have 2 slots, others have 3) >> >> Let's call this the WAGON table, and it will look something like this >> >> (Wagon Lookup table) >> WAGON MAXSLOTS >> WAGON1 2 >> WAGON2 2 >> WAGON3 3 >> WAGON4 2 >> WAGON5 3 >> ... etc >> >> >> My clients have a second table where they load the container information >> into their system. On this table, they allocate the wagon number, and the >> slot number that the container is loaded into... >> >> (Container Table) >> CONTAINER TRAIN WAGON SLOT >> CONT1 TRAIN1 WAGON1 1 >> CONT2 TRAIN1 WAGON1 2 >> CONT3 TRAIN1 WAGON2 1 >> CONT4 TRAIN1 WAGON3 2 >> CONT5 TRAIN1 WAGON3 3 >> ... etc >> >> >> >> Now the task I have been assigned is to develop a result set that shows a >> list of all wagons and available slots for a particular train - >> regardless of whether that slot is used or not. >> >> So, for instance with the data above you can see that WAGON 2 has 2 slots >> available, but only 1 container has been loaded (into slot 1) in the 2nd >> table. >> >> Likewise WAGON3 has 3 slots available, but only slots 2 and 3 have been >> allocated containers. >> >> My client would like the data to show as follows: >> >> TRAIN WAGON SLOT CONTAINER >> TRAIN1 WAGON1 1 CONT1 >> TRAIN1 WAGON1 2 CONT2 >> TRAIN1 WAGON2 1 CONT3 >> TRAIN1 WAGON2 2 TRAIN1 WAGON3 1 TRAIN1 WAGON3 2 CONT4 >> TRAIN1 WAGON3 3 CONT4 >> >> >> The problem I face is that I don't currently have any source data that >> holds one record per slot - only those that are either filled, or records >> showing the total number of slots available. >> >> Some how I need to be able to use the wagon lookup table to generate a >> record per available slot. >> >> If I wasn't using SQL I guess I could create a temporary table, go >> through the Wagon Lookup Table, and perform a loop per wagon to add a >> record for wagon and slot until I reach the max number of wagons, and >> then move to the next record. I'm just wondering if I can do something >> similar in SQL? >> >> Cheers >> >> Adam. > > |
Thu, Jun 6 2013 7:17 PM | Permanent Link |
Adam H. | Hi Roy,
Nicely picked up. My client has not mentioned that there will be any empty wagons, only empty slots. But if this changes, you are right - I will need to create an additional table. For the other part though, I think Jeff's idea will work nicely. Cheers Adam. |
Thu, Jun 6 2013 7:20 PM | Permanent Link |
Adam H. | Hi Jeff,
How's it going over there across the pond. We've had some decent rain the last week or so (unusual for this century) - so not sure if you're about to cop a bit too. Thanks for your reply. On the contrary your suggestion is exactly what we're needing at this point. Even if we do require an additional table (and I think this is something that may occur down the track that they just haven't made themselves aware of yet) - the same / similar approach will be easy to do. Appreciate your help! > How nice to be able to play with trains and get paid for it!!! LOL - I wish. I'm stuck in an office. Don't get to go out on the tracks. Maybe I can buy Train Sim and claim it as a work expense though. Cheers Adam. |
Fri, Jun 7 2013 4:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Nicely picked up. My client has not mentioned that there will be any >empty wagons, only empty slots. It just looked like a standard spec that hasn't been thought out fully. I'm sure they'll spot the need after you've delivered what they ask for >But if this changes, you are right - I will need to create an additional >table. > >For the other part though, I think Jeff's idea will work nicely. So do I Roy |
Mon, Jun 10 2013 9:50 PM | Permanent Link |
Adam H. | > It just looked like a standard spec that hasn't been thought out fully. I'm sure they'll spot the need after you've delivered what they ask for Hmm. Roy - you make me suspicious in thinking that you've been here before yourself. |
Tue, Jun 11 2013 3:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>> It just looked like a standard spec that hasn't been thought out fully. I'm sure they'll spot the need after you've delivered what they ask for > >Hmm. Roy - you make me suspicious in thinking that you've been here >before yourself. How can you say that. All of my users throughout my career have been caring and thoughtful, have always considered all options and possibilities and never asked for the impossible, or the possible in impossible timescales. Roy |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |