No question at this time
DBA Top 10
1 M. Cadot 9300
2 B. Vroman 7100
3 A. Kavsek 4600
4 P. Wisse 2400
5 J. Péran 1700
6 J. Schnackenberg 1400
7 T. Boles 800
8 D. Dave 400
9 S. Spb 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48613
Total active users1400
Act. users last 24h3
Act. users last hour0
Registered user hits last week107
Registered user hits last month432
Go up

Right Join is not working properly
Next thread: How to read table columns into Shell variables?
Prev thread: Grid Infrastructure 19c installation fails

Message Score Author Date
Hi Experts, Below is the Query which i am using...... Rahul Singh May 07, 2021, 15:29
<pre>SQL> select to_date('2021-05-01 01:10:00') ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 07, 2021, 17:06
Hi Michel, You mean to say its a date formattin...... Rahul Singh May 10, 2021, 07:22
I meant first fix that. Then if there is still ...... Michel Cadot May 10, 2021, 07:32
Hi Michel, The query is basically i am running ...... Rahul Singh May 10, 2021, 09:04
Hello Rahul, probably a typo when you've copied...... Bruno Vroman May 10, 2021, 14:34
Hi Bruno, 1. Record_date is part of "Pass_Valid...... Rahul Singh May 11, 2021, 07:58
Hello Rahul, -a- you don't have to tell <i>me</...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman May 11, 2021, 16:52
Hi bruno, Thanks for the response, however stil...... Rahul Singh May 12, 2021, 10:18
Hello Rahul, here we are! As you see, prefixing...... Bruno Vroman May 12, 2021, 15:01
Thanks Bruno its works as expected now.... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Rahul Singh May 13, 2021, 10:35

Follow up by mail Click here


Subject: Right Join is not working properly
Author: Rahul Singh, India
Date: May 07, 2021, 15:29, 48 days ago
Os info: Windows
Oracle info: 11g
Message: Hi Experts,

Below is the Query which i am using to fetch the records from 2 tables where one table is empty.

Problem :

Below query only giving result when Date filter is comment out. without date filter its giving desire output.

SELECT b.station_id,
Sum(CASE WHEN validation_type=0 and pass_id=60 THEN 1 ELSE 0 END) T60,
Sum(CASE WHEN validation_type=0 and pass_id=15 THEN 1 ELSE 0 END) T15,
Sum(CASE WHEN validation_type=0 and pass_id=63 THEN 1 ELSE 0 END) UT,
Sum(CASE WHEN validation_type=0 and pass_id=21 THEN 1 ELSE 0 END) T45
from "Pass_Validation" a right join "CC_Station" b
on a.validation_station_id=b.station_id
-- where record_date >= '2021-05-01 01:10:00' and record_date <= '2021-05-05 01:10:00'
group by b.station_id
order by b.station_id

Desire Output :

station_id t60 t15 ut t45
1 0 0 0 0
2 0 0 0 0

When un-comment nothing is comming
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Michel Cadot, France
Date: May 07, 2021, 17:06, 48 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
SQL> select to_date('2021-05-01 01:10:00') from dual;

select to_date('2021-05-01 01:10:00') from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Rahul Singh, India
Date: May 10, 2021, 07:22, 45 days ago
Message: Hi Michel,

You mean to say its a date formatting issue or something else?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Michel Cadot, France
Date: May 10, 2021, 07:32, 45 days ago
Message:
I meant first fix that.
Then if there is still a problem, post a test case so we can reproduce what you have.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Rahul Singh, India
Date: May 10, 2021, 09:04, 45 days ago
Message: Hi Michel,

The query is basically i am running in postgres DB, the same query with date formatting below is working correctly in oracle.

record_date between
to_char ('01/01/2020 01:10:00','MM/DD/YYYY HH24:MI:ss) and to_char ('01/02/2020 01:10:00','MM/DD/YYYY HH24:MI:ss)

but same is not working in Postgres


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Bruno Vroman, Belgium
Date: May 10, 2021, 14:34, 45 days ago
Message: Hello Rahul,

