Sqlite

Sql queries to use Alias in where – alternative

I have been working on SQL queries. Now the scenario is as follows.

what if you wanted to compare the alias in WHERE. By mysql rules, you cannot use Alias in WHERE. However, you can use alias to filter the results using HAVING. The difference between WHERE and HAVING clause is that you can use WHERE in UPDATE or DELETE clause, while HAVING is only used when to filter the results

I have one to many relationships in table (lets say we have items at multiple locations).

Now I have to pull a low stock report where total item quantities at multiple locations is less than threshold. Thus I have to sum the total quantities at multiple locations on fly and compare it with recommended minimum level of quantities.

I am sure there could be other problems to this approach but I have used the following code.

 

   SELECT
item.id as master_item_id,
item.name as item_name,
supplier.name as supplier_name,
item.recommended_lowest_quantity as min_level,
(SELECT SUM(quantity_out) FROM item_outbound WHERE item_outbound.item_id=item.id AND item_outbound.status_id IN  (6, 7 , 12, 20 ,21)) as total_qty,
item_outbound.quantity_out as location_qty,
item_outbound.id as outbound_id,
location.name as location_name,
status.name as status_name
FROM item
LEFT JOIN item_outbound ON item.id=item_outbound.item_id
LEFT JOIN supplier ON supplier.id=item.primary_supplier_id
LEFT JOIN location ON location.id=item_outbound.location_id
LEFT JOIN status ON status.id=item_outbound.status_id
WHERE item.active=1
AND item_outbound.status_id IN (6, 7 , 12, 20 ,21)
HAVING total_qty <min_level
ORDER BY `master_item_id` ASC
Advertisements

Reading a .sql file for sqlite database

Hi

I am developing an andriod app and I have my data in .sql file downloaded via phpmyadmin.

Now there are some format difference in sql and sqlite3 commands. so I have performed the following steps to read the file in the format that is understandable by sqlite3 database

however make sure when you download the sql file from phpmyadmin, it should be MAXDB sql compatiblity mode. I have tested this, however it should also work on ANSI and DB2 compatiblity mode, but I have not used that
Also the settings shown in the image should be there when downloading the sql file

 

Here is the code which I used to read the sql file and populate it in sqlite3 database

private void fillData() {
DbHelper dh = new DbHelper(this);
SQLiteDatabase d = dh.getWritableDatabase();
String line;
String incompleteLine="";

try {
InputStream in = this.getAssets().open(“data.sql”);
if (in != null) {
// prepare the file for reading
InputStreamReader input = new InputStreamReader(in);
BufferedReader buffreader = new BufferedReader(input);

while ((line = buffreader.readLine()) != null) {
// Toast.makeText(this, line,Toast.LENGTH_LONG).show();
// d.execSQL(“INSERT INTO brand (_id,name,info) VALUES (‘2′,’AMICA’,’amica’);”);
// Print the content on the console
if (line.indexOf(“-“)==0 || line.indexOf(“/”)==0 || line.indexOf(“SET”)==0)
{
continue;
}

if (line.contains(“;”))
{
incompleteLine=incompleteLine+line;
System.out.println (incompleteLine);
d.execSQL(incompleteLine);
incompleteLine=””;

}
else
{
incompleteLine=incompleteLine+line;
}

}
in.close();
// Toast.makeText(this, res, Toast.LENGTH_LONG).show();
} else {
}
} catch (Exception e) {
Toast.makeText(this, e.toString() + e.getMessage(), 50000000).show();
}

d.close();

Intent selectBrandIntent = new Intent(this, SelectBrand.class);
startActivity(selectBrandIntent);
}