Home » SQL & PL/SQL » SQL & PL/SQL » no record fetched (oracle 9i.)
no record fetched [message #287742] Thu, 13 December 2007 06:09 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
dear all,

i m having two table HD_GATE_ENTRY & hd_receipt.
hd_gate_entry
VC_COMP_CODE      VARCHAR2(2 BYTE),
  VC_GATE_ENT_NO    VARCHAR2(10 BYTE),
  DT_GATE_ENT_DATE  DATE,
  VC_BILL_NO        VARCHAR2(20 BYTE),
  DT_BILL_DATE      DATE,
  VC_PARTY_TYPE     VARCHAR2(1 BYTE),
  NU_PARTY_CODE     NUMBER(6),
  DT_MOD_DATE       DATE,
  CH_STAT_FLAG      VARCHAR2(2 BYTE),
  CH_STAT_UP_FLAG   VARCHAR2(2 BYTE),
  VC_AUTH_CODE      VARCHAR2(2 BYTE),
  VC_DEFAULT_COMP   VARCHAR2(2 BYTE),
  VC_FIELD1         VARCHAR2(30 BYTE),
  VC_FIELD2         VARCHAR2(30 BYTE),
  VC_FIELD3         VARCHAR2(30 BYTE),
  VC_FIELD4         VARCHAR2(30 BYTE),
  NU_FIELD1         NUMBER(20,3),
  NU_FIELD2         NUMBER(20,3),
  DT_FIELD1         DATE,
  DT_FIELD2         DATE,
  DT_RECEIPT_DATE   DATE,
  CH_USER_CODE      CHAR(2 BYTE)



hd_receipt
VC_COMP_CODE         VARCHAR2(2 BYTE)         NOT NULL,
  VC_RECEIPT_NO        VARCHAR2(10 BYTE)        NOT NULL,
  DT_RECEIPT_DATE      DATE                     NOT NULL,
  VC_BILL_NO           VARCHAR2(10 BYTE),
  VC_CHALLAN_NO        VARCHAR2(20 BYTE),
  DT_CHALLAN_DATE      DATE,
  VC_GATE_ENT_NO       VARCHAR2(10 BYTE),
  VC_PARTY_TYPE        VARCHAR2(1 BYTE)         NOT NULL,
  NU_PARTY_CODE        NUMBER(6)                NOT NULL,
  VC_DOCUMENT_TYPE     VARCHAR2(1 BYTE),
  VC_DOCUMENT_NO       VARCHAR2(10 BYTE),
  VC_TRANSPORTER_CODE  VARCHAR2(30 BYTE),
  VC_VEHICLE_NO        VARCHAR2(10 BYTE),
  VC_QUALITY_FLG       VARCHAR2(1 BYTE),
  VC_MAIN_STORE_FLG    VARCHAR2(1 BYTE),
  VC_RMDA_FLG          VARCHAR2(1 BYTE),
  DT_BILL_DATE         DATE,
  CH_MIX_FLG           CHAR(1 BYTE),
  NU_TOT_QTY           NUMBER(12,3),
  DT_RECEIPT_DATE_QC   DATE,
  DT_RECEIPT_DATE_MS   DATE,
  VC_VEHICLE_TYPE      VARCHAR2(20 BYTE),
  VC_GR_RR_NO          VARCHAR2(10 BYTE),
  DT_GR_RR_DATE        DATE,
  VC_FORM_NO           VARCHAR2(20 BYTE),
  DT_MOD_DATE          DATE,
  CH_STAT_FLAG         VARCHAR2(2 BYTE),
  CH_STAT_UP_FLAG      VARCHAR2(2 BYTE),
  VC_AUTH_CODE         VARCHAR2(2 BYTE),
  VC_DEFAULT_COMP      VARCHAR2(2 BYTE),
  VC_FIELD1            VARCHAR2(30 BYTE),
  VC_FIELD2            VARCHAR2(30 BYTE),
  VC_FIELD3            VARCHAR2(30 BYTE),
  VC_FIELD4            VARCHAR2(30 BYTE),
  NU_FIELD1            NUMBER(20,3),
  NU_FIELD2            NUMBER(20,3),
  DT_FIELD1            DATE,
  DT_FIELD2            DATE,
  DT_DOCUMENT_DATE     DATE
)


now i want to display those gate entry no who is not in receipt table.

for it i made query but not working..
please help me out.

select * from hd_gate_entry e where
e.vc_gate_ent_no in( select b.VC_GATE_ENT_NO from hd_receipt b
where b.vc_gate_ent_no !=e.vc_gate_ent_no
and e.vc_comp_code= b.vc_comp_code)

no rows return.

Regards

Re: no record fetched [message #287746 is a reply to message #287742] Thu, 13 December 2007 06:19 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

may be you can try this:

 select * from hd_receipt hd_r where not exists
    (select null from hd_gate_entry hr_ge where hd_r.VC_COMP_CODE=hd_ge.VC_COMP_CODE)


regards,
Re: no record fetched [message #287750 is a reply to message #287742] Thu, 13 December 2007 06:27 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have NULL in VC_GATE_ENT_NO then your query will always return no row.

http://www.orafaq.com/forum/mv/msg/94020/287057/102589/#msg_287057

Regards
Michel
Previous Topic: Materialized Views
Next Topic: Count grouped by 6month period
Goto Forum:
  


Current Time: Thu Dec 08 10:30:30 CST 2016

Total time taken to generate the page: 0.09111 seconds