oracle query for get rows whics have null columns from generated query

Question

I have query which returns some rows with null columns. I wanted only get the rows which have null columns. here is my query

SELECT
eofficeuat.gatepass.agent_id,
eofficeuat.cnf_agents.agent_name,
eofficeuat.entrylog_vehicle.passnumber AS passnumber,
eofficeuat.gatepass.id,
eofficeuat.entrylog_vehicle.action,
to_char(eofficeuat.gatepass.issuedatetime, 'dd-mm-yyyy HH12:MI:SS PM')  AS issueddatetime

FROM
eofficeuat.gatepass
inner JOIN eofficeuat.cnf_agents ON eofficeuat.gatepass.agent_id = eofficeuat.cnf_agents.agent_id
left JOIN eofficeuat.entrylog_vehicle ON eofficeuat.gatepass.id = eofficeuat.entrylog_vehicle.passnumber

WHERE
trunc((eofficeuat.gatepass.issuedatetime - DATE '1970-01-01') * 60 * 60 * 24) - 21602 >= 1569952800
AND trunc((eofficeuat.gatepass.issuedatetime - DATE '1970-01-01') * 60 * 60 * 24) < (
    SELECT
        ( sysdate - DATE '1970-01-01' ) * 86400000
    FROM
        dual
)

order by eofficeuat.gatepass.issuedatetime desc

this showing output like this image in the link https://gyazo.com/e9e150547b8f104f832cd190ff5392a2

can you please help for this? thanks

Why < ( SELECT ( sysdate - DATE '1970-01-01' ) * 86400000 FROM dual )? Just write < ( sysdate - DATE '1970-01-01' ) * 86400000 — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 2, 2019 at 6:26
Please don't paste link to images. User formatted text for result. This page may help you to format it. — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 2, 2019 at 6:28
You WHERE condition looks a bit weird. You can write is simpler like WHERE TRUNC(eofficeuat.gatepass.issuedatetime) - (6/24) >= to_date('2019-10-01 20:00', 'YYYY-MM-DD HH24:MI') AND TRUNC(eofficeuat.gatepass.issuedatetime) < sysdate. You don't have to screw with Unix timestamps. — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 2, 2019 at 6:43

Littlefoot · Accepted Answer · 2019-10-02 06:27:28Z

2

I can't view images, but - if I understood the question, that would be

where (   agent_id   is null 
       or agent_name is null
       or passnumber is null
       or ...
     )

answered Oct 2, 2019 at 6:27

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Littlefoot Over a year ago

I'm glad if it is.

mdkamrul Over a year ago

how can i remove or not showup passnumber columun from my query

Littlefoot Over a year ago

Just omit it from the SELECT statement.

Collectives™ on Stack Overflow

oracle query for get rows whics have null columns from generated query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related