Monday, March 26, 2012

Migrating Access Query to SQL

I have complicated queries in access I want to migrate to SQL. They're
"complicated" only in the sense that I use the results of one calculated
field in an imbedded if (iif) of another calculated field, which in turn is
used in another imbedded if in another calculated field.
I replaced the iif with a CASE statement, and it worked for the first few
fields. I replace the field name with the (case statement) query since (as
far as I know) you can't use a calculated field as part of another field
like you can in access. This is okay until I get out a few fields and the
calculations become too large to be readable.
Here is an example query from access. (No problems until I get to variable
E, then it gets ridiculous)
A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
B: Len(A)
C: Len(myField2)
D: B + C - 10
E: IIF(B-C < 4, IIF(B<4,B,4), B-C)--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
E:
CASE WHEN B-C < 4
THEN CASE WHEN B < 4
THEN B
ELSE 4
END
ELSE B-C
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
gmxaj6LDDbQ5H8by4jsPyu+C
=7xKC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Try,

> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
case when (b - c) < 4 then case when b < 4 then b else 4 end else b - c end
AMB
"Jed Perlowin" wrote:

> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Thank you for your reply. However, the logic isn't my problem -- but the
fact that you can use B, C, and D in the query (as you have below) in
Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
them directly in SQL (and must expand each calculated field with it's
query).
"MGFoster" <me@.privacy.com> wrote in message
news:RpSLd.4078$cl1.1991@.newsread3.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> E:
> CASE WHEN B-C < 4
> THEN CASE WHEN B < 4
> THEN B
> ELSE 4
> END
> ELSE B-C
> END
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
> gmxaj6LDDbQ5H8by4jsPyu+C
> =7xKC
> --END PGP SIGNATURE--
>
> Jed Perlowin wrote:
is
few
(as
the
variable|||"Jed Perlowin" <jed@.perlowin.com> wrote in message
news:ObcPAMKCFHA.1424@.TK2MSFTNGP09.phx.gbl...
>I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn
> is
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since
> (as
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to
> variable
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
Use nested selects to refer to the results of computed columns by alias.
You must add a nested select for each level of recursion in your query.
SELECT A,B,C,B+C-10 D,
CASE WHEN B-C<2 THEN
CASE WHEN B<4 THEN B ELSE 4 END
ELSE B-C END E
FROM
(
SELECT A, Len(A) B, Len(MyField2) C
FROM
(
SELECT
IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField) A,
MyField2
FROM T
) DT
)DT2
David|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Yes, you're correct: you can't use aliases in calculations. IOW,
Access SQL:
(col1 + col2)/ col3 As A, A + 25 As B
SQL'r syntax:
(col1 + col2) / col3 As A,
((col1 + col2) / col3) + 25 As B
David Browne has a interesting solution that, unfortunately, doesn't
reduce the complexity of the query, but does allow you to use aliases in
calculations. In essence, he used tabular subqueries to alias the
calculations.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/ 4R4echKqOuFEgEQJOXQCgx5V7nCjfwSmy2uVFzQG
lMlXuxm8AoLfz
OWxitX3IfYip6FasKyI2I88+
=mIEC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> Thank you for your reply. However, the logic isn't my problem -- but the
> fact that you can use B, C, and D in the query (as you have below) in
> Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
> them directly in SQL (and must expand each calculated field with it's
> query).
>

No comments:

Post a Comment