SQLTeam.com | Weblogs | Forums

What's wrong with this SQL?


#1

Select distinct
a.[register] as [Terminal]
,format(a.[open_date],'yyyy-MM-dd') as [Date]
,a.[open_birno] as [Beg Invoice]
,a.[clos_birno] as [Ending Invoice]
,isnull(d.rd_tamount,0)
,isnull(c.rd_tamount,0)
,isnull(e.rd_tamount,0)
,isnull(f.rd_tamount,0)
,isnull(g.rd_tamount,0)
,isnull(h.rd_tamount,0)
,isnull(i.rd_tamount,0)
,isnull(j.rd_tamount,0)
,isnull(k.rd_tamount,0)
,isnull(l.rd_tamount,0)
,isnull(m.rd_tamount,0)
,isnull(n.rd_tamount,0)
,isnull(o.rd_tamount,0)
,isnull(p.rd_tamount,0)
,isnull(q.rd_tamount,0)
,isnull(b.rd_tamount,0)

FROM [dbo].[REGHIST] a
left join dbo.regdtail b on a.open_date = dbo.grouptodate(b.rd_group)
and (b.rd_type='M' and b.rd_code='VE')
left join dbo.regdtail c on a.open_date = dbo.grouptodate(c.rd_group)
and (c.rd_type='T' and c.rd_code ='15')
left join dbo.regdtail d on a.open_date = dbo.grouptodate(d.rd_group)
and (d.rd_type='T' and d.rd_code ='0')
left join dbo.regdtail e on a.open_date = dbo.grouptodate(e.rd_group)
and (e.rd_type='T' and e.rd_code ='23')
left join dbo.regdtail f on a.open_date = dbo.grouptodate(f.rd_group)
and (f.rd_type='T' and f.rd_code ='28')
left join dbo.regdtail g on a.open_date = dbo.grouptodate(g.rd_group)
and (g.rd_type='T' and g.rd_code ='43')
left join dbo.regdtail h on a.open_date = dbo.grouptodate(h.rd_group)
and (h.rd_type='T' and h.rd_code ='35')
left join dbo.regdtail i on a.open_date = dbo.grouptodate(i.rd_group)
and (i.rd_type='T' and i.rd_code ='17')
left join dbo.regdtail j on a.open_date = dbo.grouptodate(j.rd_group)
and (j.rd_type='T' and j.rd_code ='42')
left join dbo.regdtail k on a.open_date = dbo.grouptodate(k.rd_group)
and (k.rd_type='D' and k.rd_code ='A1')
left join dbo.regdtail l on a.open_date = dbo.grouptodate(l.rd_group)
and (l.rd_type='D' and l.rd_code ='05')
left join dbo.regdtail m on a.open_date = dbo.grouptodate(m.rd_group)
and (m.rd_type='D' and m.rd_code ='A2')
left join dbo.regdtail n on a.open_date = dbo.grouptodate(n.rd_group)
and (n.rd_type='D' and n.rd_code ='02')
left join dbo.regdtail o on a.open_date = dbo.grouptodate(o.rd_group)
and (o.rd_type='D' and o.rd_code ='10')
left join dbo.regdtail p on a.open_date = dbo.grouptodate(p.rd_group)
and (p.rd_type='D' and p.rd_code ='11')
left join dbo.regdtail q on a.open_date = dbo.grouptodate(q.rd_group)
and (q.rd_type='T' and q.rd_code ='26')

where a.register = 'A5'

and a.open_date between '2015-06-04' and '2015-06-04'
group by a.[register], a.[open_date], a.[open_birno], a.[clos_birno]
, b.rd_tamount, c.rd_tamount, d.rd_tamount
, e.rd_tamount, f.rd_tamount, g.rd_tamount
, h.rd_tamount, i.rd_tamount, j.rd_tamount
, k.rd_tamount, l.rd_tamount, m.rd_tamount
, n.rd_tamount, o.rd_tamount, p.rd_tamount
, q.rd_tamount


#3

What error do you get?


#4

Hello, i got multiple rows by dates. For every dates ( Dec 28,29,30), there were many rows in it. But the only right data is the last row record of the specific date.

How can i trim the rows / how can i just only get the last row record of each date?

Thanks


#5

Please provide description of tables (as create statements), sample data (as insert statements) end your expected output (from the sample data).


#6

Hi guys,

After rethinking my query, I changed it to make the process faster.

