Inserting data in one table as selecting from other tables

Question

Inserting data in one table as selecting from other tables

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

priest Ivan Delic SSC Eights! Points: 942 More actions · Answer 1

Thanks laurie!

There is something else but I think I could work it out. One of the problem was a thing that I didn't entered few row data, I left them NULL (for instance Father occupation, mother birthname - those data wasn't entered in original paper data that was given to me).

When I executed T-SQL

Select Father_FirstName + ' ' + Father_LastName + ' ' + Father_Occupation... AS Parent

Result was NULL

cause of NULL data in Father_Occupation field (column).

I will try, however, to work it out on my own. Thanks for the help.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

priest Ivan Delic (8/14/2012)
Thanks laurie!
There is something else but I think I could work it out. One of the problem was a thing that I didn't entered few row data, I left them NULL (for instance Father occupation, mother birthname - those data wasn't entered in original paper data that was given to me).
When I executed T-SQL
Select Father_FirstName + ' ' + Father_LastName + ' ' + Father_Occupation... AS Parent
Result was NULL
cause of NULL data in Father_Occupation field (column).
I will try, however, to work it out on my own. Thanks for the help.

Try

select IsNull(Father_FirstName,'') + ...

which replaces NULLs with empty strings and should make things work.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

If you add a NULL into a string, the result is NULL, so you need to handle the nulls individually like this:

ISNULL(first value, second value) - returns the first non-null value. COALESCE can handle more than 2 values - I don't think you need this though.

So you would use:

Select ISNULL(Father_FirstName + ' ', '') + ISNULL(Father_LastName + ' ', '') + ISNULL(Father_Occupation... AS Parent

You only need to deal with the columns which contain nulls (of course).

Note: The way it's written stops too many spaces being entered in the string.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

priest Ivan Delic (8/13/2012)
Hi!
I forgot one thing. In paper-data there is about 30 books with 100 pages in each of them. On each page there is 10 records, so 1,000 records per book, equally about 30,000 records all.
In app that was given to me, I have assignment to print only one record on printing form (which is called in Serbia Baptism paper, I don't know in English how to call it). So I need to print one record on one page with all data for that record (book, page, number, dates, name, parents, priest, godfather, about 15 fields). As for ID field, it is PK, Auto-increment and not changeable in app.
Sincerely yours,
Fr. Ivan
Serbia

I make a small observation.....it would appear from what you have said that that the printing app requires you to provide the order of book/page/number etc prior to print....by linking to the RECORDS table on ID ...it seems you only have one method of sort order?

what are you going to do if, for instance you decide to change your mind and print 5 records per page, book - place of baptism - chronological order of dob?

I am not sure that the code will give that you a degree of flexibility going forward...but thats for you to decide.

...or of course, I may have misunderstood 🙂

kind regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 5

I read it as: the book/page/entry data refers to the original hand-written books, and the data is just required to print on the certificate so it can be referred-back to the books if required.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

laurie-789651 (8/15/2012)
I read it as: the book/page/entry data refers to the original hand-written books, and the data is just required to print on the certificate so it can be referred-back to the books if required.

you may well be correct...I didnt read it that way originally....but can now see your viewpoint.

we can only wait and see if the OP has a "view" on this.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

priest Ivan Delic SSC Eights! Points: 942 More actions · Answer 7

I just want to say all of you who helped me out.

Regarding the NULL values I decided to fill it with blank values by simply putting this statement online

UPDATE PERSONS

SET Father_Occupation = ''

WHERE Father_Occupation IS NULL

GO

It worked for me, now I don't see those NULL values anymore.

Laurie was right about understanding my case, I just needed an app for printing one record from original baptism book