CODEWITHSUNDEEP
oracle-database
Anthony Butt
Anthony Butt

Reputation: 3

Missing Keyword Error in Oracle - Wrong Syntax WHERE Expression

I'm new to Oracle SQL, I'm being asked to do some scenarios to learn the different expressions and so on.

I'm currently working on this statement but I keep having trouble with syntax and trying to get my expressions in the correct place.

If you don't mind taking a look at what I'm doing wrong and helping me learn the correct syntax I'd appreciate it a lot.

I have to find everything in the Sale, SaleDetail, OrderStatus, Warehouse, User and StockDetail tables.

The fields I need to find are saleno, serialstart, serialend, the product description (label field), sale status (saleid (I think)), WarehouseName (WH.NAME)

Here below is the code I've written so far.

SELECT
S.SALENO,
SD.SERIALSTART,
SD.SERIALEND,
SDT.LABEL,
USR.USERNAME,
WH.NAME
FROM
ITR_SALE,
ITR_SALEDETAIL,
ITR_ORDER,
ITR_WAREHOUSE,
ITR_USER,
ITR_STOCKDETAIL
JOIN ITR_SALE S
JOIN ITR_SALEDETAIL SD ON S.ID = SD.SALENO
JOIN ITR_WAREHOUSE WH ON SD.ID = WH.NAME
JOIN ITR_ORDER ODR ON WH.ID = ODR.STATUSID
JOIN ITR_USER USR ON ODR.ID = USR.USERNAME
JOIN ITR_STOCKDETAIL ON USR.ID = SDT.LABEL
WHERE S.LASTSTATUSCHANGETIME 
BETWEEN ('2016-01-01 00:00:00' AND '2016-12-31 23:59:59')
AND STATUSID = ('COMPLETED');

Below follows the error message

ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:
Error at Line: 21 Column: 1

EDIT:

Finished code below, changed a few expressions and conditions.

SELECT
S.SALENO,
SD.SERIALSTART,
SD.SERIALEND,
SDA.LABEL,
USR.USERNAME,
WH.NAME
FROM 
ITR_SALE S
INNER JOIN
ITR_SALEDETAIL SD ON S.ID = SD.SALEID
INNER JOIN
ITR_ORDERSTATUS ODS ON SD.ID = ODS.ID
INNER JOIN
ITR_WAREHOUSE WH ON ODS.ID = WH.NAME
INNER JOIN
ITR_USER USR ON WH.ID = USR.USERNAME
INNER JOIN
ITR_STOCKDETAIL SDA ON USR.ID = SDA.LABEL
WHERE 'DATE' BETWEEN '2016-01-01' AND '2016-12-31'
AND S.STATUSID = '4';`

Upvotes: 0

Views: 132

Answers (1)

Utsav
Utsav

Reputation: 8093

Use proper join syntax. Edit. Need to remove parenthesis from last line or user IN clause. 

SELECT
S.SALENO,
SD.SERIALSTART,
SD.SERIALEND,
SDT.LABEL,
USR.USERNAME,
WH.NAME
FROM
ITR_SALE S INNER JOIN ITR_SALEDETAIL SD ON S.ID = SD.SALENO
INNER JOIN ITR_SALEDETAIL SD ON S.ID = SD.SALENO
INNER JOIN ITR_WAREHOUSE WH ON SD.ID = WH.NAME
INNER JOIN ITR_ORDER ODR ON WH.ID = ODR.STATUSID
INNER JOIN ITR_USER USR ON ODR.ID = USR.USERNAME
INNER JOIN ITR_STOCKDETAIL STD ON USR.ID = SDT.LABEL
WHERE S.LASTSTATUSCHANGETIME 
BETWEEN '2016-01-01 00:00:00' AND '2016-12-31 23:59:59'
AND STATUSID = 'COMPLETED';

Upvotes: 1

Related Questions