|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-11 : 08:29:53
|
Jesus writes "Okay I built a UNION based on 4 select statements that I will paste into this e-mail. This is my problem I have the first two select statments that don't have a column called CHK_NO, but in my last two selects the CHK_NO does exists. I tried passing in double quotes as you'll see and it does not work.
How do I fake this column in my first two selects. Thanks in advance. And here is my SQL UNION:
SELECT
aj_dtl.`pro_id` AS PRO_ID,
aj_dtl.`trn_dt` AS TRN_DATE,
aj_dtl.`gl_cr` AS TRN_CASH_ACCT,
coa.`name` AS ACCT_NAME,
trim(aj_dtl.`desc`) + " - " + aj.`desc_hdr` AS DESCRIPTION,
aj_dtl.`cash_type` AS ALL_TYPES,
"" AS Rcpt_ChkNo,
aj_dtl.`amt` AS BALANCE,
"ajcr" as selectid
FROM
`aj` aj,
`aj_dtl` aj_dtl,
`coa` coa
WHERE
aj.`jrn_id` = aj_dtl.`jrn_id` AND
aj_dtl.`pro_id` = coa.`pro_id` AND
aj_dtl.`gl_db` = coa.`acct_id` and
(aj_dtl.cash_type = "1" or aj_dtl.cash_type = "2")
UNION
SELECT
aj_dtl.`pro_id` AS PRO_ID,
aj_dtl.`trn_dt` AS TRN_DATE,
aj_dtl.`gl_db` AS TRN_CASH_ACCT,
coa.`name` AS ACCT_NAME,
trim(aj_dtl.`desc`) + " - " + aj.`desc_hdr` AS DESCRIPTION,
aj_dtl.`cash_type` AS ALL_TYPES,
"" AS Rcpt_ChkNo,
aj_dtl.`amt` AS BALANCE,
"ajdb" as selectid
FROM
`aj` aj,
`aj_dtl` aj_dtl,
`coa` coa
WHERE
aj.`jrn_id` = aj_dtl.`jrn_id` AND
aj_dtl.`pro_id` = coa.`pro_id` AND
aj_dtl.`gl_db` = coa.`acct_id` AND
(aj_dtl.cash_type = "1" or aj_dtl.cash_type = "2")
UNION
SELECT
ap_dtl.`pro_id` AS PRO_ID,
ap_dtl.`trn_dt` AS TRN_DATE,
ap_dtl.`gl_db` AS TRN_CASH_ACCT,
coa.`name` AS ACCT_NAME,
trim(ap_dtl.`desc`) + " - " + Vend.`name` AS Description,
ap_dtl.type AS ALL_Types,
ap_dtl.`chk_no` AS Rcpt_ChkNo,
ap_dtl.`amt` AS BALANCE,
"ap" as selectid
FROM
`ap` ap,
`ap_dtl` ap_dtl,
`coa` coa,
`vend` vend
WHERE
ap.`jrn_id` = ap_dtl.`jrn_id` AND
ap_dtl.`pro_id` = coa.`pro_id` AND
ap_dtl.`gl_db` = coa.`acct_id` and
ap.`id` = vend.`vend_id` AND
AP_DTL.TYPE = "P"
UNION
SELECT
ar_dtl.`pro_id` AS PRO_ID,
ar_dtl.`trn_dt` AS TRN_DATE,
ar_dtl.`gl_cr` AS TRN_CASH_ACCT,
coa.`name` AS ACCT_NAME,
trim(ar_dtl.`desc`) + " - " + ar.`name` AS Description,
ar_dtl.`type` AS ALL_TYPES,
ar_dtl.`chk_no` AS Rcpt_ChkNo,
ar_dtl.`amt` AS BALANCE,
"ar" as selectid
FROM
`ar` ar,
`ar_dtl` ar_dtl,
`coa` coa
WHERE
ar.`jrn_id` = ar_dtl.`jrn_id` AND
ar_dtl.`pro_id` = coa.`pro_id` AND
ar_dtl.`gl_cr` = coa.`acct_id` AND
(ar_dtl.type = "C" or ar_dtl.type = "P")" |
|
UNION SQL