Nested SQL(Nested SQL(Nested SQL(Nested SQL)))

Can you give a whole SQL statement an alias so you can use it later?

Eg.

SELECT * FROM Employees WHERE age < 19
-- Could I call the above statement something like 'statement1' to use below as shown

SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)

Soin effect I get a nested statement.
The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.

So basically, I just want to alias a qhole SQL statement

Any help would be greatly appreciated - Georgethe concept you are thinking of is called a view

so, the answer is yes, you can|||Could you elaborate slightly please?
Could you perhaps include an example of hwo to reference/alias/view an sql statement.

Thanks mate|||Something like this?CREATE VIEW stat_1 AS
SELECT * FROM employees WHERE age < 19;

CREATE VIEW stat_2 AS
SELECT * FROM employees
WHERE age < 25
AND employee_id NOT IN (SELECT employee_id FROM stat_1);|||I'm using a program called QUERY ANALYZER in which I am the following (and just to confuse I am now trying qualifications..

CREATE VIEW stat_1 AS
SELECT e.employee_number
FROM pwa_master.employee e, pwa_master.trgqual t
WHERE t.qualification_level = 'ALEV'

CREATE VIEW stat_2 AS
SELECT e.employee_number, t.qualification_level
FROM pwa_master.employee e, pwa_master.trgqual t
WHERE t.qualification_level = 'GCSE'
AND e.employee_number NOT IN (SELECT e.employee_number FROM stat_1)

From which I get the following message:

Server: Msg 2714, Level 16, State 5, Procedure stat_1, Line 2
There is already an object named 'stat_1' in the database

I don't know what I have done, but it doesn't look good.

Normally in this program if I do a select it only displays the results, which is all I want to do, not create anything in the database! :S :o :confused: :shocked:|||if you run CREATE VIEW stat_1 more than once, guess what happens on the second attempt?

a view is simple a query definition -- it's exactly what you want

yes, the query definition gets stored in your database

if you don't want to store the view definition in your database, you cannot use a view|||I dont want to store anything in the database, just use SQL to extract the data I need.

So are there any other methods I can use instead of a view?

P.S. When a view is created, where in the database is it stored?|||I dont want to store anything in the database, just use SQL to extract the data I need.you can always do that

So are there any other methods I can use instead of a view?depends on what you are really trying to do, which isn't really clear

P.S. When a view is created, where in the database is it stored?in the system tables|||Ok, I'll try and clarify.

Employees table is linked to a table called Training and Qualifications.
I have been asked to produce an SQL statement that selects an employees' highest qualification. Unfortunately I cannot do this with things such as child functions or even entry date as this will be innacurate.

Unfortunately there is no ranking available in the database to signify which qualification is highest E.g. GCSE, ALevels, OLevels, Masters etc etc.

So I thought that if I could say, have one SQL statement for each and gave them each an alias I could do the following:

SELECT employee_number
FROM training_and_qualifications
WHERE Qualification_Level = 'GCSE'
AND Qualification_Level NOT IN [SQL Statement 2]

the previous one would be of a similar format again, but GCSE could be replaced with 'ALEV' and used NOT IN [SQL Statement 3].

Is that any better?
If not - I'm sorry :o|||you should have posted your original requirements first, instead of asking how to accomplish something really complicated

"I have been asked to produce an SQL statement that selects an employees' highest qualification"

please give information about the ranking of these qualifications|||That's the problem, there is no ranking.

I would have to do it logically which is why I was building this sql statement.
I would take the highest qualification (Masters I think) and that would be my very first SQL statement.

Then I would perform another one for, say, ALevels and use "NOT IN MastersSQLStatement"

Followed by GCSE (?) and use "NOT IN MastersSQLStatement OR NOT IN ALevelsSQLStatement"

Sorry for complicating the issue.|||if there is no ranking, then you could never pick the highest one, could you

so of course there's a ranking

you said yourself that Masters is the highest, but i bet Doctors is even higher, and Bachelors is next highest after Masters, and so on

if an employee has several qualifications, how are they stored?

you need to give information about your table structure|||There is no ranking stored in the database, I am producing the order myself.
I wouldn't have a problem if in the database each qualification has a rank number, but I am unable to amend the tables to do such a thing.

training_and_qualifications table is linked to the employees table by the employees unique_identifier.

In the training_and_qualifications table each row has its own unique_identifier.

therefore one employee can have many qualifications, linked by the employee unique ID|||There is no ranking stored in the database, I am producing the order myself.i do realize there is no ranking stored in the database, however, if you don't share this information with me, i cannot help you

what are the rankings?|||This discussion reminds me of something that happened ages ago when I was a teenager and worked in a service station. One morning I showed up to open up, and found a car parked on the driveway, no keys, with a note on the windshield that said "something is wrong".

Looking at the car, my first thought was "Obviously" which was quickly followed by "What is this thing doing squarely in the middle of my driveway?" although the phrasing was a bit different. ;)

I'm sure that we can help you. I'm comfortable that your database design needs some small changes, specifically you need a table that includes all of the qualifications you use with a ranking for each of them.

As we know that this uses Microsoft SQL Server, and is almost certainly using SQL 2000 instead of SQL 2005, that helps us too. In order to get more relevant comments, I'm going to move the whole thread to the Microsoft SQL Server forum where more people will find it and contribute to helping solve your problem(s).

At the top of every page in the Microsoft SQL forum is a FAQ. In the FAQ is a post describing how to get fast and correct answers to your questions. Please read that post, the ideas there will help you get a faster and less frustrating answer for your question!

-PatP|||for the sake of a quick response (there are 77 different qualifications in the code table). Here is a condensed list or 5 in order.

Highest

MAST (Masters)
ALEV (A Level)
ASLEV (AS Level)
GCSE (GCSE)
CG (City and Guilds)

Lowest

Hope it helps|||Ok, so create a list of the qualifications (if you show your table layouts, I can provide code that will help you with this), and then assign each one a number. I'd start numbering at 100000 and bump the number by 1000 for each higher qualification. That gives you lots of room in case you need to rearrange things later.

Once you create this table, then things are relatively easy, but we'll take things one step at a time for now.

-PatP|||Thanks Pat.

The database contains a table called "Qualifications_Code_List" which has 77 rows, each one has a unique "Qualification_Code" and "Description".

I know the simplest way would be to add a third column in which I could assign a numeric value for rank. BUT I am supposed to be doing this without changing the database, hence this workaround.

tomake a change to the tables would require exclusive access, i.e. booting everyone off the system. At the end of the month we are taking the system offline for 6hours, so if all else fails I can impliment this then along with lots the other changes.

I'm just being nagged to give these people an answer sooner than that :p

Oh, and I realise my original post was.. well.. rubbish, so I apologise.
I will read up on the FAQ and hopefull next time I will be better.

thanks for all the replies everyone :beer: cheers!|||Ok, so create a list of the qualifications (if you show your table layouts, I can provide code that will help you with this), and then assign each one a number. I'd start numbering at 100000 and bump the number by 1000 for each higher qualification. That gives you lots of room in case you need to rearrange things later.

Once you create this table, then things are relatively easy, but we'll take things one step at a time for now.

-PatP

Very good idea using 1000's instead, I wouldn't have thought of that, thanks!|||I'm just being nagged to give these people an answer sooner than that :ptell them you cannot do it until they provide you with a list in sorted order

how the heck are you supposed to run a query if you're not sure about the ranking values?

there's no way i would guess at how to find the "highest" value from amongst 77 different values

and i would have no problem telling the users that they aren't going to get what they want until they are more forthcoming|||Check out ALTER TABLE, it requires the use of SQL syntax instead of the GUI, but it allows you to add a column in real time without your users being aware that anything happened.

The next time they do a SELECT * FROM yourTable there will suddenly be a new column. Note that you should try this in a dev/test environment before you rip into production, but ALTER TABLE will have no adverse affect on online users (other than adding the column to the table).

-PatP|||IF I eventually get this with rankings in the database table, I just sort by rank or whatever...

IF I cannot amend the table but I still have an ordered list, i.e. I know the order, can I use sql to create it?

I know it will be a huge pain in the a** but still :p

Sorry eveyone :o|||SELECT employees.unique_identifier
, case when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'MAST' )
then 'Masters'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ALEV' )
then 'A Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ASLEV' )
then 'AS Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'GCSE' )
then 'GCSE'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'CG' )
then 'City and Guilds'
else '*none*'
end as highest_qualification
FROM employeesthe reason this works is because a CASE expression is evaluated linearly, so the first WHEN clause that evaluates true will determine the result, which in this instance is a column called "highest_qualification"

simply create as many WHEN clauses as you have qualifications

simple, yes?|||Check out ALTER TABLE, it requires the use of SQL syntax instead of the GUI, but it allows you to add a column in real time without your users being aware that anything happened.

The next time they do a SELECT * FROM yourTable there will suddenly be a new column. Note that you should try this in a dev/test environment before you rip into production, but ALTER TABLE will have no adverse affect on online users (other than adding the column to the table).

-PatP

Sadly I have tried this in the past in our test environment, and through the query analyzer you can populate the row, but it remains unrecognised in the system (complete pain in the a**). This means when I create a query within the system for the users.. use.. I cannot select that row because it just simply cannot be seen!

We use a system called Empower which is an ex microsoft product.|||There are ten thousand solutions for almost any problem. About nine thousand, nine hundred and ninety four of them are impractical.

