Question on Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Question on Query

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-07-09 : 07:32:54

I know I did something wrong with this query...Let me refine and provide you with the right information if I still got error.

Thanks for looking at ....

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-09 : 08:33:51

The first thing I noticed was that there is an extra comma after c.dln in your first select statement. Get rid of it and then see what happens.

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-09 : 08:53:05

Okay, we'll tackle this bit by bit. Your code is really hard to read.
It would be easier to debug if it were a bit more organized. But you are missing the close apostrophe here
and a.act_date >= '2008-06-13
and you haven't aliased the last table

where a.act_code='PY'
and a.act_date >= '2008-06-13
and (c.trans_code is null
or (c.trans_code='15' and c.process_date >='2008-06-13' and c.method_code not in ('3', '4') and c.tc_code not in ('06', '15'))
) XX

Let's see what that does

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-09 : 09:07:06

[code]select distinct
a.EIN
, case when c.process_date is not null then c.process_date else act_date end as PaymentDate,
GetDate() as EnteredDate
, act_code
, func_area
, a.state_code
, payment, 'O' as Ledger
, act_date
, a.CCH_PERIOD
, c.dln

from dba.p325cch a
left join
dba.debtshv b
on
a.ein = b.ein
left join
dba.ctopenmo c
on
b.ctbox = c.box
and a.payment = c.trans_amt
and c.trans_code in ('01', '15')
left join
dba.ctclosed d
on
b.ctbox = d.box
and a.payment = d.trans_amt
and d.trans_code in ('01', '15')
left join
(
select distinct box
, dln
, max(process_date) as Proc_Dt
, sum(trans_amt) as amt
from
(
select distinct box
, dln
, max(process_date) as Prc_Dt
, sum(trans_amt) as amt
from dba.ctopenmo

UNION ALL

select distinct box
, dln
, max(process_date) as Proc_Dt
, sum(trans_amt) as amt
from dba.ctclosed
where trans_code='08' and tc_code='01' and trans_amt > 0
group by box, dln, amt
) zz
) AA
on
c.box = AA.box
and c.process_date = AA.proc_dt
and AA.amt < c.trans_amt
and AA.box = d.box
and d.process_date = AA.proc_dt
and AA.amt < d.trans_amt

where a.act_code='PY'
and a.act_date >= '2008-06-13'
and
(
c.trans_code is null
or
(
c.trans_code='15' and c.process_date >='2008-06-13'
and c.method_code not in ('3', '4')
and c.tc_code not in ('06', '15')
)
)
[/code]

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-07-09 : 10:24:30

I got an aggregate function error when I tried the above code. But please see my updated code below. It works very well. However, I still need help with my last union query (red highlighted).

select a.EIN, case when cc.process_date is not null then cc.process_date else act_date end as PaymentDate ,
GetDate() as EnteredDate, a.act_code, a.func_area, a.state_code, a.payment,
case when dd.box is not null then 'C' else 'O' end as Ledger, a.act_date, a.CCH_PERIOD,
case when cc.dln is not null then cc.dln else dd.dln end as DLN
from dba.p325cch a
left join dba.debtshv b on a.ein = b.ein
left join (
select c.box, sum(c.trans_amt) as amt, c.trans_code, max(c.dln) as dln, c.process_date
from dba.ctopenmo c
inner join (
select distinct box, max(process_date) as Proc_dt, trans_amt
from dba.ctopenmo
where trans_code='08' and tc_code='01' and trans_amt >0
group by box, trans_amt) d on d.box=c.box and c.process_date=d.proc_dt
where c.trans_code in ('01', '15')
group by c.box, c.trans_code, c.process_date
) cc on b.ctbox=cc.box and a.payment=cc.amt

left join (
select d.box, sum(d.trans_amt) as amt, d.trans_code, max(d.dln) as dln, d.process_date
from dba.ctclosed d
inner join (
select distinct box, max(process_date) as Proc_dt, trans_amt
from dba.ctclosed
where trans_code='08' and tc_code='01' and trans_amt >0
group by box, trans_amt) e on e.box=d.box and d.process_date=e.proc_dt

where d.trans_code in ('01', '15')
group by d.box, d.trans_code, d.process_date
) dd on b.ctbox=dd.box and a.payment=dd.amt

where a.act_code='PY'
and a.act_date >= '2008-06-13'

UNION
select a.ein, process_date, GetDate() as EnteredDate, '01' as act_code,b.s, right(b.stcd,2), c.trans_amt,
case when b.ctbox is not null then 'C' else 'O' end as Ledger, process_date, a.PER,
case when c.dln is not null then c.dln else d.dln end as DLN

from dba.debtst a
left join dba.debtshv b on a.ein = b.ein
left join dba.ctopenmo c on b.ctbox=c.box and a.ctt = c.tax_type and a.per=right(cast((c.tax_year * 100) + c.file_month as varchar(6)),4)
left join dba.ctclosed d on b.ctbox=d.box and a.ctt = d.tax_type and a.per=right(cast((d.tax_year * 100) + d.file_month as varchar(6)),4)

where (c.trans_code is null
or (c.trans_code='01' and c.mc <> '02' and c.process_date>= '2008-06-13')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 13:33:04

Are you getting any error?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-09 : 13:38:47

You have process_date twice in your select list and are missing a close parenthesis in the where clause

select a.ein
      , process_date
      , GetDate() as EnteredDate
      , '01' as act_code
      , b.s
      , right(b.stcd,2)
      , c.trans_amt
      , case when b.ctbox is not null then 'C' else 'O' end as Ledger
      , process_date
      , a.PER
      , case when c.dln is not null then c.dln else d.dln end as DLN

from 
    dba.debtst a
left join 
   dba.debtshv b 
on a.ein = b.ein
left join 
  dba.ctopenmo c 
on 
    b.ctbox = c.box and a.ctt = c.tax_type 
and a.per = right(cast((c.tax_year * 100) + c.file_month as varchar(6)),4) 
left join 
   dba.ctclosed d 
on 
   b.ctbox = d.box and a.ctt = d.tax_type and a.per=right(cast((d.tax_year * 100) + d.file_month as varchar(6)),4) 

where (
          c.trans_code is null
          or (c.trans_code='01' and c.mc <> '02' and c.process_date>= '2008-06-13')
      )

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-09 : 14:42:09

Did you try it? The where clause will take all the records where at least one of the following is true
1)c.trans_code is null and d.trans_code is null
2)c.trans_code='01' and c.mc <> '02' and c.process_date>= '2008-06-13'
3)d.trans_code='01' and d.mc<>'02' and d.process_date>='2008-06-13'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 06:54:25

We cant help you much unless we know what you're looking at. it would be much better if you can explain what you want with some data from tables and sample output.

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2008-07-10 : 12:16:59

I got the query work by doing two separate queries and union. Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources