Re: Returned row count doesn't match lines in output file
Rob Sargent <[email protected]> Thu, 7 Nov 2019 11:06:33 -0700
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_8F6A6E58-DA4D-476B-B104-D932E560247E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Nov 7, 2019, at 10:57 AM, Tchouante, Merlin = <[email protected]> wrote: >=20 > Hello group, > =20 > I=E2=80=99m new to this group so please bear with me. > =20 > =20 > select count (u.user_id) > from users u, course_main cm, course_users cu > where cu.crsmain_pk1 =3D cm.pk1 > and cu.users_pk1 =3D u.pk1 > and cm.course_id =3D 'Org.dent.Training'; > =20 > (4915 rows) > =20 > =20 > I=E2=80=99m executing an .sql file which looks like this: > =20 > \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 =3D cm.pk1 > and cu.users_pk1 =3D u.pk1 > and cm.course_id =3D 'Org.dent.Training' > order by u.lastname, u.firstname; > \t off > \o > =20 > 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=E2=80=99t all 4,915 records writing to the file? > =20 Are there any null values in any of the fields you=E2=80=99re = concatenating? Try the same query but return the separate columns. = Maybe add \pset null nil to get other than whitespace for null. > =20 > Any and all help would be greatly appreciated. > =20 > Thanks, > -- Merlin > =20 > =20 > Merlin D. Tchouante, Sr. IT Enterprise Application Developer > Center for Information Technology Services (CITS)=20 > 601 West Lombard Street=20 > Baltimore, Maryland 21201-1512=20 > [email protected] <mailto:[email protected]> =20 > 410-706-4489 * 410-706-1500 fax > =20 > Please send Blackboard questions to the CITS support email address: = [email protected] <mailto:[email protected]> > Please send Mediasite questions to the CITS support email address: = [email protected] = <mailto:[email protected]> > =20 > <image001.jpg> --Apple-Mail=_8F6A6E58-DA4D-476B-B104-D932E560247E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; = charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br = class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div = class=3D"">On Nov 7, 2019, at 10:57 AM, Tchouante, Merlin <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>> wrote:</div><br = class=3D"Apple-interchange-newline"><div class=3D""><div = class=3D"WordSection1" style=3D"page: WordSection1; caret-color: rgb(0, = 0, 0); font-family: CourierNewPSMT; font-size: 14px; font-style: normal; = font-variant-caps: normal; font-weight: normal; letter-spacing: normal; = text-align: start; text-indent: 0px; text-transform: none; white-space: = normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; = text-decoration: none;"><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">Hello = group,<o:p class=3D""></o:p></div><div style=3D"margin: 0in 0in = 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p class=3D""> </o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">I=E2=80=99m new to this group so please bear with me.<o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""><o:p = class=3D""> </o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""><o:p = class=3D""> </o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">select = count (u.user_id)<o:p class=3D""></o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">from users u, course_main cm, course_users cu<o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">where = cu.crsmain_pk1 =3D cm.pk1<o:p class=3D""></o:p></div><div style=3D"margin:= 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">and cu.users_pk1 =3D u.pk1<o:p class=3D""></o:p></div><div = style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: = Calibri, sans-serif;" class=3D"">and cm.course_id =3D = 'Org.dent.Training';<o:p class=3D""></o:p></div><div style=3D"margin: = 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p class=3D""> </o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">(4915 rows)<o:p class=3D""></o:p></div><div style=3D"margin: = 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p class=3D""> </o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p class=3D""> </o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">I=E2=80=99m executing an .sql file which looks like this:<o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""><o:p = class=3D""> </o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">\o = /home/bbuser/banner/gradeload/sodorgusers.txt<o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">\t on<o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">select = u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student= _id<o:p class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">from = users u, course_main cm, course_users cu<o:p class=3D""></o:p></div><div = style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: = Calibri, sans-serif;" class=3D"">where cu.crsmain_pk1 =3D cm.pk1<o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">and = cu.users_pk1 =3D u.pk1<o:p class=3D""></o:p></div><div style=3D"margin: = 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">and cm.course_id =3D 'Org.dent.Training'<o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D"">order by = u.lastname, u.firstname;<o:p class=3D""></o:p></div><div style=3D"margin: = 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">\t off<o:p class=3D""></o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">\o<o:p class=3D""></o:p></div><div style=3D"margin: 0in 0in = 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p class=3D""> </o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D"">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=E2=80=99t all 4,915 records = writing to the file?<o:p class=3D""></o:p></div><div style=3D"margin: = 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p = class=3D""> </o:p></div></div></div></blockquote><div><br = class=3D""></div>Are there any null values in any of the fields you=E2=80=99= re concatenating? Try the same query but return the separate = columns. Maybe add \pset null nil to get other than whitespace for = null.</div><div><br class=3D""><blockquote type=3D"cite" class=3D""><div = class=3D""><div class=3D"WordSection1" style=3D"page: WordSection1; = caret-color: rgb(0, 0, 0); font-family: CourierNewPSMT; font-size: 14px; = font-style: normal; font-variant-caps: normal; font-weight: normal; = letter-spacing: normal; text-align: start; text-indent: 0px; = text-transform: none; white-space: normal; word-spacing: 0px; = -webkit-text-stroke-width: 0px; text-decoration: none;"><div = style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: = Calibri, sans-serif;" class=3D""><o:p class=3D""> </o:p></div><div = style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: = Calibri, sans-serif;" class=3D"">Any and all help would be greatly = appreciated.<o:p class=3D""></o:p></div><div style=3D"margin: 0in 0in = 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p class=3D""> </o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><b class=3D""><i class=3D""><span style=3D"color: teal;" = class=3D"">Thanks,</span></i></b><o:p class=3D""></o:p></div><div = style=3D"margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: = Calibri, sans-serif;" class=3D""><b class=3D""><i class=3D""><span = style=3D"" class=3D""> -- Merlin</span></i></b><o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""> <o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""> <o:p = class=3D""></o:p></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""><b = class=3D""><i class=3D""><span style=3D"" class=3D"">Merlin D. = Tchouante,</span></i></b><span style=3D"" class=3D""><span = class=3D"Apple-converted-space"> </span></span><span style=3D"color: = maroon;" class=3D"">Sr. IT Enterprise Application Developer</span><span = style=3D"color: navy;" class=3D""><br class=3D""></span><b class=3D""><i = class=3D""><span style=3D"font-size: 10pt; font-family: Arial, = sans-serif; color: red;" class=3D"">Center for Information Technology = Services (CITS)</span></i></b><span style=3D"color: navy;" = class=3D""><span class=3D"Apple-converted-space"> </span><br = class=3D""></span><i class=3D""><span style=3D"font-size: 10pt; = font-family: Arial, sans-serif; color: navy;" class=3D"">601 West = Lombard Street</span></i><span style=3D"color: navy;" class=3D""><span = class=3D"Apple-converted-space"> </span><br class=3D""></span><i = class=3D""><span style=3D"font-size: 10pt; font-family: Arial, = sans-serif; color: navy;" class=3D"">Baltimore, Maryland = 21201-1512</span></i><span style=3D"color: navy;" class=3D""><span = class=3D"Apple-converted-space"> </span><br class=3D""></span><a = href=3D"mailto:[email protected]" = title=3D"mailto:[email protected]" style=3D"color: rgb(149, 79, = 114); text-decoration: underline;" class=3D""><i class=3D""><span = style=3D"font-size: 10pt; font-family: Arial, sans-serif; color: blue;" = class=3D"">[email protected]</span></i></a><span style=3D"" = class=3D""> <span class=3D"Apple-converted-space"> </span><br = class=3D""></span><span style=3D"font-size: 10pt; font-family: Arial, = sans-serif; color: teal;" class=3D"">410-706-4489 * 410-706-1500 fax<o:p = class=3D""></o:p></span></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""><span = style=3D"font-size: 10pt; font-family: Arial, sans-serif; color: teal;" = class=3D""><o:p class=3D""> </o:p></span></div><div style=3D"margin: = 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><i class=3D""><span style=3D"font-size: 10pt;" = class=3D"">Please send Blackboard questions to the CITS support email = address:</span></i><span style=3D"font-size: 10pt;" class=3D""> <span= class=3D"Apple-converted-space"> </span></span><a = href=3D"mailto:[email protected]" style=3D"color: rgb(149, = 79, 114); text-decoration: underline;" class=3D""><span = style=3D"font-size: 10pt; color: blue;" = class=3D"">[email protected]</span></a><span = style=3D"font-size: 10pt;" class=3D""><o:p = class=3D""></o:p></span></div><div style=3D"margin: 0in 0in 0.0001pt; = font-size: 11pt; font-family: Calibri, sans-serif;" class=3D""><i = class=3D""><span style=3D"font-size: 10pt; color: rgb(54, 95, 145);" = class=3D"">Please send Mediasite questions to the CITS support email = address:</span></i><span style=3D"font-size: 10pt; color: rgb(54, 95, = 145);" class=3D""> <span = class=3D"Apple-converted-space"> </span></span><a = href=3D"mailto:[email protected]" style=3D"color: = rgb(149, 79, 114); text-decoration: underline;" class=3D""><span = style=3D"font-size: 10pt; color: blue;" = class=3D"">[email protected]</span></a><u = class=3D""><span style=3D"font-size: 10pt; color: blue;" class=3D""><o:p = class=3D""></o:p></span></u></div><div style=3D"margin: 0in 0in = 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><o:p class=3D""> </o:p></div><div style=3D"margin: 0in = 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" = class=3D""><span = id=3D"cid:[email protected]"><image001.jpg></span></div= ></div></div></blockquote></div><br class=3D""></body></html>= --Apple-Mail=_8F6A6E58-DA4D-476B-B104-D932E560247E--