Pic A is executed successfully. But Pic B, how will it be fix?


#7

Please provide description of tables (as create statements), sample data (as insert statements) end your expected output (from the sample data).


#8

The error clearly states what the problem is. Remove the aggregate from the a.Open_Date column in the SELECT list.

I also strongly suggest that you NOT use FORMAT in any code. It's quite literally 44 times slower than CONVERT.

Also, if you want better help, please stop posting pictures of code. Always post real code and learn to use thee "preformatted text" option in the iconic menu bar when prepping a message. That way, we can copy and paste your code to give you coded answers back.


#9

Thank you for the time guys. I apologize for the screenshots. I'm new in databases but willing to learn.

success query =
isnull(max(Case when b.rd_type='D' and b.rd_code ='02' and b.rd_ttrans=1 then b.rd_tamount end),0) as [Line Disc%]

aggregate function on an expression containing an aggregate or a subquery query=
isnull(max(Case when b.rd_type='D' and b.rd_code ='02' and b.rd_ttrans=
max(Case when b.rd_type='D' and b.rd_code='02' then b.rd_ttrans end)
then b.rd_tamount end),0) as [Line Disc%]

The bold text is the changes between the two queries. I used the Case to get the max of b.rd_ttrans, but the error is aggregate function on an expression containing an aggregate or a subquery query.

How can I get the max of b.rd_ttrans?


#10

