Union All - Not displaying all data from query

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 2005 Forums

Transact-SQL (2005)

Union All - Not displaying all data from query

Author

Topic

james_w
Starting Member

21 Posts

Posted - 2010-02-22 : 12:18:50

Hi,

I have a union all query and am displaying the data from this query on a web page.

The problem i'm having is a very strange one for me as i've never had this happen before, the problem i'm experiencing is that I have a stored procedure and when I run the stored procedure in SQL it returns 9 rows of data (2 from the first query and 7 from the second), when I attempt to display these records on a web page it only displays the 7 records from the second query, i've never had this problem before with other queries although haven't used union all before.

The stored procedure is below

DECLARE @datefrom datetime
DECLARE @dateto datetime

SET @datefrom = DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE())-6
SET @dateto = DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE())


SELECT DISTINCT
	a.[CaseID],
	a.[ClientID],
	a.[propertyAddress],
	a.[propertyAddress2],
	a.[propertyAddress3],
	a.[propertyTown],
	a.[propertyCounty],
	a.[propertyPostcode],
	c.[PxOfferDate],
	c.[PxOffer],
	c.[PxOfferPercent],
	c.[PxOfferStatus],
	c.[ValuationUsed],
	c.[ValuationAmount],
	d.[ClientName],
	d.[ClientCode],
	e.[Region],
	ISNULL(g.[ActualCompletionDate],g.[TargetCompletionDate]) As CompletionDate,
	(SELECT 'Formal') As OfferType

FROM [CaseReg] As a
LEFT JOIN [PxOffer] As c
ON a.[CaseID] = c.[CaseID]
INNER JOIN [Clients] As d
ON a.[ClientID] = d.[ClientID]
LEFT JOIN [MsRegion] As e
ON d.[ClientID] = e.[ClientID]
INNER JOIN [MsSite] As f
ON a.[siteID] = f.[SiteID]
LEFT JOIN [Buyin] AS g
ON a.[CaseID] = g.[CaseID]
LEFT JOIN [Drawdowns] AS h
ON a.[CaseID] = h.[CaseID]

WHERE (c.[PxOfferStatus] = 'Rejected'
AND c.[PxOfferDate] >= @datefrom
AND c.[PxOfferDate] <= @dateto)
OR
(GETDATE() > c.[PxOfferExpiry]
AND c.[PxOfferDate] >= @datefrom
AND c.[PxOfferDate] <= @dateto)

UNION

SELECT DISTINCT
	a.[CaseID],
	a.[ClientID],
	a.[propertyAddress],
	a.[propertyAddress2],
	a.[propertyAddress3],
	a.[propertyTown],
	a.[propertyCounty],
	a.[propertyPostcode],
	a.[PxOfferDate],
	a.[PxOffer],
	a.[PxOfferPercent],
	a.[PxOfferStatus],
	a.[ValuationUsed],
	a.[ValuationAmount],
	b.[ClientName],
	b.[ClientCode],
	c.[Region],
	a.[ActualCompletionDate] As CompletionDate,
	a.[OfferType]

FROM [PxOfferVerbal] As a
INNER JOIN [Clients] As b
ON a.[ClientID] = b.[ClientID]
LEFT JOIN [MsRegion] As c
ON b.[ClientID] = c.[ClientID]

WHERE (a.[PxOfferStatus] = 'Rejected'
AND a.[PxOfferDate] >= @datefrom
AND a.[PxOfferDate] <= @dateto)
OR (a.[PxOfferStatus] = 'Expired'
AND a.[PxOfferDate] >= @datefrom
AND a.[PxOfferDate] <= @dateto)

I can get it returning data from both of the union queries if I just have WHERE (c.[PxOfferDate] >= @datefrom AND c.[PxOfferDate] <= @dateto) in the first query but obviously this isn't the data i want to show.

Does anyone know if this is a problem with my query or shed any light on this at all?

Thanks in advance for any help.

James.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 12:27:09

In your code I see a "UNION" and not a "UNION ALL".
UNION without "ALL" is going to suppress duplicate rows from both resultsets.

Maybe UNION ALL will solve your problem?

Otherwise example data and wanted output would be helpful...

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 12:48:08

you're using lots of condition in where clause using columns of [PxOffer] and you're taking LEFT JOIN with it. This will cause filtering out all records which doesnt have a match in [PxOffer] ( as good as INNER JOIN) . Is this intentionally done?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

james_w
Starting Member

21 Posts

Posted - 2010-02-22 : 12:56:02

Thanks for the reply, but it's not adding the 'all' thats the problem, think i'd just posted a version of the query after I had been messing with it for a bit.

