Urgent SQL Help Needed! Problem with getting a query to work properly.?

Question

Hi Everyone,

I have created the following DB table: -

CREATE TABLE Patient (
patient_id NUMBER(10) NOT NULL PRIMARY KEY,
Patient_name VARCHAR(30) NOT NULL,
address VARCHAR(150) NOT NULL,
phone VARCHAR(25) NOT NULL,
date_of_birth DATE NOT NULL ,
blood_group VARCHAR(2) NOT NULL,
medCon_id NUMBER(10) NOT NULL,
FOREIGN KEY (medCon_id) REFERENCES Medical_Conditions(medCon_id));

I'm trying to run the following query

SELECT
p.NAME AS Patient_name,
p.blood_group AS blood_group
FROM
Patient p,
GP doc,
visit v,
Medical_Conditions cond
WHERE
p.patient_id = v.patient_id
AND doc.Gp_id = v.Gp_id
AND p.medCon_id = cond.medCon_id
AND cond.condition = 'High Blood Pressure'
AND doc.NAME = 'DR S Smith'
ORDER BY
p.NAME ASC,
p.blood_group DESC

I continually get presented with the following error:
p.Name ASC,
*

ERROR at line 16:
ORA-00904: "P"."NAME": invalid identifier

I'm a beginner with SQL any help will be greatly appreciated.
Thanks In Advance

Answer 1

This is Oracle SQL isn't it?



The problem, it seems to me, is that you're selecting p.NAME, but NAME is not a column in the Patient table. Try p.Patient_name in the select and order parts of the statement.



Also it is possible that NAME is a reserved word in Oracle - used by the system. So try changing it.

Answer 2

You have the column name and alias backwards.



Wen you say "select p.name as patient_name" you are saying, bring back column "name" from the table "p" and re-name that column "patient_name."



As a side note, there is no need to use AS if you are not renaming the column in the returned dataset. So, the line "p.blood_group AS p.blood_group" is not necessary, simply say "p.blood_group."

Answer 3

I'm not as much of an expert as others that use this site... but as you've created an alias for p.Name (ie Patient_name), should you use Patient_name ASC as your ORDER BY clause instead of p.NAME ASC?



Worth a try, anyway...

Answer 4

Assuming you do no longer want a Cartesian fabricated from all achievable row mixtures (which works to be genuine ninety 9.999999% of the time), you want to apply inner joins (the two implicitly or explicitly) interior the question. An exhibit connect makes use of the connect...ON clause on an identical time as an implicit one specifies an equality interior the the place clause. to illustrate, assuming that authID is the favourite key in table authors and a distant places key in table e book, the two of those would do: go with in spite of FROM e book b connect author a ON b.authID = a.authID go with in spite of FROM e book b, author a the place b.authID = a.authID

Answer 5

#######################

CREATE TABLE Patient (

patient_id NUMBER(10) NOT NULL PRIMARY KEY,

Patient_name VARCHAR(30) NOT NULL,

#rest of the create function ###

)

####################

SELECT p.NAME AS Patient_name

####################



#NAME is an undefined column#

Answer 6

Try p.Patient_name AS NAME



The alias is the right of "AS" and the actual name from the table has to be the left side.

Answer 7

on query use squre barckets.eg: p.name ==> [p.name]

Answer 8

Try changing it to:



p.Patient_name AS Patient_name



Just a wild guess really.