- 8ender
- Sep 24, 2003
-
clown is watching you sleep
|
var1ety posted:
Manually add a primary key constraint on the materialized views.
I meant to update this. We ended up doing this and creating the foreign constraints on the views and everything seems to be working. Only problem now is that jDeveloper isn't seeing the views but I suspect thats a grants issue.
|
#
?
Jun 4, 2009 20:51
|
|
- Adbot
-
ADBOT LOVES YOU
|
|
#
?
Jun 8, 2024 06:42
|
|
- Squashy Nipples
- Aug 18, 2007
-
|
var1ety posted:
Clever SQL tricks.
Fascinating, thank you!
Although I don't think either will work on DB2, but it will be fun figuring out why/why-not. I like the idea of SELECTing NULL in the sub-query, I never would have thought of that.
|
#
?
Jun 4, 2009 20:58
|
|
- Squashy Nipples
- Aug 18, 2007
-
|
Eh, its not really their fault, its a huge company with many layers of management and different groups and so forth. Its always easier to fix things on my end.
But you are right, in this application speed is really unnecessary. I just get nervous when I hit tables with million plus lines.
|
#
?
Jun 5, 2009 00:49
|
|
- Bad Titty Puker
- Nov 3, 2007
-
-
Soiled Meat
|
Dr.Khron posted:
I've got a Table that gets updated monthly, usually on the 5th or 6th business day after month-end. I need to be able to do a quick check to make sure that the required data is there before the rest of code executes.
(bear in mind that this is a pretty big table, with several thousand rows per month, for the last 100-150 months.)
What is the fastest way to do this?
code:SELECT COUNT(*) FROM MSPERFRM
WHERE MSP_MTH_STR_DT = '05/01/2009'
Logic: if the returned value is greater then zero ( > 0 ), then data present.
code:SELECT * FROM MSPERFRM
WHERE MSP_MTH_STR_DT = '05/01/2009'
Logic: if the EOF Property is False, then data present.
Maybe it will depend on wether the cursor/recordset is Client-side or Server-Side?
code:SELECT
CASE WHEN EXISTS
(SELECT * FROM MSPERFRM WHERE MSP_MTH_STR_DT = '05/01/2009')
THEN 1
ELSE 0
END
|
#
?
Jun 5, 2009 03:02
|
|
- Squashy Nipples
- Aug 18, 2007
-
|
Hey what do you know, DB2 does support "EXISTS"!
drat I hate the documentation I have. I will have fun playing with this, though. Thanks for the help!
EDIT:
Hmmm, that does not work.
code:SELECT CASE WHEN EXISTS (
SELECT * FROM MSPERFRM WHERE MSP_MTH_STR_DT = '03/01/2009'
) THEN 1 ELSE 0 END
produces this error:
code:[IBM][CLI Driver][DB2/SUN] SQL0104N
An unexpected token "END-OF-STATEMENT" was found
following ") THEN 1 ELSE 0 END".
Expected tokens may include: "<table_expr>". SQLSTATE=42601
I'm not sure that this syntax works under DB2. All of the examples of "EXISTS" in the DB2 documentation shows it being used as a WHERE line, such as "WHERE EXISTS(..)"
Squashy Nipples fucked around with this message at 17:26 on Jun 5, 2009
|
#
?
Jun 5, 2009 12:58
|
|
- MoNsTeR
- Jun 29, 2002
-
|
From that error text it sounds like it wants you to be selecting FROM something. In Oracle with a statement like that you would select from the "dual" table, perhaps DB2 has an analogue to that.
edit: looks like there is, sysibm.sysdummy1
MoNsTeR fucked around with this message at 18:18 on Jun 5, 2009
|
#
?
Jun 5, 2009 18:06
|
|
- Mongolian Queef
- May 6, 2004
-
|
I have these tables:
Releases
id, name, ...
(1, "Popular Linux distribution")
Articles
id, subject, size, ...
(1, "Popular Linux distribution 1/3", 65535)
(2, "Popular Linux distribution 2/3", 65535)
(3, "Popular Linux distribution 3/3", 65535)
ArticleToReleaseMap
releaseid (index into Releases), articleid (index into Articles)
(1, 1)
(1, 2)
(1, 3)
I want to select the total size of all the articles that are mapped to the specified release id. Anyone care to enlighten me, please?
Edit: This worked:
code:select sum(a1.bytes) from articles a1 INNER JOIN ArticleToReleaseMap a2 where a1.id=a2.articleid and a2.releaseid=1;
Mongolian Queef fucked around with this message at 00:07 on Jun 7, 2009
|
#
?
Jun 7, 2009 00:01
|
|
- Squashy Nipples
- Aug 18, 2007
-
|
Standard SUM behavior under DB2:
Remove all Nulls, sum up remaining numbers. If ALL fields are Null, then the result is Null.
What I need is a SUM function that does the opposite: if ANY of the fields contain Nulls, then the result is Null.
Background: I'm calculating performance for pre-set portfolios available in 401k plans. These are simply mixtures of register funds already available in the plans. One table gives me Security IDs with the percentage of each, and another gives me the performance of the component funds for 1-month, 3-month, Year to Date, etc. etc. Performance for the Portfolio is simply the weighted-average of the individual components.
UNLESS performance is missing for ANY single component, in which case performance should not be displayed at all for that time period (Null).
Basically, my SQL pulls up the underlying data (from sub-query):
code:SEC_ID WEIGHT MON1 MON3 YTD YR1 YR3 YR5 YR10
1886 5 7.8 -7.2 -7.2 -37.91 -24.88 -2.82 Null
5668 23 5.6 -2.5 -2.5 -37.52 -35.54 -27.52 -13.2
5676 22 7.19 -9.92 -9.92 -31.95 -26.7 -11.28 10.04
5683 15 2.74 6.39 6.39 1.66 18.22 18.93 56.6
5667 7 8.26 -9.76 -9.76 -36.69 -43.98 -29.97 -47.45
5669 20 2.32 7.41 7.41 -13.72 -5.67 -2.83 29.92
5980 8 0.13 0.47 0.47 3.78 13.73 23.82 59.9
And then it gives me back this:
code:4.72 -1.32 -1.32 -22.32 -5.52 -1.41 1.42
When what I need is THIS:
code:4.72 -1.32 -1.32 -22.32 -5.52 -1.41 Null
Damint, I thought I had come up with a clever solution to needing a SUMPRODUCT function, but really, it looks like I'm going to have to strip the top two layers off, and do all of the calculating in code, since I can make up whatever logic I want there.
Anyway, here is my SQL:
code:SELECT
(SUM(MON1)/100) AS MON1, (SUM(MON3)/100) AS MON3,
(SUM(YTD)/100) AS YTD, (SUM(YR1)/100) AS YR1,
(((POWER(((SUM(YR3)/10000)+1),(1.0/3.0)))-1)*100) AS YR3,
(((POWER(((SUM(YR5)/10000)+1),(1.0/5.0)))-1)*100) AS YR5,
(((POWER(((SUM(YR10)/10000)+1),(1.0/10.0)))-1)*100) AS YR10
FROM (
SELECT
(PERCENT * MON1) AS MON1, (PERCENT * MON3) AS MON3,
(PERCENT * YTD) AS YTD, (PERCENT * YR1) AS YR1,
(PERCENT * YR3) AS YR3, (PERCENT * YR5) AS YR5,
(PERCENT * YR10) AS YR10
FROM (
SELECT MJC_SEC_ID AS SEC_ID, MJC_ALCTN_PCT AS PERCENT,
MSP_CMLV_1MTHR_PCT AS MON1, MSP_CMLV_3MTHR_PCT AS MON3,
MSP_CMLV_YTDR_PCT AS YTD, MSP_CMLV_1YRR_PCT AS YR1,
MSP_CMLV_3YRR_PCT AS YR3, MSP_CMLV_5YRR_PCT AS YR5,
MSP_CMLV_10YRR_PCT AS YR10
FROM MSPERFRM, (
SELECT DISTINCT
MJC_SEC_ID, MJC_ALCTN_PCT
FROM MJCOMP
WHERE MJC_DCP_NBR = 'PLAN'
AND MJC_RCP_PTF_CD = 'RCP_ID'
AND ( MJC_COMP_XPDT IS NULL
OR MJC_COMP_XPDT > 'RunDate' )
) AS SUBTABLE1
WHERE ( MSPERFRM.MSP_SEC_ID = SUBTABLE1.MJC_SEC_ID )
AND MSPERFRM.MSP_MTH_STR_DT = 'RunDate'
) AS SUBTABLE2 ) AS SUBTABLE3
(note: the 3,5 and 10 year data is being "Annualized" with that POWER function. With multi-year data, you must get the wieghted average of the cumulative data first, and then you can annualize it)
Help, please.
|
#
?
Jun 8, 2009 22:03
|
|
- Hammerite
- Mar 9, 2007
-
And you don't remember what I said here, either, but it was pompous and stupid.
-
Jade Ear Joe
|
would this work?
code:SELECT
MON1, MON3, YTD, YR1,
(CASE YR3CHECK WHEN 0 THEN YR3 ELSE NULL) AS YR3,
(CASE YR5CHECK WHEN 0 THEN YR5 ELSE NULL) AS YR5,
(CASE YR10CHECK WHEN 0 THEN YR10 ELSE NULL) AS YR10
FROM (
SELECT
(SUM(MON1)/100) AS MON1, (SUM(MON3)/100) AS MON3,
(SUM(YTD)/100) AS YTD, (SUM(YR1)/100) AS YR1,
(((POWER(((SUM(YR3)/10000)+1),(1.0/3.0)))-1)*100) AS YR3,
(((POWER(((SUM(YR5)/10000)+1),(1.0/5.0)))-1)*100) AS YR5,
(((POWER(((SUM(YR10)/10000)+1),(1.0/10.0)))-1)*100) AS YR10,
(SUM(YR3CHECK)) AS YR3CHECK,
(SUM(YR5CHECK)) AS YR5CHECK,
(SUM(YR10CHECK)) AS YR10CHECK
FROM (
SELECT
MON1, MON3, YTD, YR1,
YR3, (CASE WHEN YR3 IS NULL THEN 1 ELSE 0) AS YR3CHECK,
YR5, (CASE WHEN YR5 IS NULL THEN 1 ELSE 0) AS YR5CHECK,
YR10, (CASE WHEN YR10 IS NULL THEN 1 ELSE 0) AS YR10CHECK
FROM(
SELECT
(PERCENT * MON1) AS MON1, (PERCENT * MON3) AS MON3,
(PERCENT * YTD) AS YTD, (PERCENT * YR1) AS YR1,
(PERCENT * YR3) AS YR3, (PERCENT * YR5) AS YR5,
(PERCENT * YR10) AS YR10
FROM (
SELECT MJC_SEC_ID AS SEC_ID, MJC_ALCTN_PCT AS PERCENT,
MSP_CMLV_1MTHR_PCT AS MON1, MSP_CMLV_3MTHR_PCT AS MON3,
MSP_CMLV_YTDR_PCT AS YTD, MSP_CMLV_1YRR_PCT AS YR1,
MSP_CMLV_3YRR_PCT AS YR3, MSP_CMLV_5YRR_PCT AS YR5,
MSP_CMLV_10YRR_PCT AS YR10
FROM MSPERFRM, (
SELECT DISTINCT
MJC_SEC_ID, MJC_ALCTN_PCT
FROM MJCOMP
WHERE MJC_DCP_NBR = 'PLAN'
AND MJC_RCP_PTF_CD = 'RCP_ID'
AND ( MJC_COMP_XPDT IS NULL
OR MJC_COMP_XPDT > 'RunDate' )
) AS SUBTABLE1
WHERE ( MSPERFRM.MSP_SEC_ID = SUBTABLE1.MJC_SEC_ID )
AND MSPERFRM.MSP_MTH_STR_DT = 'RunDate'
) AS SUBTABLE2 ) AS SUBTABLE3 ) AS SUBTABLE4 ) AS SUBTABLE5
edit: remove superfluous comma
Hammerite fucked around with this message at 13:11 on Jun 9, 2009
|
#
?
Jun 8, 2009 22:16
|
|
- Squashy Nipples
- Aug 18, 2007
-
|
Hmmm... Interesting. The only problem is that I need that for EVERY column, as its possible that they swap in a new share class with no performance at all. 1-Month is the ONLY one we expect to have no Nulls.
I'll take a crack at this later. THANK YOU for the input!
|
#
?
Jun 9, 2009 12:59
|
|
- Hammerite
- Mar 9, 2007
-
And you don't remember what I said here, either, but it was pompous and stupid.
-
Jade Ear Joe
|
actually this approach doesn't need that many "levels" of subquery, here is a better version with one fewer level
code:SELECT
MON1, MON3, YTD, YR1,
(CASE YR3CHECK WHEN 0 THEN YR3 ELSE NULL) AS YR3,
(CASE YR5CHECK WHEN 0 THEN YR5 ELSE NULL) AS YR5,
(CASE YR10CHECK WHEN 0 THEN YR10 ELSE NULL) AS YR10
FROM (
SELECT
(SUM(MON1)/100) AS MON1, (SUM(MON3)/100) AS MON3,
(SUM(YTD)/100) AS YTD, (SUM(YR1)/100) AS YR1,
(((POWER(((SUM(YR3)/10000)+1),(1.0/3.0)))-1)*100) AS YR3,
(((POWER(((SUM(YR5)/10000)+1),(1.0/5.0)))-1)*100) AS YR5,
(((POWER(((SUM(YR10)/10000)+1),(1.0/10.0)))-1)*100) AS YR10,
(SUM(YR3CHECK)) AS YR3CHECK,
(SUM(YR5CHECK)) AS YR5CHECK,
(SUM(YR10CHECK)) AS YR10CHECK
FROM (
SELECT
(PERCENT * MON1) AS MON1,
(PERCENT * MON3) AS MON3,
(PERCENT * YTD) AS YTD,
(PERCENT * YR1) AS YR1,
(PERCENT * YR3) AS YR3,
(PERCENT * YR5) AS YR5,
(PERCENT * YR10) AS YR10,
(CASE WHEN YR3 IS NULL THEN 1 ELSE 0) AS YR3CHECK,
(CASE WHEN YR5 IS NULL THEN 1 ELSE 0) AS YR5CHECK,
(CASE WHEN YR10 IS NULL THEN 1 ELSE 0) AS YR10CHECK
FROM (
SELECT MJC_SEC_ID AS SEC_ID, MJC_ALCTN_PCT AS PERCENT,
MSP_CMLV_1MTHR_PCT AS MON1, MSP_CMLV_3MTHR_PCT AS MON3,
MSP_CMLV_YTDR_PCT AS YTD, MSP_CMLV_1YRR_PCT AS YR1,
MSP_CMLV_3YRR_PCT AS YR3, MSP_CMLV_5YRR_PCT AS YR5,
MSP_CMLV_10YRR_PCT AS YR10
FROM MSPERFRM, (
SELECT DISTINCT
MJC_SEC_ID, MJC_ALCTN_PCT
FROM MJCOMP
WHERE MJC_DCP_NBR = 'PLAN'
AND MJC_RCP_PTF_CD = 'RCP_ID'
AND ( MJC_COMP_XPDT IS NULL
OR MJC_COMP_XPDT > 'RunDate' )
) AS SUBTABLE1
WHERE ( MSPERFRM.MSP_SEC_ID = SUBTABLE1.MJC_SEC_ID )
AND MSPERFRM.MSP_MTH_STR_DT = 'RunDate'
) AS SUBTABLE2 ) AS SUBTABLE3 ) AS SUBTABLE4
|
#
?
Jun 9, 2009 13:18
|
|
- IcedPee
- Jan 11, 2008
-
Yarrrr! I be here to plunder the fun outta me workplace! Avast!
FREE DECAHEDRON!
|
I'm going to preface this by saying I am absolutely terrible with sql and our DBA is out, so I have to do it. I have a simple stored procedure:
code:SELECT proposal.proposalid,
review.proposalid,
review.evaluator
FROM dbo.proposal
INNER JOIN review
ON proposal.proposalid = review.proposalid
WHERE review.evaluator = @UserName
Gets a list of reviewed proposals by @UserName. My problem is that I need to do a second query that returns the opposite of this. I need to get the list of proposals not yet evaluated by @UserName (or more simply, every proposal that wasn't returned by the above query). Every time I try to come up with a solution it just seems.. well, terrible, and I think I'm making it much more difficult than it needs to be (or just completely missing/forgetting the right operators).
|
#
?
Jun 9, 2009 16:27
|
|
- IcedPee
- Jan 11, 2008
-
Yarrrr! I be here to plunder the fun outta me workplace! Avast!
FREE DECAHEDRON!
|
Not quite. That just returns the list of proposals that have been reviewed by people other than @UserName (and can return the same proposal multiple times since more than one other person could have reviewed it), but it doesn't take into account that @UserName could have also reviewed that proposal. If a record exists where @UserName has reviewed the proposal, I don't want that proposal to return in my query.
Edit: I wish I was better at this, but I'll try to pseudocode my thinking
code:SELECT proposal.proposalid,
review.proposalid,
review.evaluator
FROM dbo.proposal
INNER JOIN review
ON proposal.proposalid = review.proposalid
WHERE Review.Evaluator = @UserName DOES NOT EXIST
IcedPee fucked around with this message at 17:53 on Jun 9, 2009
|
#
?
Jun 9, 2009 17:43
|
|
- IcedPee
- Jan 11, 2008
-
Yarrrr! I be here to plunder the fun outta me workplace! Avast!
FREE DECAHEDRON!
|
OK, that's got it. Thanks!
|
#
?
Jun 9, 2009 18:31
|
|
- Squashy Nipples
- Aug 18, 2007
-
|
Update on my last two issues:
Yes, you do need to specify the internal dummy table in order to get that syntax of "EXISTS" to work properly. Once again, a big F-U to IBM for poor documentation of the "EXISTS" clause.
This works under DB2:
code:SELECT ( CASE WHEN EXISTS (
SELECT * FROM MSPERFRM WHERE MSP_MTH_STR_DT = 'RunDate'
) THEN 1 ELSE 0 END )
FROM SYSIBM.SYSDUMMY1
As for keepign track of Nulls, Hammerite's code worked great, and its still wicked fast. Here is the final SQL I used:
code:SELECT
(CASE WHEN MON1CHECK = 0 THEN MON1 ELSE NULL END) AS MON1,
(CASE WHEN MON3CHECK = 0 THEN MON3 ELSE NULL END) AS MON3,
(CASE WHEN YTDCHECK = 0 THEN YTD ELSE NULL END) AS YTD,
(CASE WHEN YR1CHECK = 0 THEN YR1 ELSE NULL END) AS YR1,
(CASE WHEN YR3CHECK = 0 THEN YR3 ELSE NULL END) AS YR3,
(CASE WHEN YR5CHECK = 0 THEN YR5 ELSE NULL END) AS YR5,
(CASE WHEN YR10CHECK = 0 THEN YR10 ELSE NULL END) AS YR10
FROM (
SELECT
(SUM(MON1)/100) AS MON1, (SUM(MON3)/100) AS MON3,
(SUM(YTD)/100) AS YTD, (SUM(YR1)/100) AS YR1,
(((POWER(((SUM(YR3)/10000)+1),(1.0/3.0)))-1)*100) AS YR3,
(((POWER(((SUM(YR5)/10000)+1),(1.0/5.0)))-1)*100) AS YR5,
(((POWER(((SUM(YR10)/10000)+1),(1.0/10.0)))-1)*100) AS YR10,
SUM(MON1CHECK) AS MON1CHECK, SUM(MON3CHECK) AS MON3CHECK,
SUM(YTDCHECK) AS YTDCHECK, SUM(YR1CHECK) AS YR1CHECK,
SUM(YR3CHECK) AS YR3CHECK, SUM(YR5CHECK) AS YR5CHECK,
SUM(YR10CHECK) AS YR10CHECK
FROM (
SELECT
(PERCENT * MON1) AS MON1, (PERCENT * MON3) AS MON3,
(PERCENT * YTD) AS YTD, (PERCENT * YR1) AS YR1,
(PERCENT * YR3) AS YR3, (PERCENT * YR5) AS YR5,
(PERCENT * YR10) AS YR10,
(CASE WHEN MON1 IS NULL THEN 1 ELSE 0 END) AS MON1CHECK,
(CASE WHEN MON3 IS NULL THEN 1 ELSE 0 END) AS MON3CHECK,
(CASE WHEN YTD IS NULL THEN 1 ELSE 0 END) AS YTDCHECK,
(CASE WHEN YR1 IS NULL THEN 1 ELSE 0 END) AS YR1CHECK,
(CASE WHEN YR3 IS NULL THEN 1 ELSE 0 END) AS YR3CHECK,
(CASE WHEN YR5 IS NULL THEN 1 ELSE 0 END) AS YR5CHECK,
(CASE WHEN YR10 IS NULL THEN 1 ELSE 0 END) AS YR10CHECK
FROM (
SELECT
MJC_SEC_ID AS SEC_ID, MJC_ALCTN_PCT AS PERCENT,
MSP_CMLV_1MTHR_PCT AS MON1, MSP_CMLV_3MTHR_PCT AS MON3,
MSP_CMLV_YTDR_PCT AS YTD, MSP_CMLV_1YRR_PCT AS YR1,
MSP_CMLV_3YRR_PCT AS YR3, MSP_CMLV_5YRR_PCT AS YR5,
MSP_CMLV_10YRR_PCT AS YR10
FROM MSPERFRM, (
SELECT DISTINCT
MJC_SEC_ID, MJC_ALCTN_PCT
FROM MJCOMP
WHERE MJC_DCP_NBR = 'PLAN'
AND MJC_RCP_PTF_CD = 'RCP_ID'
AND ( MJC_COMP_XPDT IS NULL
OR MJC_COMP_XPDT >= 'RunDate' )
AND MJC_COMP_EFDT <= 'RunDate'
) AS SUBTABLE1
WHERE ( MSPERFRM.MSP_SEC_ID = SUBTABLE1.MJC_SEC_ID )
AND MSPERFRM.MSP_MTH_STR_DT = 'RunDate'
) AS SUBTABLE2 ) AS SUBTABLE3 ) AS SUBTABLE4
Thanks to all who assisted.
|
#
?
Jun 10, 2009 19:34
|
|
- Bad Titty Puker
- Nov 3, 2007
-
-
Soiled Meat
|
IcedPee posted:
I'm going to preface this by saying I am absolutely terrible with sql and our DBA is out, so I have to do it. I have a simple stored procedure:
code:SELECT proposal.proposalid
review.proposalid,
review.evaluator
FROM dbo.proposal
INNER JOIN review
ON proposal.proposalid = review.proposalid
WHERE review.evaluator = @UserName
Gets a list of reviewed proposals by @UserName. My problem is that I need to do a second query that returns the opposite of this. I need to get the list of proposals not yet evaluated by @UserName (or more simply, every proposal that wasn't returned by the above query). Every time I try to come up with a solution it just seems.. well, terrible, and I think I'm making it much more difficult than it needs to be (or just completely missing/forgetting the right operators).
code:SELECT proposal.proposalid
FROM dbo.proposal
WHERE NOT EXISTS
(SELECT *
FROM review
WHERE review.proposalid = proposal.proposalid
AND review.evaluator = @UserName)
|
#
?
Jun 11, 2009 00:19
|
|
- Backhand
- Sep 25, 2008
-
|
I've been running into a brick wall on a particularly annoying SQL query for a few days now and figured it'd probably be child's play for you folks; I'm used to dealing with mostly fairly simple SQL and this is a step up.
The idea is to retrieve information on any account that has more than one active (CUTON) electrical service (ELEC). A single account can be linked to multiple locations and each location can potentially have more than one electrical service, though it's extremely unlikely. Table A contains a listing of all accounts, and links them to location numbers. It also tells me whether or not those locations are currently active. Table B contains entries for the types of services at each location. I've been trying to use the following query...
code posted:
SELECT a.account_no,
Count(b.service_tp)
FROM umsdata.um00252v a
INNER JOIN umsdata.um00140t b
ON a.location_no = b.location_no
WHERE b.service_tp = 'ELEC'
AND a.service_stat = 'CUTON'
GROUP BY a.account_no
HAVING Count(b.service_tp) > 1
But instead of only retrieving and counting electrical services, it retrieves any active account with an electric service and counts ALL their services, not just electrical - which is something I'd have thought my WHERE would prevent. Anyone have any thoughts?
|
#
?
Jun 11, 2009 14:43
|
|
- MoNsTeR
- Jun 29, 2002
-
|
Backhand posted:
But instead of only retrieving and counting electrical services, it retrieves any active account with an electric service and counts ALL their services, not just electrical - which is something I'd have thought my WHERE would prevent. Anyone have any thoughts?
Given your description and the query above, that would seem to be impossible. I would need to inspect the raw data to diagnose.
|
#
?
Jun 11, 2009 17:15
|
|
- Jethro
- Jun 1, 2000
-
I was raised on the dairy, Bitch!
|
If your query is doing what you say it's doing, it would seem as though Table A has a record for each account/location/service combination, not just account/location. And the fact that CUTON is a field in Table A makes me even more suspicious that this is the case. After all, I would think that a location could still have electrical service even if the gas was shut off (or whatever).
|
#
?
Jun 11, 2009 18:37
|
|
- Backhand
- Sep 25, 2008
-
|
Table A contains three fields of interest in this situation, account_no, location_no, and service_stat. It doesn't include anything about services more than their CUTON or CUTOFF status under service_stat; information about the individual services is found in table B. My thought was that by joining the tables I could get result sets like
Account: 1, Location: 1, Type: Elec
Account: 1, Location: 2, Type: Water
Account: 1, Location: 2, Type: Elec
Account: 2, Location: 3, Type: San
from the basic select, and then weed out any result that didn't have a type value of ELEC, such as results 2 and 4 above. I then just count every service that does get returned, since (supposedly) only elec services would show up, group 'em the whole mess by account number and have done with it. Instead it seems like using GROUP BY is slapping the whole mess together for each account if any of the services are electric, and counting all of them together: If a given account has one location, with water, sanitation, electric, and area light services, then my query is telling me it has 4 electrical services.
I don't know, it's starting to look like I simply can't pull it off with these two tables. I'd gladly talk over some of the others if I thought it would help, but there are probably at least a good hundred or so.
|
#
?
Jun 11, 2009 18:58
|
|
- Jethro
- Jun 1, 2000
-
I was raised on the dairy, Bitch!
|
Backhand posted:
Table A contains three fields of interest in this situation, account_no, location_no, and service_stat. It doesn't include anything about services more than their CUTON or CUTOFF status under service_stat; information about the individual services is found in table B. My thought was that by joining the tables I could get result sets like
Account: 1, Location: 1, Type: Elec
Account: 1, Location: 2, Type: Water
Account: 1, Location: 2, Type: Elec
Account: 2, Location: 3, Type: San
from the basic select, and then weed out any result that didn't have a type value of ELEC, such as results 2 and 4 above. I then just count every service that does get returned, since (supposedly) only elec services would show up, group 'em the whole mess by account number and have done with it. Instead it seems like using GROUP BY is slapping the whole mess together for each account if any of the services are electric, and counting all of them together: If a given account has one location, with water, sanitation, electric, and area light services, then my query is telling me it has 4 electrical services.
I don't know, it's starting to look like I simply can't pull it off with these two tables. I'd gladly talk over some of the others if I thought it would help, but there are probably at least a good hundred or so.
Given your description of the data, that's what should be happening, and putting in the group by shouldn't be changing that, unless your RDMBS is way more retarded than even mySQL. What are you using, anyway?
Also, what do your results look like if you don't put in the group by? The results you describe absolutely scream out that there is one record in Table A for each account/location/service.
Jethro fucked around with this message at 19:19 on Jun 11, 2009
|
#
?
Jun 11, 2009 19:15
|
|
- Backhand
- Sep 25, 2008
-
|
I finally managed to get it working; thanks for your help. WHen all was said and done I'd missed a field to link the two tables on: they each possessed an entry for SERVICE_SEQ, which is just the number of the actual service per location. I am honestly at a loss as for why this made the difference, but it did. This is the final, functioning SQL I came up with.
code posted:
SELECT a.account_no,
Count(* )
FROM umsdata.um00252v a
INNER JOIN umsdata.um00140t b
ON a.location_no = b.location_no
AND a.service_seq = b.serv_seq
AND a.service_stat = 'CUTON'
AND b.service_tp = 'ELEC'
GROUP BY a.account_no
HAVING Count(* ) > 1
|
#
?
Jun 11, 2009 21:57
|
|
- Little Brittle
- Nov 1, 2004
-
Come visit me dawg
|
I'm trying to figure out a query to pull similar articles based on the highest number of matching tags.
Here is some example MYSQL table data:
code: TABLE posts
id | title
-----------------------------
6 | Amazing Stories
7 | Interesting Finds
8 | This Car is Cool
TABLE tags
id | Name
-----------------------------
12 | weird
48 | funny
49 | interesting
50 | sad
51 | video
123 | image
TABLE tagmap
post_id | tag_id
-----------------------------
6 | 48
6 | 49
7 | 123
7 | 50
7 | 51
7 | 12
8 | 48
8 | 49
8 | 50
Let's say posts.id = 8. How could I create a query to return 3 similar posts that share tags with posts.id 8? I would like to order results by the highest number of shared tags first. I would also like to exclude posts.id 8 from the results. Could anyone help with this? It seems easy enough, but I can't get the results I want from my testing.
Little Brittle fucked around with this message at 21:46 on Jun 13, 2009
|
#
?
Jun 13, 2009 21:11
|
|
- Bad Titty Puker
- Nov 3, 2007
-
-
Soiled Meat
|
Little Brittle posted:
I'm trying to figure out a query to pull similar articles based on the highest number of matching tags.
Let's say posts.id = 8. How could I create a query to return 3 similar posts that share tags with posts.id 8? I would also like to exclude posts.id 8 from the results.
this query (untested) retrieves all the posts that share tags with postid 8:
code:select
posts.id
from
posts
where
posts.id <> 8
and exists
(select 1
from
tagmap
where tagmap.post_id <> 8
and exists
(select 1
from tagmap t2
where t2.tag_id = tagmap.tag_id
and t2.post_id = 8)
)
quote:
I would like to order results by the highest number of shared tags first.
This part varies depending on what DBMS and version is used.
|
#
?
Jun 13, 2009 23:11
|
|
- Little Brittle
- Nov 1, 2004
-
Come visit me dawg
|
camels posted:
this query (untested) retrieves all the posts that share tags with postid 8:
This part varies depending on what DBMS and version is used.
Thanks for the help. I'm using MySQL 5.0.22
|
#
?
Jun 14, 2009 02:57
|
|
- Bad Titty Puker
- Nov 3, 2007
-
-
Soiled Meat
|
Little Brittle posted:
Thanks for the help. I'm using MySQL 5.0.22
code:select
posts.id, count(*) as tagcount
from
posts
join
tagmap t on t.post_id = posts.id
where
posts.id <> 8
and exists
(select 1
from
tagmap
where tagmap.post_id <> 8
and exists
(select 1
from tagmap t2
where t2.tag_id = tagmap.tag_id
and t2.post_id = 8)
)
group by posts.id
order by tagcount desc
|
#
?
Jun 14, 2009 09:21
|
|
- Little Brittle
- Nov 1, 2004
-
Come visit me dawg
|
Works perfectly, thanks for the help.
|
#
?
Jun 14, 2009 22:33
|
|
- smith7800
- Sep 25, 2004
-
|
How can you make a field's value a result of an operation on two other fields in the same table?
|
#
?
Jun 15, 2009 10:20
|
|
- smith7800
- Sep 25, 2004
-
|
Triple Tech posted:
code:select
my_sum as a_column + b_column
from numbas
Is there a way to build it into the structure of the table as opposed to using a statement?
|
#
?
Jun 15, 2009 11:07
|
|
- Jethro
- Jun 1, 2000
-
I was raised on the dairy, Bitch!
|
Backhand posted:
I am honestly at a loss as for why this made the difference, but it did.
You thought table a was a bunch of account-location combinations, but it was actually a list of account-service-location combinations. So your first query said, "Here are all the locations that have ELEC service. Give me the account-service-locations that correspond to those locations and are CUTON". The new query says, "Here are all the electrical service-locations. Give me the account-service-locations that correspond to those service-locations and are CUTON"
code:Table A
Account | Location | Service
Bob | Bob's house | Electrical
Bob | Bob's house | Water
Bob | Bob's office | Electrical
Bob | Bob's office | Water
Bob | Bob's office | Gas
Jim | Bob's house | Electrical
Jim | Bob's house | Water
Jim | Bob's office | Electrical
Jim | Bob's office | Gas
Ed | Ed's house | Water
Ed | Bob's office | Gas
Table B
Location | Service
Bob's house | Electrical
Bob's house | Water
Bob's office | Electrical
Bob's office | Water
Bob's office | Gas
Ed's house | Water
Old Query "Results" (match A.Location to B.Location)
Account | A.Location | A.Service | B.Location | B.Service
Bob | Bob's house | Electrical | Bob's house | Electrical
Bob | Bob's house | Water | Bob's house | Electrical
Bob | Bob's office | Electrical | Bob's office | Electrical
Bob | Bob's office | Water | Bob's office | Electrical
Bob | Bob's office | Gas | Bob's office | Electrical
Jim | Bob's house | Electrical | Bob's house | Electrical
Jim | Bob's house | Water | Bob's house | Electrical
Jim | Bob's office | Electrical | Bob's office | Electrical
Jim | Bob's office | Gas | Bob's office | Electrical
Ed | Bob's office | Gas | Bob's office | Electrical
Bob, 5
Jim, 4
Ed, 1
New Query "Results" (match A.Location to B.Location and A.Service to B.Service)
Account | A.Location | A.Service | B.Location | B.Service
Bob | Bob's house | Electrical | Bob's house | Electrical
Bob | Bob's office | Electrical | Bob's office | Electrical
Jim | Bob's house | Electrical | Bob's house | Electrical
Jim | Bob's office | Electrical | Bob's office | Electrical
Bob, 2
Jim, 2
Does this make any more sense to you?
|
#
?
Jun 16, 2009 19:10
|
|
- Agrikk
- Oct 17, 2003
-
Take care with that! We have not fully ascertained its function, and the ticking is accelerating.
|
Probably a simple question: I have some data that looks like this:
code:
MatchID Time Mod KillerName Weapon VictimName
1215673061 1215672750 DEV Entrails DEVRL 0megaMan[APE]
1215673061 1215672838 DES 0megaMan[APE] DES Entrails
1215673061 1215672909 DEV Entrails DEVRL 0megaMan[APE]
1215673663 1215673144 DEV 0megaMan[APE] DEVRL Entrails
1215673663 1215673148 DEV 0megaMan[APE] DEVRL Entrails
1215673663 1215673162 DEV 0megaMan[APE] DEVRL Entrails
1215673663 1215673426 SUICIDE Entrails DEVRL Entrails
1215673663 1215673623 FALLING Entrails GRUNT Entrails
1215674570 1215673725 TRIGGER_HURT Entrails GRUNT Entrails
1215731611 1215730773 DEV Entrails DEVCG OutCa$t[APE]
1215731611 1215730797 DEV Entrails DEVCG OutCa$t[APE]
1215731611 1215730861 DEV OutCa$t[APE] DEVRL Entrails
1215732518 1215731666 DES Entrails DES OutCa$t[APE]
1215732518 1215731675 DES Entrails DES OutCa$t[APE]
1215732518 1215731919 DEV Entrails DEVRL OutCa$t[APE]
1215756302 1215756117 TRIGGER_HURT Entrails GRUNT Entrails
1215756302 1215756164 CHAINGUN Entrails GRUNT 0megaMan[APE]
1215757207 1215756362 DEV Entrails DEVCG 0megaMan[APE]
I am trying to count up the number of matches a player has been in. What I think I should do is:
- for every match ID, check to see if a PlayerName appears as either KillerName or VictimName
- If so, add 1 to MatchCount(PlayerName) then move on.
- perform this check for every player
But that seems to be really inefficient to scroll through every kill ever done on the server for each player.
How can I achieve the same result more elegantly?
|
#
?
Jun 17, 2009 03:07
|
|
- Jethro
- Jun 1, 2000
-
I was raised on the dairy, Bitch!
|
code:select count(distinct matchID) matches,
KillerName
from table
group by KillerName
|
#
?
Jun 17, 2009 04:36
|
|
- Adbot
-
ADBOT LOVES YOU
|
|
#
?
Jun 8, 2024 06:42
|
|
- Agrikk
- Oct 17, 2003
-
Take care with that! We have not fully ascertained its function, and the ticking is accelerating.
|
Jethro posted:
code:select count(distinct matchID) matches,
KillerName
from table
group by KillerName
That'll work for seeing if PlayerName has scored any kills in the match. How do I check to see if PlayerName has scored either a kill or a death?
How do I do the check on either KillerName or VictimName?
|
#
?
Jun 17, 2009 06:17
|
|