display columns showing ?

hi,
when i run the query below in sql server it works fine, but then in the livereport I get ? as the query results on "status". i've put status in the display columns just the same as revisionno and i am not sure why the live report won't display it properly

select d.name, d.modifydate, doctype, docnum, d.dcomment,
  ISNULL(l4.valstr, ' ') as status,
  ISNULL(l3.valstr, ' ') as revisionno,
  d.*
from vwCollections c
  left outer join
  llattrdata as l3
on (c.dataid = l3.id and c.version = l3.vernum
   and l3.attrid = 3 and l3.defid = 325616)
  left outer join
  llattrdata as l4
on (c.dataid = l4.id and c.version = l4.vernum
   and l4.attrid = 2 and l4.defid = 325616)
  inner join
dtree as d
on (c.dataid = d.dataid)
where c.collectionid = 9146210
order by d.name

Find more posts tagged with

Comments

Josephine_Bush

select  l4.valstr, d.*,d.name, d.modifydate, doctype, docnum, d.dcomment,
   ISNULL(l3.valstr, ' ') as revisionno,
from vwCollections c
  left outer join
  llattrdata as l3
on (c.dataid = l3.id and c.version = l3.vernum
   and l3.attrid = 3 and l3.defid = 325616)
  left outer join
  llattrdata as l4
on (c.dataid = l4.id and c.version = l4.vernum
   and l4.attrid = 2 and l4.defid = 325616)
  inner join
dtree as d
on (c.dataid = d.dataid)
where c.collectionid = 9146210
order by d.name

Lindsay_Davies

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

Hi Josephine,

I think your problem is that there is a already column in the table DTree called status and you have selected d.*, so you have some ambiguity.

Options...

1. Change the alias to something else, like statusval.

2. do not select d.* - only select the columns you want.

Comments...

Why select d.* and several explicit columns?

You might find it easier if you just selected the raw columns from the joined tables and then treated that result set as a pseudo table with a table alias.

Then you can apply the ISNULL and any other functions to the outer result set R.

It would then be more explicit to use populate display columns with R.<column>

select isnull(StatusX), R.Name, etc....

from (select l4.valstr StatusX, d.name, d.modifydate, doctype, docnum, d.dcomment, l3.valstr revisionno
from vwCollections c

left outer join llattrdata as l3 on (c.dataid = l3.id and c.version = l3.vernum and l3.attrid = 3 and l3.defid = 325616)

                  left outer join llattrdata as l4 on (c! .dataid = l4.id and c.version = l4.vernum and l4.attrid = 2 and l4.defid = 325616)
                  inner join dtree as d on (c.dataid = d.dataid)
             where c.collectionid = 9146210

) R
order by Name

Regards

Lindsay Davies

Open Text

European Escalation Team

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 October 03, Mon 16:56
To: eLink Recipient
Subject: display columns showing ?

display columns showing ?

Posted by josephine.bush@res-americas.com (Bush, Josephine) On 2011/10/03 11:52

figured out that if i move things around in the select statement and change the way they are referenced in the display columns then it works. was aliasing l4.valstr as status and trying to remove ? from results, but that doesnt work. instead have to refer to l4.valstr in the display column as valstr. and move it higher in the select statement. very weird, but i get data now - only problem is that i still get ? in the results where there isnt anything for l4.valstr - oh well. select  l4.valstr, d.*,d.name, d.modifydate, doctype, docnum, d.dcomment,
   ISNULL(l3.valstr, ' ') as revisionno,
from vwCollections c
  left outer join
  llattrdata as l3
on (c.dataid = l3.id and c.version = l3.vernum
   and l3.attrid = 3 and l3.defid = 325616)
  left outer join
  llattrdata as l4
on (c! .dataid = l4.id and c.version = l4.vernum
   and l4.attrid = 2 and l4.defid = 325616)
  inner join
dtree as d
on (c.dataid = d.dataid)
where c.collectionid = 9146210
order by d.name

[To post a comment, use the normal reply function]
Topic:	display columns showing ?
Forum:	Content Server LiveReports Forum
Livelink Server:	knowledge-wlweb01

Josephine_Bush

hi lindsay,

perfect thanks!