Add new variable in where clause with cross apply

Question

Created a new date variable in select statement, i need to add this new variable to the where clause as below;

         SELECT a.bim, a.cim, b.Block,
            (datename(dw, a.start_date)+ ', ' + convert  (varchar(20),a.start_date,107) ) as mmm
          FROM CK.SCHEDULE a
         LEFT JOIN ck.Lookup b on b.date1=mmm
                WHERE bim='2024'

This 'mmm' variable need to be equal 'date1' Tried cross apply but didnt work in this one.

date1 looks: Monday, July 03, 2023

  start_Date looks: 2018-07-02 00:00:00.0000000

Plus is an arithmetic operator on mysql - is this question tagged correctly? — P.Salmon, Jul 03 '23 at 15:53
no we need to understand the structure of the tables, so we can help you — nbk, Jul 03 '23 at 16:48
Your SQL is not MySql and you have used a t-sql specific tag, you have not tagged your question correctly. — Stu, Jul 03 '23 at 18:23

score 0 · Answer 1 · answered Jul 03 '23 at 16:01

0

mmm is out of the scope of the WHERE clause. However, if you define it in a subquery then it would be in scope of the WHERE clause.

For example:

create table t (a int, b int);

insert into t (a, b) values (1, 2), (3, 4), (5, 6)

select *
from (
  select t.*, a + b as c from t
) x
where c > 5 -- c is in scope in the external WHERE clause

See fiddle.

answered Jul 03 '23 at 16:01

The Impaler

45,731
9
39
76

Is there anyway to make them equal in where clause without creating all these. The rest is huge complicated code, so it will mess up. maybe in where clause make those 2 date values equal? – user22168985 Jul 03 '23 at 16:08
Another option is to define a view on the table that computes that extra value. Then the query could use the view instead. – The Impaler Jul 03 '23 at 16:11

Add new variable in where clause with cross apply

1 Answers1