LiveReport mangling my SQL output (NULLS included)

BAE_Systems_Administrator_(baesysauadmin_-_(delete

Hi all,I'm writing a fairly straight forward LiveReport to extract data out of a custom database table. However, as soon as I add some conditions (namely certain columns = NULL) then all **** breaks loose.The original query (which works perfectly) is:select MF.DocNumber, MF.RevNumber, MF.Location, MF.Feedback, MF.Response, FirstName as 'First Name', LastName as 'Last Name' from LLUser.LL_Custom_Marine_Feedback_Log as MF, LLUser.KUAF where(MF.Name = LLUser.KUAF.ID) and (MF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber = MF.DocNumber)))When I add a few conditionals for NULLs in the 'where' conditionals.... e.g.select MF.DocNumber, MF.RevNumber, MF.Location, MF.Feedback, MF.Response, FirstName as 'First Name', LastName as 'Last Name' from LLUser.LL_Custom_Marine_Feedback_Log as MF, LLUser.KUAF where(MF.Name = LLUser.KUAF.ID) OR (MF.Name = NULL) OR (MF.DocNumber = NULL)and (MF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber = MF.DocNumber))).... then the results are pretty unpleasant. They can be seen in the attachment.The second query outputs results perfectly in SQL Query Analyzer.Any suggestions as to how to get around this?Cheers,Ben C

Find more posts tagged with

Comments

volvostephen

Message from via eLinkTry using 'IS NULL' not '= NULL'Like this....(MF.Name = LLUser.KUAF.ID) OR (MF.Name IS NULL) OR (MF.DocNumber IS NULL)I think that is proper and '=' when it comes to evaluating nulls shouldn'tbe used.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: November 21, 2005 7:33 PMTo: eLink RecipientSubject: LiveReport mangling my SQL output (NULLS included)LiveReport mangling my SQL output (NULLS included)Posted by Administrator, TenixLink on 11/21/2005 07:24 PMHi all,I'm writing a fairly straight forward LiveReport to extract data out of acustom database table. However, as soon as I add some conditions (namelycertain columns = NULL) then all **** breaks loose.The original query (which works perfectly) is:select MF.DocNumber, MF.RevNumber, MF.Location, MF.Feedback, MF.Response, FirstName as 'First Name', LastName as 'Last Name' fromLLUser.LL_Custom_Marine_Feedback_Log as MF, LLUser.KUAF where(MF.Name = LLUser.KUAF.ID) and (MF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber =MF.DocNumber)))When I add a few conditionals for NULLs in the 'where' conditionals.... e.g.select MF.DocNumber, MF.RevNumber, MF.Location, MF.Feedback, MF.Response, FirstName as 'First Name', LastName as 'Last Name' fromLLUser.LL_Custom_Marine_Feedback_Log as MF, LLUser.KUAF where(MF.Name = LLUser.KUAF.ID) OR (MF.Name = NULL) OR (MF.DocNumber = NULL)and (MF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber =MF.DocNumber))).... then the results are pretty unpleasant. They can be seen in theattachment.The second query outputs results perfectly in SQL Query Analyzer.Any suggestions as to how to get around this?Cheers,Ben C[To reply to this thread, use your normal E-mail reply function.]============================================================Attachment link: test.gifhttps://knowledge.opentext.com/knowledge/livelink.exe/open/4341846============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/livelink.exeTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.

