Error in Select Statement

Question

Post reply

Error in Select Statement

rahulsony111

Hall of Fame

Points: 3865
More actions
December 27, 2009 at 7:59 pm

#218984

Row_id_no is the column.
This is the code
SELECT max(DWT40034_BZL_VAT_TAX.ROW_ID_NO)
from (select DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD, DWT40034_BZL_VAT_TAX.ORD_LN,
max(DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT)
from dbo.DWT40034_BZL_VAT_TAX
where DWT40034_BZL_VAT_TAX.ETL_PROC_STAT = 1
group by DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD, DWT40034_BZL_VAT_TAX.ORD_LN)
Error Report
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

a subselect in parenthesis must have an alias;

formatting helps identiy the problem, too:

SELECT

max(DWT40034_BZL_VAT_TAX.ROW_ID_NO)

from

(

select

DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD,

DWT40034_BZL_VAT_TAX.ORD,

DWT40034_BZL_VAT_TAX.ORD_LN,

max(DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT)

from dbo.DWT40034_BZL_VAT_TAX

where DWT40034_BZL_VAT_TAX.ETL_PROC_STAT = 1

group by DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD, DWT40034_BZL_VAT_TAX.ORD_LN

) MyAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 2

I tried the code you gave, but got error

Msg 8155, Level 16, State 2, Line 1

No column was specified for column 4 of 'MyAlias'.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "DWT40034_BZL_VAT_TAX.ROW_ID_NO" could not be bound.

I tried this also

SELECT

max(MyAlias.ROW_ID_NO)

from

(

select

DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD,

DWT40034_BZL_VAT_TAX.ORD,

DWT40034_BZL_VAT_TAX.ORD_LN,

max(DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT)

from dbo.DWT40034_BZL_VAT_TAX

where DWT40034_BZL_VAT_TAX.ETL_PROC_STAT = 1

group by DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD, DWT40034_BZL_VAT_TAX.ORD_LN

) MyAlias

But didn't work

Msg 8155, Level 16, State 2, Line 1

No column was specified for column 4 of 'MyAlias'.

Msg 207, Level 16, State 1, Line 2

Invalid column name 'ROW_ID_NO'.

Lowell SSC Guru Points: 323518 More actions · Answer 3

the error message is the clue: you can't select the row_id_no, because it is not part of the subquery in parenthesis.

remember what i said about aliases and parenthesis? that goes for MIN()/MAX()/SUM() functions as well

SELECT

max(MyAlias.ROW_ID_NO)

from

(

select

DWT40034_BZL_VAT_TAX.ROW_ID_NO,

DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD,

DWT40034_BZL_VAT_TAX.ORD,

DWT40034_BZL_VAT_TAX.ORD_LN,

max(DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT) AS MYDATE

from dbo.DWT40034_BZL_VAT_TAX

where DWT40034_BZL_VAT_TAX.ETL_PROC_STAT = 1

group by DWT40034_BZL_VAT_TAX.ROW_ID_NO,DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD, DWT40034_BZL_VAT_TAX.ORD_LN

) MyAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 4

rahulsony111

Hall of Fame

Points: 3865

December 27, 2009 at 8:39 pm

#1096594

Ok i got it

Thank you very much

rahulsony111 Hall of Fame Points: 3865 More actions · Answer 5

Can i write like this

SELECT VAT_TAX.ROW_ID_NO = (max(DWT40034_BZL_VAT_TAX.ROW_ID_NO)

from (select

DWT40034_BZL_VAT_TAX.ROW_ID_NO,

DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD,

DWT40034_BZL_VAT_TAX.ORD,

DWT40034_BZL_VAT_TAX.ORD_LN,

max(DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT) as Mydate

from dbo.DWT40034_BZL_VAT_TAX

where DWT40034_BZL_VAT_TAX.ETL_PROC_STAT=1

group by DWT40034_BZL_VAT_TAX.ROW_ID_NO, DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD,

DWT40034_BZL_VAT_TAX.ORD_LN))VAT_TAX