[SOLVED] SQL Horror

[VisioX]

I have a slight problem with my SQL statement.
I hope one of you can help me out with some ideas.

Code Select Expand


select
"EQPMASFL"."Company Code" AS "EMCMP",
"EQPMASFL"."Equipment Number" AS "EMEQP#",
"EQPMASFL"."Mi/Hr from old meter" AS "EMPMMI"
,
"RAEMHSFL"."RNMHO" AS "RNMHO"

from
"dEquipmentMasterFile" "EQPMASFL"

left outer join
(
select
case
when exists (
select top 1 RNCMP from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
) and "RNEQP#" = "EMEQP#"
order by RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
) then (
select top 1 RNCMP from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
)
order by RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
)
when exists (
select top 1 RNCMP from fRAEMHSFL
order by RNMTDT asc, RNMTTM asc, RNSMF# asc, RNSEQ# desc
) then (
select top 1 RNCMP from fRAEMHSFL
order by RNMTDT asc, RNMTTM asc, RNSMF# asc, RNSEQ# desc
)
end AS RNCMP,
case
when exists (
select top 1 RNEQP# from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
)
order by RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
) then (
select top 1 RNEQP# from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
)
order by RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
)
when exists (
select top 1 RNEQP# from fRAEMHSFL
order by RNMTDT asc, RNMTTM asc, RNSMF# asc, RNSEQ# desc
) then (
select top 1 RNEQP# from fRAEMHSFL
order by RNMTDT asc, RNMTTM asc, RNSMF# asc, RNSEQ# desc
)
end AS RNEQP#,
case
when exists (
select top 1 RNMHO from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
)
order by RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
) then (
select top 1 RNMHO from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
)
order by RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
)
when exists (
select top 1 RNMHO from fRAEMHSFL
order by RNMTDT asc, RNMTTM asc, RNSMF# asc, RNSEQ# desc
) then (
select top 1 RNMHO from fRAEMHSFL
order by RNMTDT asc, RNMTTM asc, RNSMF# asc, RNSEQ# desc
)
end AS RNMHO
) "RAEMHSFL"

on
"RAEMHSFL"."RNCMP" = "EQPMASFL"."Company Code" and
"RAEMHSFL"."RNEQP#" = "EQPMASFL"."Equipment Number"

where "Equipment Number" = '76372'

My problem is that I need this in line 18 (and 11 more times in every select) -> "RNEQP#" = "EMEQP#"
EMEQP# is considered as 'Invalid column name'.
It it possible to get EMEQP# in there?

The last line is just for lux to test the SQL.

This is just a part of the whole statement, which is right now 269 lines long.

Thanks and a happy new year

[VisioX]

Problem solved.

Code Select Expand

select distinct
"EQPMASFL"."Company Code" AS "EMCMP",
"EQPMASFL"."Equipment Number" AS "EMEQP#"
,
"fRAEMHSFL"."RNMHO" AS "RNMHO"

from
"dEquipmentMasterFile" "EQPMASFL"

join
"fRAEMHSFL" "fRAEMHSFL"
on
"Equipment Number" = "fRAEMHSFL"."RNEQP#" and
"fRAEMHSFL"."RNMHO" =
(
case
when exists (
select top 1 RNMHO from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
) and "fRAEMHSFL"."RNCMP" = "Company Code" and "fRAEMHSFL"."RNEQP#" = "Equipment Number"
order by RNCMP asc, RNEQP# asc, RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
) then (
select top 1 RNMHO from fRAEMHSFL where RNMSG# in (
select EAMSG# from dEQPSTDFL where EASRWD = 'FULL'
) and "fRAEMHSFL"."RNCMP" = "Company Code" and "fRAEMHSFL"."RNEQP#" = "Equipment Number"
order by RNCMP asc, RNEQP# asc, RNMTDT desc, RNMTTM desc, RNSMF# desc, RNSEQ# asc
)
else (
select top 1 RNMHO from fRAEMHSFL
where "RNCMP" = "Company Code" and "RNEQP#" = "Equipment Number"
order by RNCMP asc, RNEQP# asc, RNMTDT asc, RNMTTM asc, RNSMF# asc, RNSEQ# desc
)
end)