I have subquery into a principal query, and i need the subquery return me only the last row of all results, but i can`t use "order by <field> desc " in the subquery because this return me an error, nither "fetch first 1 rows only" because this return a error too. Any sugestion?
this es the SQL... - SELECT
-
C.NOMBRE,V.COBJCOD,CS.DESC,V.CSUBCOD,
-
N.NOMBRE,CS.NIVEL,V.SUBLIBRO,
-
V.TSUBLIBRO,CS.CAT5COD,CAT7COD,
-
A.CODIGO,V.ANIO,M.codigo,V.MES,
-
sum(V.saldod),acumulado
-
FROM
-
QS36F.FIN0002M V
-
Inner Join
-
QS36F.V570010 S
-
on
-
(S.codigo=V.SUCCOD)
-
Inner Join
-
QS36F.V57CIA E
-
on
-
(S.CODEMP=E.codigo)
-
Inner Join
-
QS36F.V570909 C
-
on
-
(C.cuenta=V.COBJCOD)
-
Inner Join
-
QS36F.V570901 CS
-
on
-
(CS.UNEGCOD=V.UNEGCOD
-
and
-
CS.CSUBCOD=V.CSUBCOD
-
and
-
CS.COBJCOD=V.COBJCOD)
-
Inner Join
-
QS36F.NIVEL N
-
on
-
(CS.nivel=N.codigo)
-
Inner Join
-
QS36F.ANIO A
-
on
-
(A.codigo=V.anio)
-
Inner Join
-
QS36F.MES M
-
on
-
(M.codigo=V.mes)
-
-
WHERE
-
-
acumulado IN
-
(
-
///SUBQUERY
-
select
-
acumulado
-
from
-
QS36F.FIN0002M
-
// order by acumulado desc ( <-- this return a error)
-
)
-
AND
-
(E.codigo='E01')
-
AND
-
(A.codigo='2007' OR A.codigo='2006')
-
-
GROUP BY
-
C.nombre,V.COBJCOD,CS.DESC,
-
V.CSUBCOD,N.nombre,CS.nivel,
-
V.sublibro,V.TSUBLIBRO,CS.cat5cod,
-
CS.cat7cod,A.codigo,V.anio,
-
M.codigo,V.mes
-
ORDER BY
-
C.nombre,V.COBJCOD,CS.DESC,
-
V.CSUBCOD,N.nombre,CS.nivel, V.sublibro,
-
V.TSUBLIBRO,CS.cat5cod,CS.cat7cod,
-
A.codigo,V.anio,M.codigo,V.mes,acumulado
-
-
FETCH FIRST 80 ROWS ONLY
1 2067
Hi,
first, I took the liberty to add some code tags to your post for better readability.
Second, if you sort descending by 'acumulado' and just need the first row, then why don't you use - SELECT
-
max(acumulado)
-
FROM
-
QS36F.FIN0002M
If that doesn't fit your needs, then you could create a view based on your subquery and uses that view in the conditions of your big query.
Saludos,
Bernd
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Andrew Mayo |
last post by:
Here's a really weird one for any SQL Server gurus out there...
We have observed (SQL Server 2000) scenarios where a stored procedure
which
(a) begins a transaction
(b) inserts some rows into a table
(c) re-queries another table using a subquery which references the
inserted table (correlated or not)
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two...
|
by: K. Crothers |
last post by:
I administer a mechanical engineering database. I need to build a
query which uses the results from a subquery as its input or
criterion.
I am attempting to find all of the component parts of which a part may
be composed. I have a table of parts and their subparts. The problem
is that each of those subparts may be composed of smaller...
|
by: Rod |
last post by:
I have a client site where the code below has been working happily for at
least four months. The site is using SQL Server 7. The code is ASP.NET
Last week an error appeared related to the following SQL statement.
INSERT INTO OrderItems (ClientID, ProductID, OrderHeaderID, Quantity,
Dispatched, BackOrdered) SELECT ClientID, ProductID,...
|
by: ThePrinceIsRight |
last post by:
I have a problem with using a subquery in MS Access. The purpose of the sub-query is to create a list of people who have had doctor exams in the past 6 months and exclude them from the main query. This is what I came up with:
SELECT GI.generalinfoid AS GeneralInfoId, max(GI.FirstName) AS FirstName, max(GI.LastName) AS LastName,...
| |
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |