Using a Where _ in _ Statement

Using a WHERE ___ IN ___ statement

You need to create enough parameters to match your list of vars:

statement = "SELECT * FROM tab WHERE obj IN ({0})".format(', '.join(['?'] * len(list_of_vars)))
c.execute(statement, list_of_vars)

Note that you pass in list_of_vars as the parameter values list. Using the ', '.join() we generate a string of ? characters separated by commas, then use .format() to insert that into the statement.

For a long list of variables, it may be more efficient to use a temporary table to hold those values, then use a JOIN against the temporary table rather than an IN clause with bind parameters.

SQL use CASE statement in WHERE IN clause

No you can't use case and in like this. But you can do

SELECT * FROM Product P    
WHERE @Status='published' and P.Status IN (1,3)
or @Status='standby' and P.Status IN  (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

BTW you can reduce that to

SELECT * FROM Product P    
WHERE @Status='standby' and P.Status IN (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

since or P.Status IN (1,3) gives you also all records of @Status='published' and P.Status IN (1,3)

The reason we use using statement in the following code?

1) The reason for the using statement is to ensure that the myEntities.Dispose() method is called as soon as the code inside the using statement completes. This is necessary since the PlanetWroxEntities class' base class is a DbContext which holds a connection to the database.

Database connections are a precious resource that are obtained from the database connection pool and there are only a finite number of connections available that your whole application must share. If you don't call Dispose on the myEntities object as soon as you are done with it via the using statement (or some other manner) then the myEntities object will continue to tie up that database connection for some undetermined amount of time until the garbage collection code from the .net memory manager gets around to reclaiming the memory that is held by the myEntities object that is no longer in use.

The memory manager will at that time calls the dispose method. As soon as the Dispose method is called the database connection is released from the object and returned to the database connection pool where it can be obtained by other parts of your application when you make a call to get a database connection (for example when you create other instances of the PlanetWroxEntities class.)

The purpose of the using statement is to have the Dispose method called as soon as you are done with the object so that in this case the database connection is returned to the pool right away.

2) It means the database connection is released by your application and is returned to the database connection pool where it is now available to be obtained by other parts of your application. Here is one resource that talks more about connection pooling, it will give you more background on what is going on under the hood: https://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Under the hood the using statement is implemented as a finally statement. So this code:

using (PlanetWroxEntities myEntities = new PlanetWroxEntities())    
{
    var authorizedReviews = from review in myEntities.Reviews    
                            where review.Authorized == true    
                            orderby review.CreateDateTime descending    
                            select review;

    GridView1.DataSource = authorizedReviews.ToList();

    GridView1.DataBind();    
} 

becomes

PlanetWroxEntities myEntities; 
try{
    myEntities = new PlanetWroxEntities();
    var authorizedReviews = from review in myEntities.Reviews    
                            where review.Authorized == true    
                            orderby review.CreateDateTime descending    
                            select review;

    GridView1.DataSource = authorizedReviews.ToList();

    GridView1.DataBind();   

} finally{
    myEntities.Dispose();
}

In the old days of C# we always had to write the finally statement ourselves to dispose of disposable objects so that their unmanaged resources were released. But later the using statement was introduced in C# to make it easier to accomplish this. Even today, you can write the finally statement yourself if you prefer, but most people love the using statement.

For more information see https://msdn.microsoft.com/en-us/library/yh598w02.aspx

Using With Statement inside function in BigQuery

Try below:

1. You have a typo : RETURN -> RETURNS
2. Add one more parenthesis pair around a select statement. It will make a statement as an expression.
3. Be sure not to return one more rows or have one more columns in your select query. Just return single INT64 value same as a return type.

Hope this is helpful.

CREATE TEMP FUNCTION MyFunc(my_var INT)
RETURNS INT64 AS 
((
WITH ABC AS (select * from t where t.col = var),
DEF AS (select * from t where t1.col = var),
GHI AS (select * from t where t2.col = var)

SELECT * FROM ABC JOIN DEF USING (...) JOIN GHI USING (...)
 
));

SELECT MY_FUNC(5);

c# using statement follow by try statement can the bracket be ombitted in that case?

If you look at the grammar of the using statement in the C# Specification, you see that using statements are followed by (or rather, their body consists of) "embedded_statements".

using_statement
    : 'using' '(' resource_acquisition ')' embedded_statement
    ;

Embedded statements are defined as follows:

embedded_statement
    : block
    | empty_statement
    | expression_statement
    | selection_statement
    | iteration_statement
    | jump_statement
    | try_statement
    | checked_statement
    | unchecked_statement
    | lock_statement
    | using_statement
    | yield_statement
    | embedded_statement_unsafe
    ;

So yes, this is not a typo. After using (...), there can be any of the statements that are defined in embedded_statement. And anyway, to see whether this is a typo, you could have just simply tried to compile the example code.

How do I use the WITH statement in Access?

As others have stated in the comments, Access SQL does not support the with keyword the same way that TSQL does. You can accomplish close to the same thing though by writing the first query and saving it. The saved query can then be referenced in your Access SQL as though it was a table (similar to creating a view in TSQL).

Others also noted that VBA can use the with keyword, but for a different purpose.

how to combine a WITH statement and an INSERT INTO in SQL

For Oracle, you want the syntax:

insert into temp (quarter)
with bla (year) as (
  select 2021 as year from dual
)
select 10*year + 1 as quarter from bla;

And your CREATE TABLE statement should be:

create table temp (quarter decimal(5));

db<>fiddle here

Using statement doesn't work correctly

The problem might be that you are running the code in the debugger. That means that whatever code comes after an unhandled exception wont be executed becuase the debugger will halt at that point.

If you run a test scenario out of the debugger then the Dispose() method will be called when the runtime cleans up the mess. Put some code "marker" in the Dispose() method that will give you a visible sign that Dipose is called: for example create a file in some accessible folder namesd DisposedHasRun.yeah (note that outputting to the Console or a MessageBox is not an option because the application is quitting due to an unhandled exception). When you run the test case outside the debugger you'll see that the file has been created.

That said, there are ocassions where Dispose wont be called (nor finally blocks). For example an StackOverflow exception due to an infinite recursion will not call Dispose() as the runtime will make the application bail out asap. (ThreadAbortException I think will behave the same)

---

Related Topics