var1ety posted:

Manually add a primary key constraint on the materialized views.

var1ety posted:

Clever SQL tricks.

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?

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 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

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?

Backhand posted:

Help

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.

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

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.

quote:

I would like to order results by the highest number of shared tags first.

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.

Little Brittle posted:

Thanks for the help. I'm using MySQL 5.0.22

camels posted:

smith7800 posted:

How can you make a field's value a result of an operation on two other fields in the same table?

Triple Tech posted:

code:

select
  my_sum as a_column + b_column

from numbas

Backhand posted:

I am honestly at a loss as for why this made the difference, but it did.

Jethro posted:

code:

select count(distinct matchID) matches,
       KillerName
from table
group by KillerName