• Register
0 votes
90 views

Problem :

I am new to the SQL. What am I doing wrong here?

SELECT LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000),

PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%',batchinfo.datapath), 8000))-1),qvalues.name,qvalues.compound,qvalues.rid

FROM batchinfo JOIN qvalues ON batchinfo.rowid=qvalues.rowid

WHERE LEN(datapath)>4

GROUP BY 1,2,3

HAVING rid!=MAX(rid)

I am getting error when I try to execute above query.

I am trying to do group by the first, second, and third columns having the max rid.

It is works perfectly fine without the group by and having.

6 5 3
7,540 points

2 Answers

0 votes

Solution :

I have lots of experience in writing complex SQL Server queries.

I have gone through your post and understood your problem.

So to begin with you must not do the following mistake:

having rid!=MAX(rid)

The HAVING clause which you want to use can only contain things which are attributes of the aggregate groups.

In addition to that, 1, 2, 3 are not valid in GROUP BY in SQL Server .I think those are only valid in ORDER BY clause.

Please find the following updated code which will work and solve your issues.

select

LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),

qvalues.name,

qvalues.compound,

MAX(qvalues.rid)

 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid

where LEN(datapath)>4

group by LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),

qvalues.name,

qvalues.compound

9 7 4
38,600 points
0 votes

Solution:

To begin with you can't do this:

having rid!=MAX(rid)

The Having clause can just hold things which are attributes of the aggregate groups.

Moreover, 1, 2, 3 is not valid in GROUP BY in SQL Server - I think that's just valid in ORDER BY.

Can you interpret why this isn't what you are search for:

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid)
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound

Well, as it was expressed prior, you can't GROUP by literals, I think that you are confused because you can ORDER by 1, 2, 3. at the time you employ functions as your columns, you require to GROUP by the similar expression. Yet, the HAVING clause is wrong, you can just exercise what is in the agreggations. In this instance, your query must be like this:

SELECT 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound,
MAX(qvalues.rid) MaxRid
FROM batchinfo join qvalues 
ON batchinfo.rowid=qvalues.rowid
WHERE LEN(datapath)>4
GROUP BY 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1),
qvalues.name,
qvalues.compound

You can't group by literals, just columns.

You are perhaps searching for something like th

select 
LEFT(SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000), PATINDEX('%[^0-9]%', SUBSTRING(batchinfo.datapath, PATINDEX('%[0-9][0-9][0-9]%', batchinfo.datapath), 8000))-1) as pathinfo,
qvalues.name,
qvalues.compound,
qvalues.rid
 from batchinfo join qvalues on batchinfo.rowid=qvalues.rowid
where LEN(datapath)>4
group by pathinfo, qvalues.name, qvalues.compound
having rid!=MAX(rid)

Firstly, you have to give that first expression a column name with as. Then you have to fix the names of the columns in the group by expression.

10 6 4
31,120 points

Related questions

0 votes
1 answer 5 views
0 votes
1 answer 155 views
155 views
Problem : My stored procedure giving me following error cannot perform an aggregate function on an expression containing an aggregate or a subquery.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
2 answers 240 views
240 views
Problem : I want to import the flat file into my oledb target sql server database. But I am getting below error message : [Source - 18942979103_txt [424]] Error: Data conversion failed. The data conversion for column "recipient-name" returned status value 4 and status text ... more characters had no match in the target code page.". Why am I getting this error message? What am I doing wrong here?
asked Dec 23, 2019 alecxe 7.5k points