EVE Technology Lab

Trying to find where the anomaly type lives in the database... dbo.mapLocationWormholeClasses has (as you'd expect) the w-space classes, but nothing about whether said system is a boring regular one, wolf-rayet, red giant, etc...

Ahh, strange place to be. Using groupID = 920 looks to be a cleaner way to get what I need :)

It is an odd place, since inv stands for inventory I think. It probably something to do with those attributes and the dgm system.

I imagine that data not being there is by design.

There is a celestials table in /EVEINSTALL/bulkdata/mapbulk.db (sqlite) but it only has groups



The wormhole websites out there are populated by users. You could scrap one (with permission), I'm guessing the anoms don't change.

If I'm understanding your problem correctly, these might help:


SELECT t.typename, c.wormholeclassid
FROM mapdenormalize n
LEFT JOIN invtypes t ON n.typeid = t.typeid
LEFT JOIN maplocationwormholeclasses c ON n.regionid = c.locationid
WHERE n.solarsystemid = 123456789
AND n.groupid = '995'



SELECT i.typename, t.attributename, t.displayname, a.valuefloat, t.unitid
FROM dgmtypeattributes a
INNER JOIN dgmattributetypes t ON t.attributeid = a.attributeid
INNER JOIN invtypes i ON i.typeid = a.typeid
WHERE i.typename LIKE '%Red Giant%'
AND i.typename LIKE '%Class 5%'

Sigh... MSSQL doesn't like me for some reason. Here's my current SELECT statement:

Now before I added the underlinged segment, it was just another ",0 as [Zero_2]" and things were fine. Now that I've added that CASE statement, I get the message

Msg 8120, Level 16, State 1, Line 12
Column 'dbo.mapDenormalize.typeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What I don't understand is why this wasn't a problem with [Num_Planets] and such. At this point it should be fairly obvious I'm learning by doing, so pointers would be appreciated :)

Awesome, worked perfectly!! (I did try adding something to the GROUP BY portion earlier this afternoon as you hinted, but came up with an entirely different error that made it seem like I was trying to feed oranges to a flamingo)

Now to go hack at the last bit... system classification (mapLocationWormholeClasses), which I think is the missing link in getting route planning to work again... CCP did this kinda weird by defining entire regions as high, null, or C1-C6, then overriding low-sec system by system. (except for two constellations in Jove space which are apparently undefined classes 10 and 11?). Here's how I *think* it needs to be done, but I have no idea how to write the JOIN to make it work, since mapLocationWormholeClasses doesn't share a one-for-one match to mapSolarSystems they way mDenormalize does (C. is mLWC)...

,SUM(CASE WHEN C.[locationID] = A.[regionID] THEN C.[wormholeClassID] /*Matches highsec, nullsec, C1-C6*/
CASE WHEN C.[locationID] = A.[constellationID] THEN C.[wormholeClassID] /*special case for the Jove*/
ELSE 8 END) as [classification] /*anything that's left must be low sec*/

That's probably an ugly hack, but I'm not sure how else I could copy the values over since mLWC is missing that one-for-one that mD shares with mSS :(

The mapLocationWormholeClasses table is an interesting table. The locationID refers to the regionID from the mapSolarSystems table. You've already found out this gives some interesting results for some solarsystems, especially low-sec.

The trick is, that for some systems the locationID is actually the solarSystemID.

Bloem: Yeah, pretty much. I wonder what made CCP decide to do it that way. Probably for performance, but.....?
Fubar: Let me see if I follow your code correctly...

LEFT JOIN [dbo].[mapLocationWormholeClasses] C ON C.locationID = A.regionID
LEFT JOIN [dbo].[mapLocationWormholeClasses] D ON D.locationID = A.solarSystemID
- The two LEFT JOIN statements connect the mLWC to the mSS table via either regionID or solarSystemID, rather than my method of just throwing both tables into the bucket and seeing what sloshes back out. The results are then made available via C.[x] and D.[x]. You've left out the two Jove constellations since nobody can get there anyway.

COALESCE(D.[wormholeClassID],C.[wormholeClassID],9) as [classification]
- This replaces my convoluted method of assigning a classification by iterating through the nine defined system classifications until there's a match. Again, the two Jove classes (10 and 11) were left out since nobody can legitimately go there.

ROUND(B.security,1) as [security]
- Not sure why this is being replaced, since you can pluck A.[security] and not need to do anything special

(CASE WHEN B.[typeID] = 30574 THEN 3.......
- I'm guessing the changes to the GROUP BY formula made the SUM unnecessary?

GROUP BY.........
- Really don't understand just what this does yet. The MS Technet article makes it seem like this functions rather like a PivotTable, but since there should only be one row for each solar system, I don't quite get why it's needed. Unless SQL returns only values for fields that were SELECTed, in which case I could see this collapsing 12 rows, each with only two fields populated and the rest NULL, down into one row with all 12 fields populated?

ALL JOIN statements need an ON condition. The LEFT JOIN specifies that all records from the left table (here mapSolarSystems) and only the records from the right table (here mapLocationWormholeClasses) that match, otherwise NULL if returned.



COALESCE can be defined as a case shortcut. Basically the first value will be used if the value is not null, if it is null the second value is considered for null or not null. Finally if both of the first values are null 9 is used.



I used round just to verify the classification was returning the proper value. You don't have to use round if you don't want to.



You are correct. I remembered that the full expression is used in the GROUP BY section.



The GROUP BY section is required when any aggregate function is used (here sum was used).