BAE_Systems_Administrator_(baesysauadmin_-_(delete

Stephen,Thanks. I should have mentioned in the original post that the same behaviour occurs when either = NULL or IS NULL are used.

Drane_Matthew

What is the purpose of the evaluations for NULL?It seems that the reason for the incorrect results is that by adding:"OR (MF.Name = NULL) OR (MF.DocNumber = NULL)"the join of "LL_Custom_Marine_Feedback_Log" to "kuaf" tables is no longer working. Although you only posted an excerpt, I would suspect that you're probably returning many more records with this query than you want.It will help if you can post what you're trying to accomplish.

volvostephen

Message from via eLinkAlong these lines, I think you should re-order your query to place the ANDbit as the first clause in the WHERE and then the OR's last. I think thequery may be evaluating the first two clauses as OR's and then the last oris an ( OR MF.DocNumber=NULL AND that final clause). Know what I mean? Putthe last clause first and it should work or bracket together all the OR'sand it should work.So Trywhere (MF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber = MF.DocNumber))) AND(MF.Name = LLUser.KUAF.ID) OR (MF.Name = NULL) OR (MF.DocNumber = NULL) orwhere ((MF.Name = LLUser.KUAF.ID) OR (MF.Name = NULL) OR (MF.DocNumber = NULL) )and (MF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber = MF.DocNumber))) because I think this was evaluated aswhere (MF.Name = LLUser.KUAF.ID) OR (MF.Name = NULL) OR ( (MF.DocNumber = NULL) and (MF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber = MF.DocNumber))) )and if this is of no help - I give up....

-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: November 22, 2005 8:10 PMTo: eLink RecipientSubject: What is the purpose of the evaluations for NULL?What is the purpose of the evaluations for NULL?Posted by Drane, Matthew on 11/22/2005 08:08 PMWhat is the purpose of the evaluations for NULL?It seems that the reason for the incorrect results is that by adding:"OR (MF.Name = NULL) OR (MF.DocNumber = NULL)"the join of "LL_Custom_Marine_Feedback_Log" to "kuaf" tables is no longerworking. Although you only posted an excerpt, I would suspect that you'reprobably returning many more records with this query than you want.It will help if you can post what you're trying to accomplish. [To reply to this thread, use your normal E-mail reply function.]============================================================Topic: LiveReport mangling my SQL output (NULLS included)https://knowledge.opentext.com/knowledge/livelink.exe/open/4341845Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/livelink.exeTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.

BAE_Systems_Administrator_(baesysauadmin_-_(delete

Matthew and Stephen,Thanks for your assistance thus far. I've attached a Word doc with screenshots to try and explain what I'm trying to do and the (mangled) results that occur.BC

Drane_Matthew

Message from Drane, Matt <mdrane@hasbro.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

One more question....

Based on the use of "MF.VersionNum = ( select max(MF2.VersionNum) from
LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber = MF.DocNumber))"

...I assume that you want to only show the latest (max version) for each of the DocNumbers. Is that correct? What if two different people have a record for the same DocNumber...do you want one record or two records displayed?

This may not accomplish what you want, depending upon the answer to the question above, but try the following and respond what results you get from your dataset:

select MF.DocNumber, MF.RevNumber, MF.Location, MF.Feedback, MF.Response,
FirstName as 'First Name', LastName as 'Last Name'

from LLUser.LL_Custom_Marine_Feedback_Log MF
left join LLUser.KUAF on MF.Name = LLUser.KUAF.ID

where MF.VersionNum = ( select max(MF2.VersionNum) from
LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber = MF.DocNumber))

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, November 23, 2005 8:06 PM
To: eLink Recipient
Subject: Notes attached

Notes attached Posted by TenixAdmin (Administrator, TenixLink) on 11/23/2005 08:02 PM
In reply to: RE What is the purpose of the evaluations for NULL? Posted by ASTZCA01User1 (Fisher, Stephen J) on 11/22/2005 08:24 PM

Matthew and Stephen,

Thanks for your assistance thus far. I've attached a Word doc with screenshots to try and explain what I'm trying to do and the (mangled) results that occur.

BC

volvostephen

Message from via eLinkIt's hard to look at SQL without having a DB to test it out on. So, my lastthought on this is - try to use an OUTER JOIN. Soselect MF.DocNumber, MF.RevNumber, MF.Location, MF.Feedback, MF.Response, FirstName as 'First Name', LastName as 'Last Name' fromLLUser.LL_Custom_Marine_Feedback_Log as MF, LLUser.KUAF whereBecomesselect MF.DocNumber, MF.RevNumber, MF.Location, MF.Feedback, MF.Response, FirstName as 'First Name', LastName as 'Last Name' fromLLUser.LL_Custom_Marine_Feedback_Log as MFLEFT OUTER JOIN LLUser.KUAF ON MF.Name = LLUser.KUAF.IDwhereMF.VersionNum = ( select max(MF2.VersionNum) from LLUser.LL_Custom_Marine_Feedback_Log as MF2 where (MF2.DocNumber =MF.DocNumber)) You should be able to leave out all the OR's. The outer Join will returnall records from MF and if there is a matching KUAF.ID, it will also bringin last and first names. I think that is what you wanted. Then the last ORfor Doc Number - not sure why that is there. You want it whether it is NULLor not right? Then just leave out that parameter.Hope this finally gets you setup.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: November 23, 2005 8:09 PMTo: eLink RecipientSubject: Notes attached[Attachments exceeded pre-set size limits. Use the links provided to access]Notes attachedPosted by Administrator, TenixLink on 11/23/2005 08:02 PMMatthew and Stephen,Thanks for your assistance thus far. I've attached a Word doc withscreenshots to try and explain what I'm trying to do and the (mangled)results that occur.BC[To reply to this thread, use your normal E-mail reply function.]============================================================Attachment link: Post to OT KC re SQL prob.dochttps://knowledge.opentext.com/knowledge/livelink.exe/open/4349385============================================================Topic: LiveReport mangling my SQL output (NULLS included)https://knowledge.opentext.com/knowledge/livelink.exe/open/4341845Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/livelink.exeTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.