Select Serial Number in a SQL query

I was recently asked this question about listing records from a database and even though this is unrelated with most of this blog, I thought it has earned its place as part of this blog.

The question was:

How do I select records from a database table and display the results as a list in the UI (which uses a SQL query to access the DB)

Consider a simple 'Grocery Shopping' database from which we want to display information in the UI/web page based on selected Shopper as follows:

Shopper1 bought

1. Eggs -$3

2. Bread - $2.5

3. Cream -$6

OR

Shopper2 bought

1. Cheese -$5

2. Eggs -$3

3. Ham -$9

4. Bread -$2.5

We have two tables:

To create the above result, using either Oracle or SQL server, you can use the following query:

SELECT
l.Shopper_name,
row_number() over (order by i.item#) as srno,
i.item_name,
i.price
FROM
Shopping_list as l, Inventory as i
WHERE l.shopper_name :=selected_shopper --Prompted value through SQL
GROUP BY l.shopper_name,i.item_name,i.price
ORDER BY l.shopper_name




After getting the results as a list, you can use your code to format it appropriately in the UI.



Another use for this type of query could be to see the number of times a given item appeared in the 'Shopping_List' table Eg: List the Shoppers who bought Eggs with Serial Numbers attached.



Anisha

ORA-12154, ORA-12560 - Cannot Connect to the Oracle Database

These are some common errors for new installations. The solution pretty simple.
Errors like:
ORA-12154, ora-12560

Listed below are a few of the problems we faced...with a few things to check.

1. SQL+ does not connect to the DB - Check to see that the fire wall on the DB server is not blocking the port (usually 1521) - You can either turn off the firewall or get the system admins to open up the database port.
Also make sure that the listener is up and running.

2. When creating the ODBC connection, we could not test the database connection. - Again make sure the DB listener is turned on and the TNSfiles are copied over from the DB server to the server you are trying to connect from. Copy the files to the location similar to : \Network\Admin\

3. Sometimes you can simply re-install the DB client (Its easier than debugging)

Google is extremely helpful with the ORA-XXXX errors that the DB Connection might throw.

Hope this helps.

Anisha