The data below is what I am seeing in SQL when I run the query (pasted as CSV, i've removed some data columns):

CaseID,Offer,Percentage,Valuation,OfferType
2,117500,NULL,135000,Verbal
3,520000,NULL,575000,Verbal
4,301500,90,335000,Verbal
5,60000,NULL,0,Verbal
6,260000,NULL,287500,Verbal
7,157500,NULL,180000,Verbal
8,85000,NULL,102500,Verbal
12040,102000,85,120000,Formal
12039,160000,84.21,190000,Formal

the first 7 rows (OfferType Verbal) are from the second query in my union all and the last 2 (OfferType Formal) are from the first query and i would like to see all this data on my web page.

But when i display the recordset on my webpage it only displays the data from the second query (OfferType Verbal), here is the code on my web page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../Connections/connPGS.asp" -->
<%
		Dim cmdReportPrevious, rsReportPrevious
		Set cmdReportPrevious = Server.CreateObject("ADODB.Command")
		cmdReportPrevious.ActiveConnection = connPGS
		cmdReportPrevious.CommandText = "dbo.spRepOffersCashPrevious"
		cmdReportPrevious.CommandType = 4
		cmdReportPrevious.Prepared = false
		Set rsReportPrevious = cmdReportPrevious.Execute
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
	<tr>
		<td>CaseID</td>
		<td>Address</td>
		<td> </td>
		<td> </td>
	</tr>
	<%
		While Not rsReportPrevious.EOF
	%>
	<tr>
		<td><%=rsReportPrevious("CaseID")%></td>
		<td><%=rsReportPrevious("OfferType")%></td>
		<td> </td>
		<td> </td>
	</tr>
	<%
		rsReportPrevious.MoveNext
		Wend
	%>
</table>

</body>
</html>

You can also view the web page here https://www.arcpropertygroup.co.uk/pgs/reports/previous.asp (again i have removed sensitive data)

Thanks,
James.

james_w
Starting Member

21 Posts

Posted - 2010-02-22 : 13:03:14

Hi visakh16,

I have taken the [PxOffer] left join out of the query as I actually didn't need that one (was used for a query that i copied from and it still doesn't change although I am using the other left joins intentionally as not all records that will be displayed have matching records in the joined tables.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 13:40:04

Just a shot but who knows what is going on between the page and sql server...

you have this:


...
	ISNULL(g.[ActualCompletionDate],g.[TargetCompletionDate]) As CompletionDate,
	(SELECT 'Formal') As OfferType

FROM [CaseReg] As a
...

change it to this:


...
	ISNULL(g.[ActualCompletionDate],g.[TargetCompletionDate]) As CompletionDate,
	'Formal' As OfferType

FROM [CaseReg] As a
...

No, you're never too old to Yak'n'Roll if you're too young to die.

james_w
Starting Member

21 Posts

Posted - 2010-02-23 : 04:38:56

Still no change, I’m absolutely stumped, not sure what’s causing this.

Although if I change the first WHERE clause I can get data out from both of the UNION queries so maybe it’s the WHERE clause that causing the problem??

If I change the first WHERE clause to something simple like:

WHERE (c.[PxOfferStatus] = 'Rejected')

Then data is returned fine, but now obviously it's not the correct data.

I think for now i may just have to run it as two seperate sprocs, this way I can still display them in the same table but cannot order them correctly.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 05:10:02

A last try:
Make your union select a derived table and then select from that:

select * from
(...first select union all second select...)dt

Otherwise we should see the whole code of the stored procedure...

No, you're never too old to Yak'n'Roll if you're too young to die.

james_w
Starting Member

21 Posts

Posted - 2010-02-23 : 05:18:15

Woo hoo, I couldn't let it go and eventially think i've found the problem.

I thought i'd try passing the date (first and last dates of current week) variables from the web page instead of directly in SQL and it seemed to work fine. For some reason the variables I did in SQL only applied to the second union query, don't know if anyone knows what i did wrong for future reference?

Thanks for your help though.

James.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-23 : 05:26:34

Maybe the datatypes in PXOFFER and PXOFFERVERBAL are different?
Or it is about the time part?

No, you're never too old to Yak'n'Roll if you're too young to die.

james_w
Starting Member

21 Posts

Posted - 2010-02-23 : 05:33:34

Datatypes are the same and the datetimes stored in these fields are just the actual date all times are 00:00:00.000 although i suppose this could have confused matters.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:28:29

quote:
Originally posted by james_w

Datatypes are the same and the datetimes stored in these fields are just the actual date all times are 00:00:00.000 although i suppose this could have confused matters.

did your tables have date with time part as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

james_w
Starting Member

21 Posts

Posted - 2010-02-23 : 10:55:25

All the time parts of datetime datatype stored the date and not the time, so all times within these datetime columns were 00:00:00, such as: 19/02/2010 00:00:00.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources