Solved

using MAX to eliminate second empty (almost) record.

Posted on 2013-12-05
6
233 Views
Last Modified: 2013-12-27
I have a question. I am running a query in which I join two tables based on a relationship defined on a third table.

I am getting two rows when I add the Item column. I have used the MAX function around the Item Number column and that eliminates the second row which is empty on all relevant columns with the exemption of Item Number, but I am not sure if using this function is recommended.

I am including my query, and the results I get when I run it specifying some where and and conditions.

SELECT
	gle.[Posting Date] AS PostingDate
,	gle.[G_L Account No_] AS GL_AccountNumber
,	ve.[Item No_] AS ItemNumber
,	gle.[Document No_] AS DocumentNumber
,	SUM(gle.Amount) GL_Balance
,	SUM(ve.[Item Ledger Entry Quantity]) AS Quantity
,	SUM(ve.[Cost Amount (Actual)]) + SUM(ve.[Cost Amount (Expected)]) AS Cost
,	SUM(ve.[Cost Amount (Actual)] + ve.[Cost Amount (Expected)]) / NULLIF(SUM(ve.[Item Ledger Entry Quantity]), 0) AS UnitCost
FROM 
	NAV.[dbo].[G_L - Item Ledger Relation] rel FULL OUTER JOIN 
	NAV.dbo.[G_L Entry] gle ON 
	rel.[G_L Entry No_] = gle.[Entry No_] FULL OUTER JOIN
	NAV.dbo.[Value Entry] ve ON 
	rel.[Value Entry No_] = ve.[Entry No_] 
GROUP BY 
	gle.[Posting Date]
,	gle.[G_L Account No_]
,	ve.[Item No_] 	
,	gle.[Document No_]

Open in new window


SELECT * from GL_InventoryValuation] WHERE GL_AccountNumber = '13110' AND PostingDate BETWEEN '1753-01-01' AND '2013-11-23' AND DocumentNumber = 'SPI03164'

I get:


2010-05-28 00:00:00.000	13110	NULL	SPI03164	-24995.95000000000000000000	NULL	NULL	NULL
2010-05-28 00:00:00.000	13110	10828	SPI03164	0.00000000000000000000	0.00000000000000000000	0.00000000000000000000	NULL

Open in new window


Really the second row only shows the Item Number, Is there an efficient method to merge the results?
0
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39698606
So you think you are getting the results you want but you think there may be a more efficient way of going about it?  Or, are you worried that your approach is giving you bad results?
0
 

Author Comment

by:metropia
ID: 39698749
hi sl8rz:

to answer, I think I am getting the results you want but you think there may be a more efficient way of going about it.

Thank you.
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 250 total points
ID: 39698868
I will often force the one row that I'm after to the top (by using an "ORDER BY" clause, sometimes using DESC for descending order) then I can grab it by using "TOP 1" like this:

SELECT TOP 1 * FROM myTable ORDER BY dollarAmount DESC

Open in new window

This code would return only the row with the maximun dollarAmount (MAX could be used but there are times when this is better - ie. maybe you want the top 10).  Remember that ORDER BY works on text too.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:metropia
ID: 39698876
can you put an ORDER BY within a view?
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39698894
No, a View is treated like a table when querying occurs against it (even though it doesn't actually exist as a table).  So you can either handle the ORDER BY in code (C#, VB, PHP, whatever) or you can make a simple stored procedure in the database that performs the query using the ORDER BY clause.  Then you just execute that stored proc.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39700198
PostingDate        GL_AccountNumber col3    DocumentNumber
2010-05-28 00:00:00.000    13110    NULL    SPI03164    -24995.95    NULL    NULL    NULL
2010-05-28 00:00:00.000    13110    10828   SPI03164    0.00         0.00    0.00    NULL
                                    ^^^^

Open in new window

what is "col3"?
If you were to run that query for all account numbers would you always get 2 records per account?
is "col3" always NULL for one of the records?
SELECT
      *
FROM GL_InventoryValuation
WHERE GL_AccountNumber = '13110'
      AND PostingDate BETWEEN '1753-01-01' AND '2013-11-23'
      AND DocumentNumber = 'SPI03164'

-- try these (one at a time)
-- and "col3" is null
-- and "col3" is NOT null

Open in new window

0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question