Hi group.
I have this query:
SELECT FTDLN.NID FROM FTDLN INNER JOIN FTDOC ON
FTDLN.NTIPO = FTDOC.NTIPO AND
FTDLN.NNUMDOC = FTDOC.NNUMDOC AND
FTDLN.CSERIE = FTDOC.CSERIE
This query in a particular database dont'n work, i.e., 0 rows return.
But this query:
SELECT FTDLN.NID FROM FTDLN INNER JOIN FTDOC ON
FTDLN.NTIPO = FTDOC.NTIPO AND
FTDLN.NNUMDOC = FTDOC.NNUMDOC AND
FTDLN.CSERIE = FTDOC.CSERIE
ORDER BY FTDLN.NID
With the 'ORDER BY' clause alredy works.
Does anybody has any ideia why this happened?
Thanks a lot...
Nuno Teixeira
PortugalNuno Teixeira wrote:
> Hi group.
> I have this query:
> SELECT FTDLN.NID FROM FTDLN INNER JOIN FTDOC ON
> FTDLN.NTIPO = FTDOC.NTIPO AND
> FTDLN.NNUMDOC = FTDOC.NNUMDOC AND
> FTDLN.CSERIE = FTDOC.CSERIE
> This query in a particular database dont'n work, i.e., 0 rows return.
> But this query:
> SELECT FTDLN.NID FROM FTDLN INNER JOIN FTDOC ON
> FTDLN.NTIPO = FTDOC.NTIPO AND
> FTDLN.NNUMDOC = FTDOC.NNUMDOC AND
> FTDLN.CSERIE = FTDOC.CSERIE
> ORDER BY FTDLN.NID
> With the 'ORDER BY' clause alredy works.
> Does anybody has any ideia why this happened?
> Thanks a lot...
> Nuno Teixeira
> Portugal
Are any of those columns NULLable? If so,make sure to include a WHERE
ColName IS NOT NULL to the query and then try them again.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Showing posts with label queryselect. Show all posts
Showing posts with label queryselect. Show all posts
Friday, March 30, 2012
Monday, February 20, 2012
PIVOT operator for variable number of transformations
Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.
Subscribe to:
Posts (Atom)