probably a typo when you've copied on the site but your format is not correct: there is a missing end quote in
  to_char ('01/01/2020 01:10:00','MM/DD/YYYY HH24:MI:ss)

Anyway, if it works in one DB engine and not in another, well, this can have various origins... Does TO_CHAR( 'a_string', 'MM/DD/YYYY HH24:MI:SS' ) is a valid syntax in Postgres? (I don't know)? If no, no surprise... (check especially if "HH24" is a valid format element)

Maybe a trick: if it is possible to define a default data format in Postgres like it is in Oracle (SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS';), you might try this option

Other remarks:
 - the title of your post is about right join, so I guess that record_date is part of "CC_Station", but I strongly encourage you to use the alias as prefix for each column that you mention... I copy your statement but put "c." where I don't know if it should be "a." or "b."
 - note a small change in the where clause: do you really want to include both extremities of the interval? It is common to rather use [ mydate [ , i.e. mydate >= mini and mydate < maxi
 - "from a right join b" is ok but I prefer to write things as "from b left outer join a"
 - and BTW, are there rows in CC_Station with dates in the specified interval? Zero rows in result set is perhaps exactly what is requested...
SELECT b.station_id

, SUM( CASE WHEN a.validation_type = 0 AND c.pass_id = 60 THEN 1 ELSE 0 END ) T60
, SUM( CASE WHEN a.validation_type = 0 AND c.pass_id = 15 THEN 1 ELSE 0 END ) T15
, SUM( CASE WHEN a.validation_type = 0 AND c.pass_id = 21 THEN 1 ELSE 0 END ) T45
FROM "Pass_Validation" a
RIGHT JOIN "CC_Station" b
ON a.validation_station_id = b.station_id
WHERE c.record_date >= TO_DATE( '20210501011000', 'YYYYMMDDHH24MISS' )
AND c.record_date < TO_DATE( '20210505011000', 'YYYYMMDDHH24MISS' )
GROUP BY b.station_id
ORDER BY b.station_id
;
HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Rahul Singh, India
Date: May 11, 2021, 07:58, 44 days ago
Message: Hi Bruno,

1. Record_date is part of "Pass_Validation" table
2. "Pass_Validation" table is empty
3. " CC_Station" is to capture "Station_id" which is haing values from 1 to 12.
4. Interval of date i need to put as the query will run daily basis.
5. "from b left outer join a" this condition still not works
6. validation_type ,pass_id,validation_station_id are the column of "Pass_Validation" table which is currently empty

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Bruno Vroman, Belgium
Date: May 11, 2021, 16:52, 44 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Rahul,

-a- you don't have to tell me which column is part of which table, I advised to tell it in the statement via aliases.
-b- writing with "right" or "left" gives same result: in a sense this is a relief ;-) these are equivalent was of writing a statement; I just wanted to stress the point that I "imagin" the statement as
 give me all the rows of a table, and if there is a match on other table, fine, but if there is no match it is not critical
although when I see the statement with right join it gives me the strange feeling that we ask
 give me all the matching rows of t1 with t2, and in addition, give me aslo the rows of t2 that do not match.
-c- an empty Pass_Validation table doesn't prevent the statement from retrieving data, I don't understand your "does not work"...
-d- here is a test case built from what I understood (BTW, you might provide the test case yourself in the future, to explain what you get and what you expect to get)
CREATE TABLE "Pass_Validation"( validation_type NUMBER, pass_id NUMBER, validation_station_id NUMBER );

CREATE TABLE "CC_Station"( station_id NUMBER, record_date DATE );
INSERT INTO "CC_Station" VALUES ( 1, DATE '2010-11-12' );
INSERT INTO "CC_Station" VALUES ( 1, DATE '2021-05-02' );
INSERT INTO "CC_Station" VALUES ( 2, DATE '2021-05-03' );
INSERT INTO "CC_Station" VALUES ( 2, DATE '2021-05-04' );

SELECT b.station_id
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 60 THEN 1 ELSE 0 END ) T60
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 15 THEN 1 ELSE 0 END ) T15
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 21 THEN 1 ELSE 0 END ) T45
FROM "Pass_Validation" a
RIGHT JOIN "CC_Station" b
ON a.validation_station_id = b.station_id
WHERE b.record_date >= TO_DATE( '20210501011000', 'YYYYMMDDHH24MISS' )
AND b.record_date < TO_DATE( '20210505011000', 'YYYYMMDDHH24MISS' )
GROUP BY b.station_id
ORDER BY b.station_id;
as well as
SELECT b.station_id

, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 60 THEN 1 ELSE 0 END ) T60
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 15 THEN 1 ELSE 0 END ) T15
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 21 THEN 1 ELSE 0 END ) T45
FROM "CC_Station" b
LEFT OUTER JOIN "Pass_Validation" a
ON a.validation_station_id = b.station_id
WHERE b.record_date >= TO_DATE( '20210501011000', 'YYYYMMDDHH24MISS' )
AND b.record_date < TO_DATE( '20210505011000', 'YYYYMMDDHH24MISS' )
GROUP BY b.station_id
ORDER BY b.station_id;
give me the same result:
STATION_ID        T60        T15        T45

---------- ---------- ---------- ----------
1 0 0 0
2 0 0 0
Can you elaborate on "not works"?

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Rahul Singh, India
Date: May 12, 2021, 10:18, 43 days ago
Message: Hi bruno,

Thanks for the response, however still it not works

As i saw in your table "CC_Station" you have created Date a column Record_date but actually it is not part of this table.

Pls refer below description of my tables for your reference:


Description of pass_validation :

Column Name :

pass_id bigint
record_date timestamp without time zone
validation_type bigint


Description of CC_Station :

station_id integer



SELECT b.station_id,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=60 THEN 1 ELSE 0 END) T60,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=15 THEN 1 ELSE 0 END) T15,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=63 THEN 1 ELSE 0 END) UT,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=21 THEN 1 ELSE 0 END) T45
from "Pass_Validation" a right join "CC_Station" b
on a.validation_station_id=b.station_id
where a.record_date >= to_date('20210501011000','YYYYMMDDHH24MISS') and a.record_date <= to_date('20210505011000','YYYYMMDDHH24MISS')
group by b.station_id
order by b.station_id

Station_id | t60 | t15 | ut | t45
No output


-- Output came after commenting record_date

SELECT b.station_id,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=60 THEN 1 ELSE 0 END) T60,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=15 THEN 1 ELSE 0 END) T15,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=63 THEN 1 ELSE 0 END) UT,
Sum(CASE WHEN a.validation_type=0 and a.pass_id=21 THEN 1 ELSE 0 END) T45
from "Pass_Validation" a right join "CC_Station" b
on a.validation_station_id=b.station_id
-- where a.record_date >= to_date('20210501011000','YYYYMMDDHH24MISS') and a.record_date <= to_date('20210505011000','YYYYMMDDHH24MISS')
group by b.station_id
order by b.station_id


Station_id | t60 | t15 | ut | t45

1 0 0 0 0
2 0 0 0 0
3 0 0 0 0
4 0 0 0 0
5 0 0 0 0
6 0 0 0 0
7 0 0 0 0
8 0 0 0 0
9 0 0 0 0
10 0 0 0 0
11 0 0 0 0
12 0 0 0 0

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Bruno Vroman, Belgium
Date: May 12, 2021, 15:01, 43 days ago
Message: Hello Rahul,

here we are! As you see, prefixing column names with aliases is useful...

When you state "WHERE a.record_date >= ...", what do you expect as result if there are NO rows in table a? There can be no row in result set... Your "outer join" becomes de facto an "inner join". So you get what you have asked in fact.

To have what you want, the conditions on dates should be in the "join on" clause or should handle "NULL is OK" in the where clause. I copy one more time (the last one!) a test case, I strongly (very strongly) encourage you to provide thsi kind of things when asking questions if you expect to receive answers)
CREATE TABLE "Pass_Validation"( validation_type NUMBER, pass_id NUMBER, validation_station_id NUMBER, record_date DATE );

CREATE TABLE "CC_Station"( station_id NUMBER );
INSERT INTO "CC_Station" VALUES ( 1 );
INSERT INTO "CC_Station" VALUES ( 2 );
INSERT INTO "CC_Station" VALUES ( 3 );

SELECT b.station_id
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 60 THEN 1 ELSE 0 END ) T60
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 15 THEN 1 ELSE 0 END ) T15
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 21 THEN 1 ELSE 0 END ) T45
FROM "Pass_Validation" a
RIGHT JOIN "CC_Station" b
ON a.validation_station_id = b.station_id
AND a.record_date >= TO_DATE( '20210501011000', 'YYYYMMDDHH24MISS' )
AND a.record_date < TO_DATE( '20210505011000', 'YYYYMMDDHH24MISS' )
GROUP BY b.station_id
ORDER BY b.station_id;
or, as once again I prefer to write "give me all the rows of CC_Station and, if there is a match, the corresponding rows of Pass_Validation:
SELECT b.station_id

, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 60 THEN 1 ELSE 0 END ) T60
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 15 THEN 1 ELSE 0 END ) T15
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 21 THEN 1 ELSE 0 END ) T45
FROM "CC_Station" b
LEFT OUTER JOIN "Pass_Validation" a
ON a.validation_station_id = b.station_id
AND a.record_date >= TO_DATE( '20210501011000', 'YYYYMMDDHH24MISS' )
AND a.record_date < TO_DATE( '20210505011000', 'YYYYMMDDHH24MISS' )
GROUP BY b.station_id
ORDER BY b.station_id;
The other way (inside the WHERE clause but taking care of NULLs) might be:
SELECT b.station_id

, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 60 THEN 1 ELSE 0 END ) T60
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 15 THEN 1 ELSE 0 END ) T15
, SUM( CASE WHEN a.validation_type = 0 AND a.pass_id = 21 THEN 1 ELSE 0 END ) T45
FROM "CC_Station" b
LEFT OUTER JOIN "Pass_Validation" a
ON a.validation_station_id = b.station_id
WHERE NVL( a.record_date, DATE '2100-12-31' ) >= TO_DATE( '20210501011000', 'YYYYMMDDHH24MISS' )
AND NVL( a.record_date, DATE '1900-01-01' ) < TO_DATE( '20210505011000', 'YYYYMMDDHH24MISS' )
GROUP BY b.station_id
ORDER BY b.station_id;
or something similar, but you see it is really not nice to use pseudo values like 31-DEC-2100 or 01-JAN-1900... Much better to include the conditions in the JOIN ON clause!

HTH,

Bruno.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Right Join is not working properly
Author: Rahul Singh, India
Date: May 13, 2021, 10:35, 42 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Thanks Bruno its works as expected now.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here