Horton

Thread

  1. Returned row count doesn't match lines in output file

    Tchouante, Merlin <mtchouan@umaryland.edu> — 2019-11-07T17:57:16Z

    Hello group,

I'm new to this group so please bear with me.


select count (u.user_id)
from users u, course_main cm, course_users cu
where cu.crsmain_pk1 = cm.pk1
and cu.users_pk1 = u.pk1
and cm.course_id = 'Org.dent.Training';

(4915 rows)


I'm executing an .sql file which looks like this:

\o /home/bbuser/banner/gradeload/sodorgusers.txt
\t on
select u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id
from users u, course_main cm, course_users cu
where cu.crsmain_pk1 = cm.pk1
and cu.users_pk1 = u.pk1
and cm.course_id = 'Org.dent.Training'
order by u.lastname, u.firstname;
\t off
\o

When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of 4916.  What is causing the blank lines?  When I strip away the blank lines from the file, I get 3525 lines.  Why aren't all 4,915 records writing to the file?


Any and all help would be greatly appreciated.

Thanks,
  -- Merlin


Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS)
601 West Lombard Street
Baltimore, Maryland 21201-1512
mtchouan@umaryland.edu<mailto:mtchouan@umaryland.edu>
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu<mailto:dl-citsbbsupport@umaryland.edu>
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu<mailto:DL-CITSMediasiteSupport@umaryland.edu>

[New UMB Logo]

  2. Re: Returned row count doesn't match lines in output file

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-07T18:06:31Z

    "Tchouante, Merlin" <mtchouan@umaryland.edu> writes:
> I'm executing an .sql file which looks like this:

> \o /home/bbuser/banner/gradeload/sodorgusers.txt
> \t on
> select u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id
> from users u, course_main cm, course_users cu
> where cu.crsmain_pk1 = cm.pk1
> and cu.users_pk1 = u.pk1
> and cm.course_id = 'Org.dent.Training'
> order by u.lastname, u.firstname;
> \t off
> \o

> When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of 4916.  What is causing the blank lines?

Null values in one or more of the columns you're concatenating, perhaps?
Concatenating a null with something else yields null.  (See
coalesce() for one ad-hoc way to fix that.)

			regards, tom lane

  3. Re: Returned row count doesn't match lines in output file

    Rob Sargent <robjsargent@gmail.com> — 2019-11-07T18:06:33Z

    
> On Nov 7, 2019, at 10:57 AM, Tchouante, Merlin <mtchouan@umaryland.edu> wrote:
> 
> Hello group,
>  
> I’m new to this group so please bear with me.
>  
>  
> select count (u.user_id)
> from users u, course_main cm, course_users cu
> where cu.crsmain_pk1 = cm.pk1
> and cu.users_pk1 = u.pk1
> and cm.course_id = 'Org.dent.Training';
>  
> (4915 rows)
>  
>  
> I’m executing an .sql file which looks like this:
>  
> \o /home/bbuser/banner/gradeload/sodorgusers.txt
> \t on
> select u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id
> from users u, course_main cm, course_users cu
> where cu.crsmain_pk1 = cm.pk1
> and cu.users_pk1 = u.pk1
> and cm.course_id = 'Org.dent.Training'
> order by u.lastname, u.firstname;
> \t off
> \o
>  
> When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of 4916.  What is causing the blank lines?  When I strip away the blank lines from the file, I get 3525 lines.  Why aren’t all 4,915 records writing to the file?
>  

Are there any null values in any of the fields you’re concatenating?  Try the same query but return the separate columns.  Maybe add \pset null nil to get other than whitespace for null.

>  
> Any and all help would be greatly appreciated.
>  
> Thanks,
>   -- Merlin
>  
>  
> Merlin D. Tchouante, Sr. IT Enterprise Application Developer
> Center for Information Technology Services (CITS) 
> 601 West Lombard Street 
> Baltimore, Maryland 21201-1512 
> mtchouan@umaryland.edu <mailto:mtchouan@umaryland.edu>  
> 410-706-4489 * 410-706-1500 fax
>  
> Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu <mailto:dl-citsbbsupport@umaryland.edu>
> Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu <mailto:DL-CITSMediasiteSupport@umaryland.edu>
>  
> <image001.jpg>

  4. Re: Returned row count doesn't match lines in output file

    Steve Midgley <science@misuse.org> — 2019-11-07T18:06:38Z

    On Thu, Nov 7, 2019 at 9:58 AM Tchouante, Merlin <mtchouan@umaryland.edu>
wrote:

> Hello group,
>
>
>
> I’m new to this group so please bear with me.
>
>
>
>
>
> select count (u.user_id)
>
> from users u, course_main cm, course_users cu
>
> where cu.crsmain_pk1 = cm.pk1
>
> and cu.users_pk1 = u.pk1
>
> and cm.course_id = 'Org.dent.Training';
>
>
>
> (4915 rows)
>
>
>
>
>
> I’m executing an .sql file which looks like this:
>
>
>
> \o /home/bbuser/banner/gradeload/sodorgusers.txt
>
> \t on
>
> select
> u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id
>
> from users u, course_main cm, course_users cu
>
> where cu.crsmain_pk1 = cm.pk1
>
> and cu.users_pk1 = u.pk1
>
> and cm.course_id = 'Org.dent.Training'
>
> order by u.lastname, u.firstname;
>
> \t off
>
> \o
>
>
>
> When I look at the output file, it has a bunch of blank lines in between
> the records but displays a line count of 4916.  What is causing the blank
> lines?  When I strip away the blank lines from the file, I get 3525 lines.
> Why aren’t all 4,915 records writing to the file?
>
>
>
>
>
If you just run a simple select on user_id (none of the pipe concat stuff)
from the file, what results do you get? Also if you run the count statement
from the file, what results do you get? (Just want to make sure you're not
accidentally running against a different server, database or default schema
as the source of the problem..)

Steve

  5. RE: Returned row count doesn't match lines in output file

    Tchouante, Merlin <mtchouan@umaryland.edu> — 2019-11-07T18:29:17Z

    Thank you so much for responding.

Yes, one of the selected columns is null; when I only select one column that I know can't be a null, it returns all of them.  I changed it to the below and it worked just fine, thank you so much.

select u.user_id||'|'||coalesce(u.firstname,'')||'|'||coalesce(u.lastname,'')||'|'||coalesce(u.email,'')||'|'||coalesce(u.student_id,'')

Thanks,
  -- Merlin
 
 
Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS) 
601 West Lombard Street 
Baltimore, Maryland 21201-1512 
mtchouan@umaryland.edu  
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu




-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Thursday, November 7, 2019 1:07 PM
To: Tchouante, Merlin <mtchouan@umaryland.edu>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Returned row count doesn't match lines in output file

"Tchouante, Merlin" <mtchouan@umaryland.edu> writes:
> I'm executing an .sql file which looks like this:

> \o /home/bbuser/banner/gradeload/sodorgusers.txt
> \t on
> select 
> u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.stu
> dent_id from users u, course_main cm, course_users cu where 
> cu.crsmain_pk1 = cm.pk1 and cu.users_pk1 = u.pk1 and cm.course_id = 
> 'Org.dent.Training'
> order by u.lastname, u.firstname;
> \t off
> \o

> When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of 4916.  What is causing the blank lines?

Null values in one or more of the columns you're concatenating, perhaps?
Concatenating a null with something else yields null.  (See
coalesce() for one ad-hoc way to fix that.)

			regards, tom lane