You could always take the "outsourcing approach" and ship all of the data overseas, have someone read your data and produce a sorted list, then you can describe the selection criteria (for order of qualifications) and tell them to discard everything except for the highest qualification. You can probably do this in less than a year, and it will probably take a few days for each run of the list, but it will be reasonably priced when it does work!

-PatP|||You can't just edit a result set in Query Analyzer. That tool is really for developers, not for users, so there is no "push" technology in it... Any changes you make are local to your own machine, they are never returned to the database.

If you use another tool to access the table such as Microsoft Access, then you can make the kind of changes you are envisioning. You can also write SQL statements to UPDATE the database, which is a bit of a pain, but is often my tool of preference.

-PatP|||SELECT employees.unique_identifier
, case when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'MAST' )
then 'Masters'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ALEV' )
then 'A Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'ASLEV' )
then 'AS Level'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'GCSE' )
then 'GCSE'
when exists
( select 1
from training_and_qualifications
where employee_unique_identifier
= employees.unique_identifier
and Qualification_Level = 'CG' )
then 'City and Guilds'
else '*none*'
end as highest_qualification
FROM employeesthe reason this works is because a CASE expression is evaluated linearly, so the first WHEN clause that evaluates true will determine the result, which in this instance is a column called "highest_qualification"

simply create as many WHEN clauses as you have qualifications

simple, yes?

THIS WORKS!
Now to implemment the other 72 select statements :P
Atleast I can now tell them that it, atleast in theory, is possible without editing the database.

I love you ;)

Thank you everyone for all your help (and for putting up with me).|||You can't just edit a result set in Query Analyzer. That tool is really for developers, not for users, so there is no "push" technology in it... Any changes you make are local to your own machine, they are never returned to the database.

If you use another tool to access the table such as Microsoft Access, then you can make the kind of changes you are envisioning. You can also write SQL statements to UPDATE the database, which is a bit of a pain, but is often my tool of preference.

-PatP

They only really want a spreadsheet at the end of it all, so I will just run it and paste it int a spreadheet and voila! (ish) :p

cheers Pat|||Actually once you get the SQL worked out, Excel 2000 includes a nifty feature under Data | Get External Data | Database Query that makes this process a lot easier (and is repeatable too).

-PatP|||Can you give a whole SQL statement an alias so you can use it later?
This is not necessarily a VIEW: views are stored in the database, while you might just want to use the "alias" for the current query only.

In that case a common table expression is more appropriate.
CTEs precede an SQL Select statemant in a WITH subclause.

Example:WITH stat_1 AS
( SELECT e.employee_number AS n1
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'ALEV'
),
stat_2 AS
( SELECT e.employee_number AS n2
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'GCSE'
)
SELECT stat_1.unique_identifier,
COALESCE(n1, n2)
FROM stat_1 FULL OUTER JOIN stat_2 ON n1 = n2|||if you don't have any other method, you can always rank your items this way:

select * from yourtable order by newid()

;)

EDIT: tell your boss that you are using Bayesian classifiers to determine the ranking.|||This is not necessarily a VIEW: views are stored in the database, while you might just want to use the "alias" for the current query only.

In that case a common table expression is more appropriate.
CTEs precede an SQL Select statemant in a WITH subclause.

Example:WITH stat_1 AS
( SELECT e.employee_number AS n1
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'ALEV'
),
stat_2 AS
( SELECT e.employee_number AS n2
FROM pwa_master.employee AS e, pwa_master.trgqual AS t
WHERE t.qualification_level = 'GCSE'
)
SELECT stat_1.unique_identifier,
COALESCE(n1, n2)
FROM stat_1 FULL OUTER JOIN stat_2 ON n1 = n2

I ran this and got the following:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.

I understand what the code is supposed to do - but I don't know what the syntax should be!|||you have to wait until SQL Server 2005 to use CTEs|||I have SQL Server 2005 installed on this machine - but I have nno idea how to use it. :p

But thanks for the reply mate - it's all realllllly useful!
Even if it's not something I can use now this is all great to learn!|||There one other thing you can do, provided that you know the order of the ranking.
--Create a temp-table:
CREATE TABLE #tmprank (rank INT IDENTITY, desc VARCHAR(10))

--Fill the temp-table with the ranking in the right order:
INSERT #tmprank (desc) VALUES ('MAST')
INSERT #tmprank (desc) VALUES ('ALEV')
INSERT #tmprank (desc) VALUES ('ASLEV')
INSERT #tmprank (desc) VALUES ('GCSE')
INSERT #tmprank (desc) VALUES ('CG')

--Join your query to this temp-table and order by rank:
SELECT employees.unique_identifier, t.rank, t.desc
FROM employees e
INNER JOIN #tmprank t ON t.desc = e.Qualification_Level
ORDER BY t.rank

--Drop the temp-table (closing the session will also do this)
DROP TABLE #tmprank
This is a non-intrusive way that leaves no traces (does not alter the structure of your db).