Skip to Content
avatar image
Former Member

Navigation arrow

Hi All Experts,

I would like to display navigation arrow in the query report as follows:


SELECT DISTINCT T1.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam], T0.[CreateDate], T0.[DocDate],
case 
when T0.[TransType] = '162' 
then ('MR'+' '+Cast((select distinct a.docnum from OMRV a with (nolock) inner join MRV1 b on a.docentry = b.docentry 
where a.docnum = t0.base_ref and b.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '16'
then ('RE'+' '+Cast((select distinct c.docnum from ORDN c with (nolock) inner join RDN1 d on c.docentry = d.docentry 
where c.docnum = t0.base_ref and d.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '15'
then ('DN'+' '+Cast((select distinct e.docnum from ODLN e with (nolock) inner join DLN1 f on e.docentry = f.docentry 
where e.docnum = t0.base_ref and f.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '13'
then ('IN'+' '+Cast((select distinct g.docnum from OINV g with (nolock) inner join INV1 h on g.docentry = h.docentry 
where g.docnum = t0.base_ref and h.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '14'
then ('CM'+' '+Cast((select distinct i.docnum from ORIN i with (nolock) inner join RIN1 j on i.docentry = j.docentry 
where i.docnum = t0.base_ref and j.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '20'
then ('PD'+' '+Cast((select distinct k.docnum from OPDN k with (nolock) inner join PDN1 l on l.docentry = k.docentry 
where k.docnum = t0.base_ref and l.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '21'
then ('PR'+' '+Cast((select distinct m.docnum from ORPD m with (nolock) inner join MRV1 n on m.docentry = n.docentry 
where m.docnum = t0.base_ref and n.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '18'
then ('PU'+' '+Cast((select distinct o.docnum from OPCH o with (nolock) inner join PCH1 p on o.docentry = p.docentry 
where o.docnum = t0.base_ref and p.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '19'
then ('PC'+' '+Cast((select distinct q.docnum from ORPC q with (nolock) inner join RPC1 r on q.docentry = r.docentry 
where q.docnum = t0.base_ref and r.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '58'
then ('SU'+' '+Cast((select distinct s.base_ref from OINM s with (nolock) 
where s.base_ref = t0.base_ref and s.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '59'
then ('SI'+' '+Cast((select distinct t.docnum from OIGN t with (nolock) inner join IGN1 u on t.docentry = u.docentry 
where t.docnum = t0.base_ref and u.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '60'
then ('SO'+' '+Cast((select distinct v.docnum from OIGE v with (nolock) inner join IGE1 w on v.docentry = w.docentry 
where v.docnum = t0.base_ref and w.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '67'
then ('IM'+' '+Cast((select distinct x.docnum from OWTR x with (nolock) inner join WTR1 y on x.docentry = y.docentry 
where x.docnum = t0.base_ref and y.itemcode = t0.itemcode for browse) as varchar))
when T0.[TransType] = '69'
then ('IF'+' '+Cast((select distinct z.docnum from OIPF z with (nolock) inner join IPF1 aa on z.docentry = aa.docentry 
where z.docnum = t0.base_ref and aa.itemcode = t0.itemcode for browse) as varchar))
end as 'Document', T0.[Warehouse], 
(T0.[InQty] - t0.outqty)"Quantity", T0.[CalcPrice], T0.[Price] FROM [dbo].[OINM]  T0 INNER JOIN OITM T1 ON 
T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod 
WHERE T0.[ItemCode] = 'A10031'
order by t0.createdate asc

Pls give advice how to display the navigation arrow using the above query. Thx in advance

Rgd,

John Ed

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 22, 2010 at 08:27 AM

    Use the following query

     SELECT DISTINCT T1.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam], T0.[CreateDate], T0.[DocDate], case when T0.[TransType] = '162' then ('MR'+' '+Cast((select distinct a.docnum from OMRV a with (nolock) inner join MRV1 b on a.docentry = b.docentry where a.docnum = t0.base_ref and b.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '16' then ('RE'+' '+Cast((select distinct c.docnum from ORDN c with (nolock) inner join RDN1 d on c.docentry = d.docentry where c.docnum = t0.base_ref and d.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '15' then ('DN'+' '+Cast((select distinct e.docnum from ODLN e with (nolock) inner join DLN1 f on e.docentry = f.docentry where e.docnum = t0.base_ref and f.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '13' then ('IN'+' '+Cast((select distinct g.docnum from OINV g with (nolock) inner join INV1 h on g.docentry = h.docentry where g.docnum = t0.base_ref and h.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '14' then ('CM'+' '+Cast((select distinct i.docnum from ORIN i with (nolock) inner join RIN1 j on i.docentry = j.docentry where i.docnum = t0.base_ref and j.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '20' then ('PD'+' '+Cast((select distinct k.docnum from OPDN k with (nolock) inner join PDN1 l on l.docentry = k.docentry where k.docnum = t0.base_ref and l.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '21' then ('PR'+' '+Cast((select distinct m.docnum from ORPD m with (nolock) inner join MRV1 n on m.docentry = n.docentry where m.docnum = t0.base_ref and n.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '18' then ('PU'+' '+Cast((select distinct o.docnum from OPCH o with (nolock) inner join PCH1 p on o.docentry = p.docentry where o.docnum = t0.base_ref and p.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '19' then ('PC'+' '+Cast((select distinct q.docnum from ORPC q with (nolock) inner join RPC1 r on q.docentry = r.docentry where q.docnum = t0.base_ref and r.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '58' then ('SU'+' '+Cast((select distinct s.base_ref from OINM s with (nolock) where s.base_ref = t0.base_ref and s.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '59' then ('SI'+' '+Cast((select distinct t.docnum from OIGN t with (nolock) inner join IGN1 u on t.docentry = u.docentry where t.docnum = t0.base_ref and u.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '60' then ('SO'+' '+Cast((select distinct v.docnum from OIGE v with (nolock) inner join IGE1 w on v.docentry = w.docentry where v.docnum = t0.base_ref and w.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '67' then ('IM'+' '+Cast((select distinct x.docnum from OWTR x with (nolock) inner join WTR1 y on x.docentry = y.docentry where x.docnum = t0.base_ref and y.itemcode = t0.itemcode for browse) as varchar)) when T0.[TransType] = '69' then ('IF'+' '+Cast((select distinct z.docnum from OIPF z with (nolock) inner join IPF1 aa on z.docentry = aa.docentry where z.docnum = t0.base_ref and aa.itemcode = t0.itemcode for browse) as varchar)) end as 'Document', T0.[Warehouse], (T0.[InQty] - t0.outqty)"Quantity", T0.[CalcPrice], T0.[Price] FROM [dbo].[OINM] T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod WHERE T0.[ItemCode] = 'A10031' order by t0.createdate asc  for browse
    

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 22, 2010 at 11:03 AM

    not answered yet

    Add comment
    10|10000 characters needed characters exceeded