Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Chuck Dgrow
Sun Micro Systems
0
|
Posted - 2013.02.18 16:42:00 -
[1] - Quote
Hello,
Im working on to build a query that would return me the required material to build T2 Ship and Mods. My query is able to grab all the Components required and the RAM needed to build it but where i seem to struggle is with the Mineral required.
Here my Query :
select invTypes.typeName,invTypes.raceID,invGroups.groupName,invTypeMaterials.* from invTypes INNER JOIN invBlueprintTypes on invTypes.typeID = invBlueprintTypes.productTypeID INNER JOIN invGroups on invTypes.groupID = invGroups.groupID INNER JOIN invTypeMaterials on invTypes.typeID = invTypeMaterials.typeID where invBlueprintTypes.techLevel = '2' and invTypes.typeName = 'Enyo' and invTypes.marketGroupID is not NULL
**** I added the Enyo line to only receive this results to make an example ****
The result is showing this :
Enyo8Assault Ship120443412600 Enyo8Assault Ship12044356564 Enyo8Assault Ship12044363104 Enyo8Assault Ship1204437450 Enyo8Assault Ship120443886 Enyo8Assault Ship120443915 Enyo8Assault Ship12044404 Enyo8Assault Ship12044382825 Enyo8Assault Ship120441139930 Enyo8Assault Ship120441153115 Enyo8Assault Ship120441153520 Enyo8Assault Ship120441154190 Enyo8Assault Ship1204411545500 Enyo8Assault Ship12044115475 Enyo8Assault Ship120441155360 Enyo8Assault Ship120441155620
If you check the first couple result you will see the mineral ID required to build that Enyo. I noticied that this query seem to add the Mineral of the Incursus which is the T1 ship required to build the Enyo.
Incursus Trit required = 1200 (Base mats without waste) Enyo Trit required = 600 (Base mats without waste)
So basicly you have to remove the T1 item mineral required from the T2 BPO but there seem to be exeption, if you take the Ishkur as example, you will end up with a required amount of trit of 11400 which is 600 unit less then the Incursus (which is the T1 ship required to build the Ishkur) so substracting the amount would leave you with a -400 unit
Anyone could light up my lantern on this ? I can't seem to find the commun factor that would make my query generic for all the T2 bpo |
Elena Thiesant
Sun Micro Systems
190
|
Posted - 2013.02.18 17:30:00 -
[2] - Quote
Was fiddling with this myself a while back. Didn't get too far, but I did find these: http://k162space.com/2012/04/13/working-with-eve-static-data/ http://pastebin.com/6hypXsrS
Change the table def in the pastebin listing to CREATE TABLE [dbo].[typeBuildReqs] ( typeID INT , activityID TINYINT , requiredTypeID SMALLINT , quantity INT , damagePerJob FLOAT , wasted TINYINT , CONSTRAINT typetypeBuildReqs_PK PRIMARY KEY CLUSTERED ( typeID, activityID, requiredTypeID, wasted ) ) or you get a conversion error.
Once the code from that site is run, this will pull the build requirements for a standard ME -4 BPC (excluding T1 component). Change the multiplier in the case statement for other ME levels.
SELECT ProdType.typeName, CEILING(CASE wasted WHEN 0 THEN br.quantity ELSE br.quantity*1.5 END) AS MaterialAtMEMinus4, br.damagePerJob, invTypes.typeName FROM typeBuildReqs br INNER JOIN invTypes ON invTypes.typeID = br.requiredTypeID INNER JOIN dbo.invBlueprintTypes AS ibt ON br.typeID = ibt.blueprintTypeID INNER JOIN dbo.invTypes AS ProdType ON ibt.productTypeID = ProdType.typeID WHERE ProdType.typeName IN ( 'Enyo', 'Ishkur' ) |
Steve Ronuken
Fuzzwork Enterprises
1168
|
Posted - 2013.02.18 18:20:00 -
[3] - Quote
These should answer most of your questions.
http://www.fuzzwork.co.uk/2012/10/24/eve-sde-sql-blueprint-details/ http://www.fuzzwork.co.uk/2012/10/27/eve-sde-sql-blueprint-details-part-2/ FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
Chuck Dgrow
Sun Micro Systems
0
|
Posted - 2013.02.18 19:38:00 -
[4] - Quote
Thank you Steeve !
I will try to get that converted to SQL. For some reason my Case binded with that SUM seem to fail ... I will post my results once i figure it out |
Steve Ronuken
Fuzzwork Enterprises
1169
|
Posted - 2013.02.18 19:42:00 -
[5] - Quote
Chuck Dgrow wrote:Thank you Steeve !
I will try to get that converted to SQL. For some reason my Case binded with that SUM seem to fail ... I will port my results once i figure it out
If you're trying it with MS SQL, then you'll probably have trouble with the greatest() function. you should be able to get away with an case statement instead.
case when quantity>0 then quantity else 0 end as quantity
If you're doing it in mysql (say, with my conversion of the SDE) then the sql should just work. FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
Chuck Dgrow
Sun Micro Systems
0
|
Posted - 2013.02.18 20:22:00 -
[6] - Quote
I'm using MSSQL and i modified the query like this :
SELECT typeid,name,SUM(quantity) as quantity, case when quantity>0 then quantity else 0 end FROM ( SELECT invTypes.typeID typeid,invTypes.typeName name,quantity FROM invTypes,invTypeMaterials WHERE invTypeMaterials.materialTypeID=invTypes.typeID AND invTypeMaterials.typeID=12044 UNION SELECT invTypes.typeID typeid,invTypes.typeName name, invTypeMaterials.quantity*r.quantity*-1 quantity FROM invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt WHERE invTypeMaterials.materialTypeID=invTypes.typeID AND invTypeMaterials.typeID =r.requiredTypeID AND r.typeID = bt.blueprintTypeID AND r.activityID = 1 AND bt.productTypeID=12044 AND r.recycle=1 ) as T
But i get a error saying : Msg 8120, Level 16, State 1, Line 1 Column 'T.typeid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Once i get that fixed i would need to modify that to return theses value from multiple TypeID. Not sure if that will be possible thought
|
Steve Ronuken
Fuzzwork Enterprises
1169
|
Posted - 2013.02.18 20:31:00 -
[7] - Quote
Chuck Dgrow wrote:I'm using MSSQL and i modified the query like this :
SELECT typeid,name,SUM(quantity) as quantity, case when quantity>0 then quantity else 0 end FROM ( SELECT invTypes.typeID typeid,invTypes.typeName name,quantity FROM invTypes,invTypeMaterials WHERE invTypeMaterials.materialTypeID=invTypes.typeID AND invTypeMaterials.typeID=12044 UNION SELECT invTypes.typeID typeid,invTypes.typeName name, invTypeMaterials.quantity*r.quantity*-1 quantity FROM invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt WHERE invTypeMaterials.materialTypeID=invTypes.typeID AND invTypeMaterials.typeID =r.requiredTypeID AND r.typeID = bt.blueprintTypeID AND r.activityID = 1 AND bt.productTypeID=12044 AND r.recycle=1 ) as T
But i get a error saying : Msg 8120, Level 16, State 1, Line 1 Column 'T.typeid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Once i get that fixed i would need to modify that to return theses value from multiple TypeID. Not sure if that will be possible thought
add: GROUP BY typeid,name
after 'as T' FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
Chuck Dgrow
Sun Micro Systems
0
|
Posted - 2013.02.18 20:37:00 -
[8] - Quote
i added as T GROUP BY typeid,name, quantity and now the results is off. The SUM(quantity) doesnt seem to work. The results look like this :
34Tritanium-120000 34Tritanium1260012600 35Pyerite-100000 35Pyerite65646564 36Mexallon-36000 36Mexallon31043104 37Isogen-300 37Isogen450450 38Nocxium-150 38Nocxium8686 39Zydrine-50 39Zydrine1515 |
Steve Ronuken
Fuzzwork Enterprises
1169
|
Posted - 2013.02.18 20:46:00 -
[9] - Quote
Try the following:
SELECT typeid,name,case when SUM(quantity)>0 then SUM(quantity) else 0 end as quantity FROM ( SELECT invTypes.typeid typeid,invTypes.typeName name,quantity FROM invTypes,invTypeMaterials WHERE invTypeMaterials.materialTypeID=invTypes.typeID AND invTypeMaterials.TypeID=12044 UNION SELECT invTypes.typeid typeid,invTypes.typeName name, invTypeMaterials.quantity*r.quantity*-1 quantity FROM invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt WHERE invTypeMaterials.materialTypeID=invTypes.typeID AND invTypeMaterials.TypeID =r.requiredTypeID AND r.typeID = bt.blueprintTypeID AND r.activityID = 1 AND bt.productTypeID=12044 AND r.recycle=1 ) t GROUP BY typeid,name
I've not had a chance to play with it, as this box doesn't have sql server installed. but it should be viable. FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
Chuck Dgrow
Sun Micro Systems
0
|
Posted - 2013.02.18 21:05:00 -
[10] - Quote
That worknig ! Thank for your help ! |
|
Teezea Evotori
Red-Five Frozen Shipyards
7
|
Posted - 2013.04.19 15:15:00 -
[11] - Quote
Hi Steve - I am unable to get this to work for T2 Blueprints i.e. TypeID 1319 (Expanded Cargohold II) it only shows me the base materials and not the additional ones. Is there additional sql needed perhaps? *\o/* |
Steve Ronuken
Fuzzwork Enterprises
1318
|
Posted - 2013.04.19 18:09:00 -
[12] - Quote
Teezea Evotori wrote:Hi Steve - I am unable to get this to work for T2 Blueprints i.e. TypeID 1319 (Expanded Cargohold II) it only shows me the base materials and not the additional ones. Is there additional sql needed perhaps?
Which database (sqlite, mysql, MS sql, etc) are you using? (so I can double check the sql before I confirm it to you) Steve Ronuken for CSM 8 Handy tools and SDE conversions Twitter: @fuzzysteve on Twitter |
Teezea Evotori
Red-Five Frozen Shipyards
7
|
Posted - 2013.04.19 18:13:00 -
[13] - Quote
Thank you for replying! I didn't realize till after I posted that this thread was over 2 months old.
We are using Microsoft SQL Server 2008. *\o/* |
Steve Ronuken
Fuzzwork Enterprises
1318
|
Posted - 2013.04.19 18:36:00 -
[14] - Quote
ahh. Now that I've refreshed my memory, yes. This SQL is just for the basic materials. The union in it is to remove the materials which are marked as recyclable in the extra materials.
The other bit of SQL you need is the bit for the extra materials (mentioned in my blog post): SELECT t.typeName tn, r.quantity qn, r.damagePerJob dmg,t.typeID typeid FROM ramTypeRequirements r,invTypes t,invBlueprintTypes bt,invGroups g where r.requiredTypeID = t.typeID and r.typeID = bt.blueprintTypeID and r.activityID = 1 and bt.productTypeID=1319 and g.categoryID != 16 and t.groupID = g.groupID
If the post doesn't answer any questions you have, feel free to ask here, in a new post, or by evemail Steve Ronuken for CSM 8 Handy tools and SDE conversions Twitter: @fuzzysteve on Twitter |
Teezea Evotori
Red-Five Frozen Shipyards
7
|
Posted - 2013.04.20 18:43:00 -
[15] - Quote
Thank you!!! I *think* it's working. Should we have more questions I will definitely ask in a better spot LOL Thanks again! *\o/* |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |