0

i have two tables with the schema below:

INCOME TABLE:
INCOME_ID, INCOME_NAME, INCOME_DATE

OUTCOME TABLE:
OUTCOME_ID, OUTCOME_NAME, OUTCOME_DATE

EXAMPLE DATA INCOME
INCOME_ID | INCOME_NAME | INCOME_DATE
1         | FIRST EXMPL | 24-07-2016
2         | SEC EXAMPLE | 25-07-2016
ETC
EXAMPLE DATA OUTCOME
OUTCOME_ID | OUTCOME_NAME | OUTCOME_DATE
1          | FIRST        | 24-07-2016
2          | SEC          | 03-08-2016
ETC

so i use union all to combine these table

SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME FROM INCOME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME NAME FROM OUTCOME

and i use query above to create view

CREATE VIEW V_BALANCE AS SELECT INCOME_DATE AS DATE, INCOME_NAME AS NAME
UNION ALL
SELECT OUTCOME_DATE, OUTCOME_NAME FROM OUTCOME

the view was succesfully created but when i want to add where clause in view, it returns empty row

SELECT * FROM V_BALANCE WHERE DATE BETWEEN = '24-07-2016' AND '03-08-2016'
2
  • 1
    What's the datatype of INCOME_DATE and OUTCOME_DATE? Jul 29, 2016 at 9:36
  • The string '24-07-2016' is > '03-08-2016'. Those are invalid dates.
    – Rick James
    Jul 29, 2016 at 20:41

2 Answers 2

2

Assuming that the two date columns are indeed DATE columns, the problem is that you are not using proper format in the date literals. Use ANSI format:

SELECT * 
FROM V_BALANCE 
WHERE DATE BETWEEN '2016-07-24' 
               AND '2016-08-03' ;

See the documentation for other options: Date and time literals.


If the two date columns are not DATE but some CHAR or VARCHAR, then the issue is there. Convert the columns to the proper date type, otherwise the comparisons will work incorrectly.

1

You have a typo in your code:

You wrote

  • SELECT * FROM V_BALANCE WHERE DATE BETWEEN = '24-07-2016' AND '03-08-2016'

whereas you should have written

  • SELECT * FROM V_BALANCE WHERE DATE BETWEEN '24-07-2016' AND '03-08-2016'

I know that that this sort of thing is very difficult to spot when you're reading back through code. I didn't see it either - I had to create the views and actually run your command before my client showed up the error.

Your client tool should have given you a warning about something this obvious.

Suggestion - maybe you should look at changing client tool? p.s. welcome to the forum.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.