SQL If statements-CodePudding

IIf(#LASTCYCLEGASINJ.[rb gas]<=0,"", #LASTCYCLEWTRINJ.[RB WTR]/#LASTCYCLEGASINJ.RBGAS AS [WTR INJ/GAS INJ],

I have all my code working expect for the If statement. I know it needs a condition but I have tried many different things. Any help appreciated. Here is the entire code but everything seems to work but the If statements.

SELECT #ACTORSIINJ.UCI, #ACTORSIINJ.NAME, 
    #LASTCYCLEDAYS.CountOfDate AS DAYSLASTCYCLE, 
    #LASTCYCLEWTRINJ.DAYSWTR, #LASTCYCLEGASINJ.[DAYS GAS], 
    #LASTCYCLEGASINJ.AvgOfIGASVOL, #LASTCYCLEGASINJ.[RB GAS], 
    #LASTCYCLEWTRINJ.AvgOfIWTRVOL, #LASTCYCLEWTRINJ.RBWTR, 
    IIf(#LASTCYCLEGASINJ.[RB GAS]*#LASTCYCLEGASINJ.[DAYS GAS]<=0,(#LASTCYCLEWTRINJ.[RB WTR]*#LASTCYCLEWTRINJ.[DAYS WTR])/(#LASTCYCLEGASINJ.[RB GAS]*#LASTCYCLEGASINJ.[DAYS GAS]) AS WAGRATIO,
    IIf(#LASTCYCLEGASINJ.[rb gas]<=0,"", #LASTCYCLEWTRINJ.[RB WTR]/#LASTCYCLEGASINJ.RBGAS AS [WTR INJ/GAS INJ], 
    #LAST90DAYWTRINJ.AvgOfIWTRVOL AS [Last 90 day wtr], #ACTORSIINJ.CoType, #ACTORSIINJ.CoPrdStatus, #InstUtilizationsStep3.[SumOfALLOCATED OIL],
    #InstUtilizationsStep3.[SumOfALLOCATED GAS INJ], #InstUtilizationsStep3.Utilizations, #ACTORSIINJ.PatternName
into #LastCycleWagMetrics
FROM #InstUtilizationsStep3
RIGHT JOIN 
(
    (
       (
           (
              (#ACTORSIINJ LEFT JOIN #LAST90DAYGASINJ ON #ACTORSIINJ.UCI = #LAST90DAYGASINJ.UCI) 
               LEFT JOIN #LAST90DAYWTRINJ ON #ACTORSIINJ.UCI = #LAST90DAYWTRINJ.UCI) 
            LEFT JOIN #LASTCYCLEDAYS ON #ACTORSIINJ.UCI = #LASTCYCLEDAYS.UCI) 
        LEFT JOIN #LASTCYCLEWTRINJ ON #ACTORSIINJ.UCI = #LASTCYCLEWTRINJ.UCI) 
    LEFT JOIN #LASTCYCLEGASINJ ON #ACTORSIINJ.UCI = #LASTCYCLEGASINJ.UCI
) ON #InstUtilizationsStep3.PatternName = #ACTORSIINJ.PatternName

CodePudding user response：

In addition to the missing parentheses that others have mentioned, I believe you are going to run into an error with have a string type in the second parameter and a number in the third parameter of the iif. I would change the "" to null or 0

CodePudding user response：

It looks like the IIF() functions are missing their right closing parentheses ()).

Both the short example at the top and the place where it's repeated in the larger block from the question follow this form:

IIf( A, B, C As ColumnName

When it should look like this:

IIf(A, B, C) As ColumnName

Next, here is the better way to write those JOINs. Note how it also rearranged the order so the RIGHT JOIN could be a LEFT JOIN instead. Every RIGHT JOIN can always be rewritten as a LEFT JOIN, and it is almost always beneficial to do so. Logicially both versions will have the same result, but this will be MUCH easier to reason about and maintain.

FROM #ACTORSIINJ a
LEFT JOIN #LAST90DAYGASINJ l90g ON a.UCI = l90g.UCI
LEFT JOIN #LAST90DAYWTRINJ l90w ON a.UCI = l90w.UCI
LEFT JOIN #LASTCYCLEDAYS cd ON a.UCI = cd.UCI
LEFT JOIN #LASTCYCLEWTRINJ cw ON a.UCI = cw.UCI
LEFT JOIN #LASTCYCLEGASINJ cg ON a.UCI = cg.UCI
LEFT JOIN #InstUtilizationsStep3 s3 ON s3.PatternName = a.PatternName

Now try this:

SELECT a.UCI, a.NAME, cd.CountOfDate AS DAYSLASTCYCLE, 
    cw.DAYSWTR, cg.[DAYS GAS], cg.AvgOfIGASVOL, cg.[RB GAS], 
    cw.AvgOfIWTRVOL, cw.RBWTR, 
    IIf(cg.[RB GAS]*cg.[DAYS GAS]<=0,
        NULL,
        (cw.[RB WTR]*cw.[DAYS WTR])/(cg.[RB GAS]*cg.[DAYS GAS]) ) AS WAGRATIO,
    IIf(cg.[rb gas]<=0,
        NULL,
        cw.[RB WTR]/cg.RBGAS) AS [WTR INJ/GAS INJ], 
    l90.AvgOfIWTRVOL AS [Last 90 day wtr], a.CoType, a.CoPrdStatus,
    s3.[SumOfALLOCATED OIL], s3.[SumOfALLOCATED GAS INJ], s3.Utilizations, 
    a.PatternName
into #LastCycleWagMetrics
FROM #ACTORSIINJ a
LEFT JOIN #LAST90DAYGASINJ l90g ON a.UCI = l90g.UCI
LEFT JOIN #LAST90DAYWTRINJ l90w ON a.UCI = l90w.UCI
LEFT JOIN #LASTCYCLEDAYS cd ON a.UCI = cd.UCI
LEFT JOIN #LASTCYCLEWTRINJ cw ON a.UCI = cw.UCI
LEFT JOIN #LASTCYCLEGASINJ cg ON a.UCI = cg.UCI
LEFT JOIN #InstUtilizationsStep3 s3 ON s3.PatternName = a.PatternName

Or this:

SELECT a.UCI, a.NAME, cd.CountOfDate AS DAYSLASTCYCLE, 
    cw.DAYSWTR, cg.[DAYS GAS], cg.AvgOfIGASVOL,cg.[RB GAS],
    cw.AvgOfIWTRVOL, cw.RBWTR, 
    CASE WHEN cg.[RB GAS]*cg.[DAYS GAS] > 0 
         THEN (cw.[RB WTR] * cw.[DAYS WTR])/(cg.[RB GAS] * cg.[DAYS GAS]) 
         END AS WAGRATIO,
    CASE WHEN cg.[rb gas]>0,
         THEN  cw.[RB WTR]/cg.RBGAS
         END AS [WTR INJ/GAS INJ], 
    l90w.AvgOfIWTRVOL AS [Last 90 day wtr], a.CoType, a.CoPrdStatus,
    s3.[SumOfALLOCATED OIL], s3.[SumOfALLOCATED GAS INJ], s3.Utilizations, 
    a.PatternName
into #LastCycleWagMetrics
FROM #ACTORSIINJ a
LEFT JOIN #LAST90DAYGASINJ l90g ON a.UCI = l90g.UCI
LEFT JOIN #LAST90DAYWTRINJ l90w ON a.UCI = l90w.UCI
LEFT JOIN #LASTCYCLEDAYS cd ON a.UCI = cd.UCI
LEFT JOIN #LASTCYCLEWTRINJ cw ON a.UCI = cw.UCI
LEFT JOIN #LASTCYCLEGASINJ cg ON a.UCI = cg.UCI
LEFT JOIN #InstUtilizationsStep3 s3 ON s3.PatternName = a.PatternName

Note this last version inverted the conditional operators. It also used CASE expressions instead of IIF(), which is the ansi standard way (IIf() is not part of the SQL standard).

Adding alias mnemonics also let us put everything into a reasonably-sized code window with no scrolling anywhere. It should be standard practice to use aliases in any query referencing at least three different tables or views.

Finally, that looks like a LOT of temp tables, which seems... well... calling it "sub-optimal" is putting it mildly. I'd expect most of the code to build up those temp tables could be re-written using Common Table Expressions, which could potentially improve performance here by multiple orders of magnitude.

CodePudding user response：

I got it the divide by zero error seemed to be hanging it up. SELECT #InstUtilizationsStep1.PatternName, Sum(#InstUtilizationsStep1.[ALLOCATED OIL]) AS [SumOfALLOCATED OIL], Sum(#InstUtilizationsStep1.[ALLOCATED GAS INJ]) AS [SumOfALLOCATED GAS INJ], IIf(Sum([allocated oil])=0,Null,Sum(#InstUtilizationsStep1.[allocated gas inj])/Sum([allocated oil])) AS Utilizations into #InstUtilizationsStep3 FROM #InstUtilizationsStep1 INNER JOIN #InstUtilizationsStep2 ON (#InstUtilizationsStep1.PatternName = #InstUtilizationsStep2.PatternName) AND (#InstUtilizationsStep1.Date = #InstUtilizationsStep2.Date) GROUP BY #InstUtilizationsStep1.PatternName