Link to home
Start Free TrialLog in
Avatar of Keith McElroy
Keith McElroy

asked on

Need help with convert Informix SQL SELECT statement to Microsoft SQL

Informix:

SELECT 'BF' TABLE,
sum(GLTRANS.glt_cr) CREDIT1,
sum(GLTRANS.glt_dr) DEBIT1,
GLKEY.glk_grp_part02 FUND1,
GLTRANS.glt_gl_obj GL_OBJ,
GLTRANS.glt_gl_obj[1,1] TYPE1,
GLOBJ.glo_obj_dl DESCL,
GLOBJ.glo_bal_type BALTYPE

FROM glo_obj_mstr GLOBJ, glk_key_mstr GLKEY,
gl_budact GLBUDACT, OUTER glt_trns_dtl GLTRANS

WHERE GLBUDACT.gr = 'GL' and
GLBUDACT.fy = @PYR and
GLBUDACT.level = 'OB' and
GLBUDACT.obj <> '1' and
GLBUDACT.key <> '1' and
GLKEY.glk_grp_part02 = @GLKEY.PART2 and
GLTRANS.glt_date = @PYB and
GLTRANS.glt_type = 'BF' and
((GLBUDACT.gr = GLKEY.glk_gr and
GLBUDACT.key = GLKEY.glk_key and
GLBUDACT.gr = GLOBJ.glo_gr and
GLBUDACT.obj = GLOBJ.glo_obj and
GLBUDACT.gr = GLTRANS.glt_gl_gr and
GLBUDACT.fy = GLTRANS.glt_gl_fy and
GLBUDACT.key = GLTRANS.glt_gl_key and
GLBUDACT.obj = GLTRANS.glt_gl_obj))

GROUP BY 4,5,6,7,8
union all

SELECT 'ST' TABLE,
sum(GLTRANS.glt_cr) CREDIT1,
sum(GLTRANS.glt_dr) DEBIT1,
GLKEY.glk_grp_part02 FUND1,
GLTRANS.glt_gl_obj GL_OBJ,
GLTRANS.glt_gl_obj[1,1] TYPE1,
GLOBJ.glo_obj_dl DESCL,
GLOBJ.glo_bal_type BALTYPE

FROM glo_obj_mstr GLOBJ, glk_key_mstr GLKEY,
gl_budact GLBUDACT, OUTER glt_trns_dtl GLTRANS

WHERE GLBUDACT.gr = 'GL' and
GLBUDACT.fy = @SYSFISCALYEAR and
GLBUDACT.level = 'OB' and
GLBUDACT.obj <> '1' and
GLBUDACT.key <> '1' and
GLKEY.glk_grp_part02 = @GLKEY.PART2 and
GLTRANS.glt_date >= @FYB and
GLTRANS.glt_date <= @ONE_LESS and
GLTRANS.glt_type = 'ST' and
((GLBUDACT.gr = GLKEY.glk_gr and
GLBUDACT.key = GLKEY.glk_key and
GLBUDACT.gr = GLOBJ.glo_gr and
GLBUDACT.obj = GLOBJ.glo_obj and
GLBUDACT.gr = GLTRANS.glt_gl_gr and
GLBUDACT.fy = GLTRANS.glt_gl_fy and
GLBUDACT.key = GLTRANS.glt_gl_key and
GLBUDACT.obj = GLTRANS.glt_gl_obj))

GROUP BY 4,5,6,7,8
union all

SELECT 'TR' TABLE,
sum(GLTRANS.glt_cr) CREDIT1,
sum(GLTRANS.glt_dr) DEBIT1,
GLKEY.glk_grp_part02 FUND1,
GLTRANS.glt_gl_obj GL_OBJ,
GLTRANS.glt_gl_obj[1,1] TYPE1,
GLOBJ.glo_obj_dl DESCL,
GLOBJ.glo_bal_type BALTYPE

FROM glo_obj_mstr GLOBJ, glk_key_mstr GLKEY,
gl_budact GLBUDACT, OUTER glt_trns_dtl GLTRANS

WHERE GLBUDACT.gr = 'GL' and
GLBUDACT.fy = @SYSFISCALYEAR and
GLBUDACT.level = 'OB' and
GLBUDACT.obj <> '1' and
GLBUDACT.key <> '1' and
GLKEY.glk_grp_part02 = @GLKEY.PART2 and
GLTRANS.glt_date >= @SYSSTARTDATE and
GLTRANS.glt_date <= @REPORTDATE and
GLTRANS.glt_type = 'ST' and
((GLBUDACT.gr = GLKEY.glk_gr and
GLBUDACT.key = GLKEY.glk_key and
GLBUDACT.gr = GLOBJ.glo_gr and
GLBUDACT.obj = GLOBJ.glo_obj and
GLBUDACT.gr = GLTRANS.glt_gl_gr and
GLBUDACT.fy = GLTRANS.glt_gl_fy and
GLBUDACT.key = GLTRANS.glt_gl_key and
GLBUDACT.obj = GLTRANS.glt_gl_obj))

GROUP BY 4,5,6,7,8
ASKER CERTIFIED SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial