16
Views
6
Comments
Solved
Error in advanced sql query syntax
Question

I am writing a sub query using with clause.

when i am trying to use that temporary table data in main query it is giving me error in clause.

i have attached image of that query.

sqlError.PNG

Rank: #64
Solution

Hi Nilam,


your last AND is not correct SQL (yellow part on the screenshot below):


If you replace it by:

AND
(
        EXISTS (SELECT p_inverntoryType.[LobId] FROM p_inverntoryType where p_inverntoryType.[LobId] like 'ALL') OR (A.[LobId] In (SELECT p_inverntoryType.[LobId] FROM p_inverntoryType))
    )

it will work.


Looking to your query, I can also add that:

  • using subqueries on SELECT, is not good for performance;
  • using aliases on query is error-prone and not good for maintainability of code.


Hope it helps.

Cheers,

João

Rank: #2386

Thanks Joao,


It works , but i need one more help

there is extension in that query's  where clause


 and
   (
        CASE WHEN @p_template = 1 THEN {AssertRegister}.[StatusId]=1
          WHEN @p_template = 2 THEN {AssertRegister}.[StatusId] in (2,3) and {AssertRegister}.[isActive]='true'
     END
    ) 

i tried this but it shows error near to '='.

depending on @p_template i need to check different attributes

Regards,

Nilam Patil

Rank: #64

Hi Nilam,


You need to set a boolean condition in the AND and the CASE just gives you a value.

Try this:

 and
   {AssertRegister}.[StatusId] =
        CASE WHEN @p_template = 1 THEN 1
          WHEN @p_template = 2 THEN 2
        END
    ) 

or even simpler:

 and {AssertRegister}.[StatusId] = @p_template

Let me know how it goes.


Cheers,

João

Rank: #2386

sorry i modified my previous reply plz chk it again.

Rank: #64

Ok, try this then:


and (
    (@p_template = 1 and {AssertRegister}.[StatusId] = 1)
    OR
    (@p_template = 2 and {AssertRegister}.[StatusId] IN (2,3) and {AssertRegister}.[isActive] = 'true')
    ) 
Rank: #2386

It works.

It gives me the proper output. You also replied very quickly to my query.

Thanks a lot.