CREATE TABLE [dbo].[REGDTAIL2](
[rd_type] char NOT NULL DEFAULT (''),
[rd_code] char NOT NULL DEFAULT (''),
[rd_ttrans] [smallmoney] NOT NULL DEFAULT ((0)),
[rd_tamount] [money] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

INSERT INTO [dbo].[REGDTAIL2]
([rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('T','15',2.00,860.98),
('T','15',1.00,500.00),
('T','15',1.00,360.98)

Select distinct

max(Case when a.rd_type='T' and a.rd_code='15' and a.rd_ttrans=

max(Case when a.rd_type='T' and a.rd_code='15' then a.rd_ttrans end)

then a.rd_tamount end) as [Zero Amount]

FROM [dbo].[REGDTAIL2] a

Error Msg =
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I think its because of the bold text in the select query...if i just replace the bold text like 1 or 2, it's fine. But I wanted to get the max value of the a.rd_ttrans...


#11

maybe this is what you want ? If it is not, please post your required result

; with 
cte as
(
	select	*, rn  = row_number() over (order by rd_tamount desc)
	from	[dbo].[REGDTAIL2]
)
select	*
from	cte
where	rn	= 1

#12

Hi Khtan,

I tried ur advice. But. I have other table joining them and different types of value in the rd_type and rd_code. What I need is the result of (T,15,2,860.98). How can I have a condition within a condition?
e.g. (Case when a.rd_type='T' and a.rd_code='15' and a.rd_ttrans=
max(Case when a.rd_type='T' and a.rd_code='15' then a.rd_ttrans end)
then a.rd_tamount end)

the rd_type and rd_code has different types, while the rd_ttrans, i just want to get the highest no of it. if it inserts more records, it looks like this...
INSERT INTO [dbo].[REGDTAIL2]
([rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('T','10',2.00,60.00),
('T','10',1.00,50.00),
('D','1',1.00,36.00),
('D','1',2.00,100.00),
('D','1',3.00,200.00)

the result I need is
T, 15, 2, 860.98
T, 10, 2, 60.00
D, 1, 3, 200

the records of rd_ttrans with less than the highest value are useless record.


#13

change to

OVER ( PARTITION BY rd_type, rd_code ORDER BY rd_tamount desc)


#14

Thanks Khan, it worked!

How can I use your advice if i have this query?

Select distinct
max(a.[register]) as [Terminal]
,isnull(max(Case when b.rd_type='D' and b.rd_code ='A2' then b.rd_tamount end),0) as [Cash]
,isnull(max(Case when b.rd_type='D' and b.rd_code ='02' and b.rd_ttrans=
max(Case when b.rd_type='D' and b.rd_code ='02' then b.rd_ttrans end) then b.rd_tamount end),0) as [Line Disc%]

FROM [dbo].[REGHIST] a
left join dbo.regdtail b on a.open_date = dbo.grouptodate(b.rd_group)
where a.open_date between '2015-10-10' and '2015-10-11'
group by a.[open_date]


#15

what are you trying to achieve with this query ?


#16

the second select statement is to get the value in the records where rd_type='D' and rd_code='02' and rd_ttrans= highest value of it. I joined the table with other data I needed where date is the common value of it.


#17

please post some sample data from both the table and the expected result.

also what does grouptodate() do ?


#18

Hi Khtan,

okay, here the data

CREATE TABLE [dbo].[REGDTAIL2](
[rd_type] char NOT NULL DEFAULT (''),
[rd_code] char NOT NULL DEFAULT (''),
[rd_ttrans] [smallmoney] NOT NULL DEFAULT ((0)),
[rd_tamount] [money] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

INSERT INTO [dbo].[REGDTAIL2]
([rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('D','1',1.00,36.00),
('D','1',2.00,100.00),
('D','1',3.00,200.00),
('D','1',4.00,150.00)

In these records, rd_type=D and rd_code=1 has four records, but the rd_ttrans=4 has the amount of 150.00. so the result i want is rd_tamount = 150.00.

my rd_ttrans has the condition of getting the max of rd_ttrans so that i can get the rd_tamount.

here is my query
Select distinct

max(Case when a.rd_type='D' and a.rd_code='1' and a.rd_ttrans=

(max(Case when rd_type='D' and rd_code='1' then rd_ttrans end))

then a.rd_tamount end) as [Zero Amount]

FROM [dbo].[REGDTAIL2] a

this query has error of "cannot perform an aggregate function on a expression or subquery"

i'm using mySql 2014


#19

it is actually same with the previous query that i posted. Just change to ORDER BY rd_ttrans

; with 
cte as
(
	select	*, rn  = row_number() OVER ( PARTITION BY rd_type, rd_code 
                                                 ORDER BY rd_ttrans desc)
	from	[dbo].[REGDTAIL2]
)
select	*
from	cte
where	rn	= 1

Well, this site is for MS SQL Server. My query should works on MS SQL, but i am not sure about My SQL.


#20

Yes, Khtan
but i just dissect part of the query. if you can look again the previous msg with the join table. u also asked the function of grouptodate. the grouptodate is a function to decode the date in the column of rd_group

if you look the query with the join table, how can i implement your advice to it? is not possible to have your query in my query right?

ops. typographical error in mysql. it should be msSql


#21

If i have another table joining them...

CREATE TABLE [dbo].[REGDTAIL2](

[rd_date]varchar NOT NULL DEFAULT (''),

[terminal] varchar NOT NULL DEFAULT (''),

[rd_type] char NOT NULL DEFAULT (''),
[rd_code] char NOT NULL DEFAULT (''),
[rd_ttrans] [smallmoney] NOT NULL DEFAULT ((0)),
[rd_tamount] [money] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

INSERT INTO [dbo].[REGDTAIL2]
(

[rd_date]

,[terminal]

,[rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('2015-12-12','A1','D','1',1.00,36.00),
('2015-12-12','A1','D','1',2.00,100.00),
('2015-12-12','A1','D','1',3.00,200.00),
('2015-12-12','A1','D','1',4.00,150.00),

('2015-12-13','A1','D','1',1.00,40.00),
('2015-12-13','A1','D','1',2.00,80.00),
('2015-12-13','A1','D','1',3.00,50.00)

CREATE TABLE [dbo].[REGHIST](

[date]varchar NOT NULL DEFAULT (''),

[terminal] varchar NOT NULL DEFAULT ('')

) ON [PRIMARY]

INSERT INTO [dbo].[REGHIST]
([date]
,[terminal])
VALUES
('2015-12-12','A1'),

('2015-12-13','A1'),

('2015-12-13','A2')

How can I use your advice if i have this query? The bold text is my old wrong condition. How will I use your advice if I have join table in this query? The table [REGHIST] has other columns I will used in the query but I didn't include it anymore to make it simple.

Select distinct
max(a.[terminal]) as [Terminal]

max(a.[date]) as [Date]

,isnull(max(Case when b.rd_type='D' and b.rd_code ='01' and b.rd_ttrans=
max(Case when b.rd_type='D' and b.rd_code ='01' then b.rd_ttrans end) then b.rd_tamount end),0) as [Line Disc%]

FROM [dbo].[REGHIST] a
left join dbo.regdtail b on a.date = b.rd_date
where a.date between '2015-12-12' and '2015-12-13'

and a.terminal = b.terminal

group by a.[date]

The result i want is

A1,2015-12-12,150.00

A1,2015-12-13,50.00