Getting more records than expected with simple query in postgresql

Transact-SQL
1

I have these query:

  select b.type,b.name,a.name_b,a.start_time
	 	 from runinfo a left JOIN definition b
		 				on a.sched_table=b.parent_table
		 				and a.name_b=b.name
		 			    where sched_table not like 'PLAN' 
		 and to_char(start_time,'YYYYMMDD') = to_char(current_date-1,'YYYYMMDD')
		 order by start_time desc;

Tables are:

DEFINITION
----------

TYPE: COMMAND
NAME: DAVE
.........

RUNINFO
-------

START_TIME: 2019/08/15 23:59
NAME_B: DAVE
.........
START_TIME: 2019/08/15 23:58
NAME_B: DAVE
........

I get duplicate rows and I can't understand:

TYPE     NAME   START_TIME           NAME_B
COMMAND  DAVE   2019/08/15 23:59     DAVE
COMMAND  DAVE   2019/08/15 23:59     DAVE
COMMAND  DAVE   2019/08/15 23:58     DAVE
COMMAND  DAVE   2019/08/15 23:58     DAVE

Why I get two rows of every record?

thanks and sorry for my English!

2

hope this helps :slight_smile: :slight_smile:

becuase you are comparing by date YYYYMMDD
you should compare with date and time here YYYYMMDD HH:MM:SS

this
to_char(start_time,'YYYYMMDD') = to_char(current_date-1,'YYYYMMDD')
should be this
start_time = current_date-1

3

Not works. If I do :
SELECT * FROM RUNINFO WHERE NAME_B='DAVE and start_time = current_date-1
ORDER BY START_TIME DESC;
Don't get any result and there is results in the table.

If I make these:

to_char(start_time,'YYYYMMDD') = to_char(current_date-1,'YYYYMMDD')

I get results

4

Hi

Depending on your requirements

You can use distinct

Or

Top 1

Select distinct rest of statement

Select top 1 rest of statement

5

hi

Another thing is

TableA
name
Harish ( 1 record )

join to

TableB
name
Harish ( 2 records )

will give result
Harish
Harish

This coud be your issue !!!

:slight_smile: :slight_smile:

6

  1. You need to provide DDL and sample data. The fact you have columns sched_table and parent_table in your query, but have not mentioned them in your definition of the tables, suggests that is where the problem is.

  2. It is not a good idea to convert dates to characters before comparison in any type of SQL. As postgresql is meant to be similar to Oracle try something like:

AND a.start_time >= TRUNC(current_date - 1)
AND a.start_time < TRUNC(current_date)