Beginner SQL help

The Sandbox Software/Technology
141

I second the rounding issue. Adding decimal places might solve your issue, but as a rule I multiply by decimals instead of dividing by fractions if possible.

2 Likes
142

Does it work if you use 1.25/2.50?

There could be some sort of difficulty with division w/o forcing a cast on the calculation itself.

143

What do you mean by “This…does not work”?

Syntax errors & it doesn’t run at all?

Or results that are nonsensical, impossible, or just different from expectations?

144

I think you are running into implicit data type handling here.

POWER help

POWER ( float_expression , y )

Return Types

The return type depends on the input type of float_expression :

Input type Return type
float , real float
decimal( p , s ) decimal(38, s )
int , smallint , tinyint int
bigint bigint
money , smallmoney money
bit , char , nchar , varchar , nvarchar float

If the result does not fit in the return type, an arithmetic overflow error occurs.

So if bore is an integer, then POWER(bore,3) is an integer. 1/2 also evaluates to an integer (0) because I get
1/2POWER(iVarName,3) = 0
but
0.5POWER(iVarName,3) = (iVarName^3)/2

division operator
If an integer dividend is divided by an integer divisor , the result is an integer that has any fractional part of the result truncated.

1 Like
145

Not to put too fine a point on it, but when I’m doing a ratio of two integers, I always use this syntax:

CAST(SUM(field_1) AS FLOAT) / SUM(field_2)

However, CAST(1 AS FLOAT) / 2 * value is kinda overkill. If the value is an integer, do 0.5 * value. If the value is a decimal, you can use the syntax value / 2.

146

Yes, you can do it that way, or use 1/2.0, which is the equivalent of 1/2# in VBA.

147

Also, this is specific to SQL Server. We use a different platform for our data lake that doesn’t use T-SQL (Impala SQL). Explicitly converting the numerator to a decimal isn’t necessary. Which is odd because the language otherwise doesn’t do implicit conversions well e.g. char to numeric.

148

This would be so easy for me to do in SAS…I can’t come up with the syntax quickly for SQL.
Here’s my data:
Curr
policy, covgcode, reserve
a1,ul,500
a1,adb,25
a2,ul,750
a3,wl,666
a3,adb,42

Prev
policy, covgcode, reserve
a1,ul,495
a1,adb,24
a2,ul,755
a3,wl,777
a3,adb,69

I need to do an inner join on Curr & Prev on policy & covgcode to compare the reserve change, BUT I only want those policies that have an adb rider. I want all of the coverages but one of them must be an adb for each policy.

Joining the two is easy:

select policy, covgcode, c.reserve as res_curr, p.reserve as res_prev
from CURR C
inner join PREV P
on c.policy = p.policy
and c.covgcode = p.covgcode

Now I need to figure out how to join them against whatever this is considered to be:
select policy
from CURR
where covgcode = ‘adb’

select policy
from PREV
where covgcode = ‘adb’

150

Using SteveGrondin’s method, I may have figured it out:
https://community-new.goactuary.com/t/beginner-sql-help/1374/47?u=1695814

I’ve added a “WITH ADBPOLS AS (SELECT POLICY FROM CURR WHERE COVGCODE = ‘ADB’)”

…and then went on from there.

edit: yeah, that worked.

1 Like
151

My internet search has failed me so far. All I want to do is put a “title” above the results of my SQL query.

In SAS it was so easy…PROC PRINT; TITLE ‘TPS REPORT’; RUN;

Certainly, there must be a way to add a title in SQL.

152

Why?

153

Because I’m running about 12 queries all at once & I’d like an easy way to verify that I’m looking at the report that I think I’m looking at.

Otherwise, I have to copy/paste code & [F5] a dozen different times and my fingers are getting tired of doing that…and I’m having trouble keeping track of where I am and what I’ve done and what needs to be done. If I had a title I could say, "Oh, well, lookee here, this is the TPS report. I need the Balanced Scorecard Report.

154

Also, in SAS, when you have a report that sums up things by Group you can also display a sum for everything.

A 3
B 2
C 4

xxxxx

__9

Does SQL do that or do I have to copy/paste the output into Excel in order to get the total-total?

155

You could create the first field on the left with info about the report.

Not sure about a sum. I use SQL for pulling data. Not to create reports for publication.

2 Likes
156

Yeah, that’s a good idea. I think I’ll do that. :toth:

157

Better than :wotf:

158

Or create two: One that shows “info” about the table and a “second” that can indicate the “totals” row.

That second can also be used to show averages. And/or standard deviations.

The info column can be “empty” for most rows and show markers of sorts (if the table is going to be huge; HUGE!)

159

You can use ROLLUP, but I’ve never found it to be very useful:

1 Like
160

In SAS I used SELECT…WHEN…OTHERWISE…END quite a bit. It’s a better way, imo, to do IF…THEN…ELSE.

Here an example:

SELECT (LOB);
 WHEN ('A','B','C','D') CLS = 'WHATEVER';
 WHEN ('E','F','G','H') CLS = 'OKAYFINE';
 OTHERWISE CLS = 'YEAHSURE';
END;

I could also do something like this where I get rid of everything that would’ve been classified as ‘YEAHSURE’:

SELECT (LOB);
 WHEN ('A','B','C','D') CLS = 'WHATEVER';
 WHEN ('E','F','G','H') CLS = 'OKAYFINE';
 OTHERWISE DELETE;
END;

Is there an alternative to a bunch of nested IF THEN ELSE statements in SQL or is that the way it goes?

161

Case statement

3 Likes