SQL / PERL advice on running multiple queries?

J

jdr0317

New member
Okay, so I'm trying to come up w/ the correct statement for this, but unfortunately I am new to writing perl and could use some help.

So from a table, I am getting 4 columns (for the sake of not giving away company info, let's just call it Column A, Column B, Column C, and Column D). What I want to do is group by column A, and then make an alert if WordB appears in Column B for corresponding term in Column A over 2% of the time, if WordC appears less than 30% for Column A term, and if LIKE '%wordD1%' OR '%wordD2%' appears over 5% of the time for corresponding A.

An example could be this: If the word "football" is one of the terms in column A, then if kick appears 3 times in column B, flag, or if punt appears under 30% in Column C, flag, or if pass or throw appear over 5% of the time in Column D, flag. Could I do this all in one ensted if statement? Would I need multiple scripts for this?

Any help is appreciated.
 
I would think you would need multiple queries for this, since it sounds like the conditions aren't mutally exclusive.

The first one would be the simplest, giving you base counts by column A and would be processed as a cursor. Then for each row of the cursor, you have queries using the current cursor row's column A value to count the number of rows that fit each of your alert criteria and see if the ratio to the total exceeds the threshhold. Not sure about exact syntax, as I don't do PERL, but it'd be along the lines of

Base query cursor defined as:
SELECT colA, COUNT(*) as colA_rows FROM someTable GROUP BY colA

Cursor rows FETCHed in each iteration into local variables @colA and @colA_rows and then

SELECT COUNT(*) INTO @colB_rows FROM someTable
WHERE colA = @colA AND colB = 'WordB'

If @colB_rows / @colA_rows > 0.02
<whatever colB alert code needed>

etc.

You might be able to do all this in a single SQL statement, but it'd probably have multiplely-correllated subqueries, be a bear to write/debug and likely crawl like a snail when processing...sometimes the simple stepwise approach is best.
 
I would think you would need multiple queries for this, since it sounds like the conditions aren't mutally exclusive.

The first one would be the simplest, giving you base counts by column A and would be processed as a cursor. Then for each row of the cursor, you have queries using the current cursor row's column A value to count the number of rows that fit each of your alert criteria and see if the ratio to the total exceeds the threshhold. Not sure about exact syntax, as I don't do PERL, but it'd be along the lines of

Base query cursor defined as:
SELECT colA, COUNT(*) as colA_rows FROM someTable GROUP BY colA

Cursor rows FETCHed in each iteration into local variables @colA and @colA_rows and then

SELECT COUNT(*) INTO @colB_rows FROM someTable
WHERE colA = @colA AND colB = 'WordB'

If @colB_rows / @colA_rows > 0.02
<whatever colB alert code needed>

etc.

You might be able to do all this in a single SQL statement, but it'd probably have multiplely-correllated subqueries, be a bear to write/debug and likely crawl like a snail when processing...sometimes the simple stepwise approach is best.
 
Back
Top