Skip to Content
0
Jan 17, 2019 at 05:51 AM

SQL query runs well in SQL dev but returns blank value in “Add Command” in Crystal report

58 Views

I have a SQL query in sql Developer(Oracle Database),which run well and returns 4 columns and 2 records but if I use the same query in Add Command in Crystal report it returns blank values.

p.Name (In DB in schema1, Data type VARCHAR2(100 Char), Eg: "SAP Consulting LLC")

TN.TNA (In DB in schema1, Data type VARCHAR2(25 Char), Eg: "123-456788")

P.SUB (In DB in schema1, Data type VARCHAR2(25 Char), Eg: "0")

P.M_NO (In DB in schema1, Data type VARCHAR2(25 Char), Eg: "123456")

with ABC as (
select pnc.p_id from schema1.pp_pnc pnc, schema1.pp_n pn
where PNC.N_ID = PN.id
and PN.DS = 'ABC'
and PNC.END_DATE like '01-JAN-20'),
EFG as (select pnc.p_id from schema1.pp_pnc pnc, schema1.pp_n pn
where PNC.N_ID = PN.id
and PN.DS = 'EFG'
and PNC.END_DATE like '01-JAN-00')

select distinct P.name, TN.TNA, P.SUB , P.M_NO
from schema.PETA P
inner join SCHEMA1.ALPHA PTPC on P.id = PTPC.P_ID
inner join schema1.BETA TN on PTPC.TN_ID = TN.id
inner join ABC on P.id=ABC.P_ID
inner join EFG on P.id=EFG.P_ID
where PTPC.END_DATE > sysdate
and TN.TNA not in ('123-456788', '456-457896')