null value constraints issue

Question

1.00/5 (1 vote)

See more:

SQL

update sc_bill_details set dt_open_date =(select dt_bill_date from sc_bill where dt_bill_date>'2015-7-1' and sc_bill.st_bill_no=sc_bill_details.st_bill_no )

when m writing above query getting following error even after there is no null values in both the tables.

ERROR:  null value in column "dt_open_date" violates not-null constraint
DETAIL:  Failing row contains (VC2009030000242, VC20090300189, null, -1, -1, ECG, 1, 100, 100, 0, 9, 0, 1, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0).

Posted 14-Sep-15 20:20pm

Member 11919722

Updated 14-Sep-15 21:04pm

phil.o

v2

Add a Solution

Comments

Michael_Davies 15-Sep-15 2:31am

You say there "is no null values in both tables", have you executed the SELECT on its own outside of the UPDATE and looked at the results.

Member 11919722 15-Sep-15 2:41am

yes Michael i did all possible research actually when inner query is running then its giving null value to date column to outer query i dont know why as per my concern my above query is correct but y inner query is returning null value

Michael_Davies 16-Sep-15 2:29am

So, obviously there is something in the data that is amiss. We can not see the data.

Can you pull one row from the tables that produces a null and show them so we can see the source data.

Member 11919722 21-Sep-15 4:36am

actually the problem in above query written by me is that inner query is returning null value for date columns to outer query that's why null value constraint error is coming but I don't understand how can it be possible since bith the tables have data in date columns.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2015-09-14T21:21:00

Obviously the

SQL

select dt_bill_date from sc_bill where dt_bill_date>'2015-7-1' and sc_bill.st_bill_no=sc_bill_details.st_bill_no

query does not return any value.

I have searched for PostGRE datetime specification formats; have you tried to put two digits to the month and day numbers? I did not see anything about that, but all examples I can find use '2015-07-01' as date specification format rather than '2015-7-1'. I don't know the internals of PostGRE, nor did I ever develop anyhting based on this RDBMS, but it could be worth trying.

You could also change the comparison operator to >= and see if that returns some records.

You could also try with

SQL

select dt_bill_date
from sc_bill
inner join sc_bill_details on sc_bill.st_bill_no=sc_bill_details.st_bill_no
where dt_bill_date>'2015-07-01'

Hope this helps. Good luck.