December 27, 2009 at 9:17 pm
This sounds like a repost, but i hope not.
If it looks so please forgive me.
But i am in urgency, please help me
This is code
select
DWT40034_BZL_VAT_TAX.ROW_ID_NO as C1_ROW_ID_NO,
DWT40034_BZL_VAT_TAX.ETL_PROC_STAT as C2_ETL_PROC_STAT,
DWT40034_BZL_VAT_TAX.INTGRT_CTL_AFF as C3_INTGRT_CTL_AFF,
DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD as C4_INTGRT_CNTRY_CD,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_CD as C5_INTGRT_SRC_TRX_CD,
CONVERT(CHAR(23),DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT,121) as C6_INTGRT_SRC_TRX_DT,
DWT40034_BZL_VAT_TAX.INTGRT_PUB_APP as C7_INTGRT_PUB_APP,
DWT40034_BZL_VAT_TAX.INTGRT_BUF_SEQ_NBR as C8_INTGRT_BUF_SEQ_NBR,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_FILE as C9_INTGRT_SRC_FILE,
DWT40034_BZL_VAT_TAX.INTGRT_PRIORITY as C10_INTGRT_PRIORITY,
DWT40034_BZL_VAT_TAX.INTGRT_DOC_ID as C11_INTGRT_DOC_ID,
DWT40034_BZL_VAT_TAX.ORD as C12_ORD,
DWT40034_BZL_VAT_TAX.ORD_LN as C13_ORD_LN,
DWT40034_BZL_VAT_TAX.IPI_TAX as C14_IPI_TAX,
DWT40034_BZL_VAT_TAX.PIS_TAX as C15_PIS_TAX,
DWT40034_BZL_VAT_TAX.CONFINS_TAX as C16_CONFINS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_TAX as C17_ICMS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_ST_TAX as C18_ICMS_ST_TAX
fromDWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX as DWT40034_BZL_VAT_TAX
where(1=1)
And DWT40034_BZL_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)
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))
Error Report
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near ')'.
December 27, 2009 at 9:32 pm
..
..
..
DWT40034_BZL_VAT_TAX.ICMS_TAX as C17_ICMS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_ST_TAX as C18_ICMS_ST_TAX
from DWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX as DWT40034_BZL_VAT_TAX
WHERE DWT40034_BZL_VAT_TAX.ROW_ID_NO IN
(
SELECT 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)
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
) AS X
)
December 27, 2009 at 9:39 pm
Hey i tried your code
select
DWT40034_BZL_VAT_TAX.ROW_ID_NO as C1_ROW_ID_NO,
DWT40034_BZL_VAT_TAX.ETL_PROC_STAT as C2_ETL_PROC_STAT,
DWT40034_BZL_VAT_TAX.INTGRT_CTL_AFF as C3_INTGRT_CTL_AFF,
DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD as C4_INTGRT_CNTRY_CD,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_CD as C5_INTGRT_SRC_TRX_CD,
CONVERT(CHAR(23),DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT,121) as C6_INTGRT_SRC_TRX_DT,
DWT40034_BZL_VAT_TAX.INTGRT_PUB_APP as C7_INTGRT_PUB_APP,
DWT40034_BZL_VAT_TAX.INTGRT_BUF_SEQ_NBR as C8_INTGRT_BUF_SEQ_NBR,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_FILE as C9_INTGRT_SRC_FILE,
DWT40034_BZL_VAT_TAX.INTGRT_PRIORITY as C10_INTGRT_PRIORITY,
DWT40034_BZL_VAT_TAX.INTGRT_DOC_ID as C11_INTGRT_DOC_ID,
DWT40034_BZL_VAT_TAX.ORD as C12_ORD,
DWT40034_BZL_VAT_TAX.ORD_LN as C13_ORD_LN,
DWT40034_BZL_VAT_TAX.IPI_TAX as C14_IPI_TAX,
DWT40034_BZL_VAT_TAX.PIS_TAX as C15_PIS_TAX,
DWT40034_BZL_VAT_TAX.CONFINS_TAX as C16_CONFINS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_TAX as C17_ICMS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_ST_TAX as C18_ICMS_ST_TAX
from DWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX as DWT40034_BZL_VAT_TAX
WHERE DWT40034_BZL_VAT_TAX.ROW_ID_NO IN
(
SELECT 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)
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
) AS X
)
Error report
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 5 of 'X'.
December 27, 2009 at 9:46 pm
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 5 of 'X'.
Hi,
Message say the column name not mentioned,
SELECT 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)INTGRT_SRC_TRX_DT /*Message says this column not mentioned as a name*/
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
) AS X
December 27, 2009 at 9:59 pm
I didn't get what you have said
What to do for that
December 27, 2009 at 10:10 pm
Hi,
Message says the col5 name not specified.
so try to name the column5 as below
select max(col1)
(
select column1 as col1,
column2 as col2,
column3 as col3,
column4 as col4,
max(column5) /*here the problem that your not mention any name of the column as col5*/
from mytable
group by column1,column2,column3,column4
) as x
December 27, 2009 at 10:20 pm
I tried that code also
select
DWT40034_BZL_VAT_TAX.ROW_ID_NO as C1_ROW_ID_NO,
DWT40034_BZL_VAT_TAX.ETL_PROC_STAT as C2_ETL_PROC_STAT,
DWT40034_BZL_VAT_TAX.INTGRT_CTL_AFF as C3_INTGRT_CTL_AFF,
DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD as C4_INTGRT_CNTRY_CD,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_CD as C5_INTGRT_SRC_TRX_CD,
CONVERT(CHAR(23),DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT,121) as C6_INTGRT_SRC_TRX_DT,
DWT40034_BZL_VAT_TAX.INTGRT_PUB_APP as C7_INTGRT_PUB_APP,
DWT40034_BZL_VAT_TAX.INTGRT_BUF_SEQ_NBR as C8_INTGRT_BUF_SEQ_NBR,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_FILE as C9_INTGRT_SRC_FILE,
DWT40034_BZL_VAT_TAX.INTGRT_PRIORITY as C10_INTGRT_PRIORITY,
DWT40034_BZL_VAT_TAX.INTGRT_DOC_ID as C11_INTGRT_DOC_ID,
DWT40034_BZL_VAT_TAX.ORD as C12_ORD,
DWT40034_BZL_VAT_TAX.ORD_LN as C13_ORD_LN,
DWT40034_BZL_VAT_TAX.IPI_TAX as C14_IPI_TAX,
DWT40034_BZL_VAT_TAX.PIS_TAX as C15_PIS_TAX,
DWT40034_BZL_VAT_TAX.CONFINS_TAX as C16_CONFINS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_TAX as C17_ICMS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_ST_TAX as C18_ICMS_ST_TAX
from DWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX as DWT40034_BZL_VAT_TAX
WHERE DWT40034_BZL_VAT_TAX.ROW_ID_NO IN
(
SELECT 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) 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.ROW_ID_NO, DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD,
DWT40034_BZL_VAT_TAX.ORD_LN
) AS X
)
Error report
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
December 27, 2009 at 10:30 pm
Hi,
What try to achieve?
Actually am tried to solve the errors what you post the above,
Ok, the retrieve data should be from DWT40034_BZL_VAT_TAX table for the max ROW_ID_NO, is in it?
December 27, 2009 at 10:39 pm
I am sorry for irritating you.
But i am helpless
Data is there in DWT40034_BZL_VAT_TAX and even in ROW_ID_NO
December 27, 2009 at 10:40 pm
Hi,
try with the below join
..
..
..
DWT40034_BZL_VAT_TAX.ICMS_TAX as C17_ICMS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_ST_TAX as C18_ICMS_ST_TAX
from DWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX as DWT40034_BZL_VAT_TAX
inner join
(
select max(DWT40034_BZL_VAT_TAX.ROW_ID_NO)ROW_ID_NO, DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD, DWT40034_BZL_VAT_TAX.ORD,
DWT40034_BZL_VAT_TAX.ORD_LN,
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
)as DWT40034_BZL_VAT_TAX_MAX
on DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD = DWT40034_BZL_VAT_TAX_MAX.INTGRT_CNTRY_CD
and DWT40034_BZL_VAT_TAX.ORD = DWT40034_BZL_VAT_TAX_MAX.ORD
and DWT40034_BZL_VAT_TAX.ORD_LN = DWT40034_BZL_VAT_TAX_MAX.ORD_LN
and DWT40034_BZL_VAT_TAX.ROW_ID_NO = DWT40034_BZL_VAT_TAX_MAX.ROW_ID_NO
December 28, 2009 at 6:40 am
This code worked for me
select
DWT40034_BZL_VAT_TAX.ROW_ID_NO as C1_ROW_ID_NO,
DWT40034_BZL_VAT_TAX.ETL_PROC_STAT as C2_ETL_PROC_STAT,
DWT40034_BZL_VAT_TAX.INTGRT_CTL_AFF as C3_INTGRT_CTL_AFF,
DWT40034_BZL_VAT_TAX.INTGRT_CNTRY_CD as C4_INTGRT_CNTRY_CD,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_CD as C5_INTGRT_SRC_TRX_CD,
CONVERT(CHAR(23),DWT40034_BZL_VAT_TAX.INTGRT_SRC_TRX_DT,121) as C6_INTGRT_SRC_TRX_DT,
DWT40034_BZL_VAT_TAX.INTGRT_PUB_APP as C7_INTGRT_PUB_APP,
DWT40034_BZL_VAT_TAX.INTGRT_BUF_SEQ_NBR as C8_INTGRT_BUF_SEQ_NBR,
DWT40034_BZL_VAT_TAX.INTGRT_SRC_FILE as C9_INTGRT_SRC_FILE,
DWT40034_BZL_VAT_TAX.INTGRT_PRIORITY as C10_INTGRT_PRIORITY,
DWT40034_BZL_VAT_TAX.INTGRT_DOC_ID as C11_INTGRT_DOC_ID,
DWT40034_BZL_VAT_TAX.ORD as C12_ORD,
DWT40034_BZL_VAT_TAX.ORD_LN as C13_ORD_LN,
DWT40034_BZL_VAT_TAX.IPI_TAX as C14_IPI_TAX,
DWT40034_BZL_VAT_TAX.PIS_TAX as C15_PIS_TAX,
DWT40034_BZL_VAT_TAX.CONFINS_TAX as C16_CONFINS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_TAX as C17_ICMS_TAX,
DWT40034_BZL_VAT_TAX.ICMS_ST_TAX as C18_ICMS_ST_TAX
fromDWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX as DWT40034_BZL_VAT_TAX
where(1=1)
And (DWT40034_BZL_VAT_TAX.ROW_ID_NO IN ((Select MAX(ROW_ID_NO) AS MAX_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) AS last_INTGRT_SRC_TRX_DT
FROM DWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX (nolock)
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) vat_last
INNER JOIN
DWB01002_GLOBAL_DW_EXTRACT.dbo.DWT40034_BZL_VAT_TAX vat_tax(nolock) on
vat_tax.last_INTGRT_SRC_TRX_DT = vat_last.last_INTGRT_SRC_TRX_DT AND
vat_tax.ORD = vat_last.ORD AND
vat_tax.ORD_LN=vat_last.ORD_LN AND
vat_tax.INTGRT_CNTRY_CD=vat_last.INTGRT_CNTRY_CD
GROUP BY vat_tax.INTGRT_CNTRY_CD, vat_tax.ORD, vat_tax.ORD_LN, vat_tax.INTGRT_SRC_TRX_DT )))
Thank you for your suggestions
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply