SQL

Question

Post reply

SQL

lungy-1072376

Valued Member

Points: 60
More actions
April 29, 2009 at 5:46 am

#199968

I am running this query in SQL 2005
And it gives me this error... please help solve di matter
Thanks a lot
SELECT M.ORDER_NUMBER,
M.ORDER_TYPE,
M.LINE_NUMBER,
CONVERT(VARCHAR, M.COMPLETION_DATE , 103) --As COMPLETION_DATE
INTO #TEMP
FROM MOVEMENT M (NOLOCK)
JOIN ORDER_MASTER OM (NOLOCK)
ON M.ORDER_NUMBER = OM.ORDER_NUMBER
AND M.ORDER_TYPE = OM.ORDER_TYPE
JOIN ORDER_LINE OL (NOLOCK)
ON M.ORDER_NUMBER = OL.ORDER_NUMBER
AND M.ORDER_TYPE = OL.ORDER_TYPE
AND M.LINE_NUMBER = OL.LINE_NUMBER
AND M.COMPLETION_DATE = OL.COMPLETION_DATE
--AND M.COMPLETION_DATE = M.COMPLETION_DATE
WHERE OM.ORGANISATION_CODE = 'AA01'
AND M.MOVEMENT_FROM IN ('STO', 'INS')
AND M.MOVEMENT_TO = 'SUP'
AND M.ORDER_TYPE = 'PUR'
AND M.RETURN_ADVICE_NUMBER LIKE 'RA002114%'
GROUP BY M.ORDER_NUMBER,
M.ORDER_TYPE,
M.LINE_NUMBER,
M.COMPLETION_DATE
HAVING (ISNULL((SELECT SUM(LINKED_QUANTITY * CHARINDEX('P', TYPE_OF_LINK)) - SUM(LINKED_QUANTITY * CHARINDEX('C', TYPE_OF_LINK))
FROM ORDER_LINE OL (NOLOCK)
JOIN INVOICE_LINE_LINK ILL (NOLOCK) ON ILL.ORDER_NUMBER = OL.ORDER_NUMBER
AND ILL.ORDER_TYPE = OL.ORDER_TYPE
AND ILL.ORDER_LINE_NUMBER = OL.LINE_NUMBER
AND ILL.COMPLETION_DATE = OL.COMPLETION_DATE
WHERE OL.ORDER_NUMBER = M.ORDER_NUMBER
AND OL.ORDER_TYPE = M.ORDER_TYPE
AND OL.LINE_NUMBER = M.LINE_NUMBER
AND OL.COMPLETION_DATE = M.COMPLETION_DATE
AND ILL.STATUS = 'A'), 0)- (SELECT SUM(QUANTITY_COMPLETE + QUANTITY_IN_INSPECTION_CRATED)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = M.ORDER_NUMBER
AND OL.ORDER_TYPE = M.ORDER_TYPE
AND OL.LINE_NUMBER = M.LINE_NUMBER
AND OL.COMPLETION_DATE = M.COMPLETION_DATE)) > 0
SELECT ILL.INVOICE_NUMBER, ILL.LINE_NUMBER, CONVERT(VARCHAR, ILL.CAPTURE_DATE, 109),
((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1)
ELSE ILL3.LINKED_QUANTITY
END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A' AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A'
AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) 'NEW_QUANTITY',
((SELECT ISNULL
((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1)
ELSE ILL3.LINKED_QUANTITY
END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A'
AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A' AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER
AND OL.ORDER_TYPE = ILL.ORDER_TYPE
AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) 'TOTAL PRICE' , (((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1) ELSE ILL3.LINKED_QUANTITY END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A'
AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A'
AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A'
AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER
AND OL.ORDER_TYPE = ILL.ORDER_TYPE
AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) * ((SELECT TCP2.TAX_PERCENTAGE
FROM TAX_CODE_PERCENTAGE TCP2
WHERE TCP2.TAX_CODE = ILL.TAX_CODE
AND TCP2.EFFECTIVE_DATE = (SELECT MAX(TCP1.EFFECTIVE_DATE)
FROM TAX_CODE_PERCENTAGE TCP1
WHERE TCP1.TAX_CODE = ILL.TAX_CODE
AND TCP1.EFFECTIVE_DATE < ILL.CAPTURE_DATE))/100)) 'TAX AMOUNT' , ILL.TAX_CODE,
((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1)
ELSE ILL3.LINKED_QUANTITY
END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A'
AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A' AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER
AND OL.ORDER_TYPE = ILL.ORDER_TYPE
AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) 'TOTAL_PRICE_INVOICED',
(SELECT M.RETURN_ADVICE_NUMBER
FROM MOVEMENT M (NOLOCK)
WHERE M.ORDER_NUMBER = ILL.ORDER_NUMBER
AND M.ORDER_TYPE = ILL.ORDER_TYPE
AND M.LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND M.COMPLETION_DATE = ILL.COMPLETION_DATE
AND M.MOVEMENT_FROM IN ('STO', 'INS')
AND M.MOVEMENT_TO = 'SUP' AND M.DATE_TIME = (SELECT MAX(M2.DATE_TIME)
FROM MOVEMENT M2 (NOLOCK) WHERE M2.ORDER_NUMBER = M.ORDER_NUMBER
AND M2.ORDER_TYPE = M.ORDER_TYPE
AND M2.LINE_NUMBER = M.LINE_NUMBER
AND M2.COMPLETION_DATE = M.COMPLETION_DATE
AND M2.MOVEMENT_FROM = M.MOVEMENT_FROM
AND M2.MOVEMENT_TO = M.MOVEMENT_TO)) 'RETURN_ADVICE',
ILL.ORDER_NUMBER, ILL.ORDER_LINE_NUMBER,
CONVERT(VARCHAR, ILL.COMPLETION_DATE , 103) ,
ILL.COMMENTS, (TCP.TAX_PERCENTAGE / 100),
CONVERT(CHAR(11), ILL.CAPTURE_DATE, 100) + ' ' + CONVERT(VARCHAR, ILL.CAPTURE_DATE, 114),
CONVERT(CHAR(11), ILL.COMPLETION_DATE, 100) + ' ' + CONVERT(VARCHAR, ILL.COMPLETION_DATE, 114) ,
ILL.LINK_CLASS,
ILL.EXEMPT_INDICATOR,
ILL.EXEMPT_AMOUNT,
ILL.TAX_REASON_CODE,
ILL.SETTLEMENT_TERM
FROM INVOICE_LINE_LINK ILL (NOLOCK)
JOIN ORDER_LINE OL (NOLOCK)
ON ILL.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL.ORDER_NUMBER = OL.ORDER_NUMBER
AND ILL.ORDER_LINE_NUMBER = OL.LINE_NUMBER
JOIN TAX_CODE_PERCENTAGE TCP
ON TCP.TAX_CODE = ILL.TAX_CODE
AND TCP.EFFECTIVE_DATE = (SELECT MAX(TCP1.EFFECTIVE_DATE)
FROM TAX_CODE_PERCENTAGE TCP1
WHERE TCP1.TAX_CODE = ILL.TAX_CODE AND TCP1.EFFECTIVE_DATE < ILL.CAPTURE_DATE) JOIN #TEMP
ON ILL.ORDER_NUMBER = #TEMP.ORDER_NUMBER AND ILL.ORDER_TYPE = #TEMP.ORDER_TYPE AND ILL.ORDER_LINE_NUMBER = #TEMP.LINE_NUMBER AND ILL.COMPLETION_DATE = #TEMP.COMPLETION_DATE WHERE ILL.ORGANISATION_CODE = 'AA01' AND ILL.STATUS = 'A' AND ILL.TYPE_OF_LINK = 'P' AND ILL.CAPTURE_DATE = (SELECT MAX(ILL2.CAPTURE_DATE) FROM INVOICE_LINE_LINK ILL2 (NOLOCK) WHERE ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE AND ILL2.STATUS = 'A' AND
ILL2.TYPE_OF_LINK = ILL.TYPE_OF_LINK )
ORDER BY ILL.CAPTURE_DATE DESC
DROP TABLE #TEMP
ERROR I GET IS:
Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

Viewing 9 posts - 1 through 8 (of 8 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

this part

CONVERT(VARCHAR, M.COMPLETION_DATE , 103) --As COMPLETION_DATE

has the alias commented out try removing the comment marks

CONVERT(VARCHAR, M.COMPLETION_DATE , 103) As COMPLETION_DATE

lungy-1072376 Valued Member Points: 60 More actions · Answer 2

ja i have removed commented part AND still my front gives me the same error so it does not work

The original code is this one which gives me this bug:

Select M.ORDER_NUMBER,

M.ORDER_TYPE,

M.LINE_NUMBER,

CONVERT(VARCHAR, M.COMPLETION_DATE , 103)

INTO #TEMP

FROM MOVEMENT M (NOLOCK)

JOIN ORDER_MASTER OM (NOLOCK)

ON M.ORDER_NUMBER = OM.ORDER_NUMBER

AND M.ORDER_TYPE = OM.ORDER_TYPE

JOIN ORDER_LINE OL (NOLOCK)

ON M.ORDER_NUMBER = OL.ORDER_NUMBER

AND M.ORDER_TYPE = OL.ORDER_TYPE

AND M.LINE_NUMBER = OL.LINE_NUMBER

AND M.COMPLETION_DATE = OL.COMPLETION_DATE

--AND M.COMPLETION_DATE = M.COMPLETION_DATE

WHERE OM.ORGANISATION_CODE = 'AA01'

AND M.MOVEMENT_FROM IN ('STO', 'INS')

AND M.MOVEMENT_TO = 'SUP'

AND M.ORDER_TYPE = 'PUR'

AND M.RETURN_ADVICE_NUMBER LIKE 'RA002114%'

GROUP BY M.ORDER_NUMBER,

M.ORDER_TYPE,

M.LINE_NUMBER,

M.COMPLETION_DATE

HAVING (ISNULL((SELECT SUM(LINKED_QUANTITY * CHARINDEX('P', TYPE_OF_LINK)) - SUM(LINKED_QUANTITY * CHARINDEX('C', TYPE_OF_LINK))

FROM ORDER_LINE OL (NOLOCK)

JOIN INVOICE_LINE_LINK ILL (NOLOCK) ON ILL.ORDER_NUMBER = OL.ORDER_NUMBER

AND ILL.ORDER_TYPE = OL.ORDER_TYPE

AND ILL.ORDER_LINE_NUMBER = OL.LINE_NUMBER

AND ILL.COMPLETION_DATE = OL.COMPLETION_DATE

WHERE OL.ORDER_NUMBER = M.ORDER_NUMBER

AND OL.ORDER_TYPE = M.ORDER_TYPE

AND OL.LINE_NUMBER = M.LINE_NUMBER

AND OL.COMPLETION_DATE = M.COMPLETION_DATE

AND ILL.STATUS = 'A'), 0)- (SELECT SUM(QUANTITY_COMPLETE + QUANTITY_IN_INSPECTION_CRATED)

FROM ORDER_LINE OL (NOLOCK)

WHERE OL.ORDER_NUMBER = M.ORDER_NUMBER

AND OL.ORDER_TYPE = M.ORDER_TYPE

AND OL.LINE_NUMBER = M.LINE_NUMBER

AND OL.COMPLETION_DATE = M.COMPLETION_DATE)) > 0

SELECT ILL.INVOICE_NUMBER, ILL.LINE_NUMBER, CONVERT(VARCHAR, ILL.CAPTURE_DATE, 109),

((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'

THEN (ILL2.LINKED_QUANTITY * -1)

ELSE ILL2.LINKED_QUANTITY

END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'

THEN (ILL3.LINKED_QUANTITY * -1)

ELSE ILL3.LINKED_QUANTITY

END)

FROM INVOICE_LINE_LINK ILL3 (NOLOCK)

JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)

ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE

AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER

AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER

AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE

AND ILL3.STATUS = 'A' AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'

AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)

FROM INVOICE_LINE_LINK ILL2 (NOLOCK)

WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL2.STATUS = 'A'

AND ILL2.TYPE_OF_LINK = 'P'

AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) 'NEW_QUANTITY',

((SELECT ISNULL

((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'

THEN (ILL2.LINKED_QUANTITY * -1)

ELSE ILL2.LINKED_QUANTITY

END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'

THEN (ILL3.LINKED_QUANTITY * -1)

ELSE ILL3.LINKED_QUANTITY

END)

FROM INVOICE_LINE_LINK ILL3 (NOLOCK)

JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)

ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE

AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER

AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER

AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE

AND ILL3.STATUS = 'A'

AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'

AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)

FROM INVOICE_LINE_LINK ILL2 (NOLOCK)

WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL2.STATUS = 'A' AND ILL2.TYPE_OF_LINK = 'P'

AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)

FROM ORDER_LINE OL (NOLOCK)

WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER

AND OL.ORDER_TYPE = ILL.ORDER_TYPE

AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) 'TOTAL PRICE' , (((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'

THEN (ILL2.LINKED_QUANTITY * -1)

ELSE ILL2.LINKED_QUANTITY

END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'

THEN (ILL3.LINKED_QUANTITY * -1) ELSE ILL3.LINKED_QUANTITY END)

FROM INVOICE_LINE_LINK ILL3 (NOLOCK)

JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)

ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE

AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER

AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER

AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE

AND ILL3.STATUS = 'A'

AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL4.STATUS = 'A'

AND ILL4.TYPE_OF_LINK = 'P'

AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)

FROM INVOICE_LINE_LINK ILL2 (NOLOCK)

WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL2.STATUS = 'A'

AND ILL2.TYPE_OF_LINK = 'P'

AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)

FROM ORDER_LINE OL (NOLOCK)

WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER

AND OL.ORDER_TYPE = ILL.ORDER_TYPE

AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) * ((SELECT TCP2.TAX_PERCENTAGE

FROM TAX_CODE_PERCENTAGE TCP2

WHERE TCP2.TAX_CODE = ILL.TAX_CODE

AND TCP2.EFFECTIVE_DATE = (SELECT MAX(TCP1.EFFECTIVE_DATE)

FROM TAX_CODE_PERCENTAGE TCP1

WHERE TCP1.TAX_CODE = ILL.TAX_CODE

AND TCP1.EFFECTIVE_DATE < ILL.CAPTURE_DATE))/100)) 'TAX AMOUNT' , ILL.TAX_CODE,

((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'

THEN (ILL2.LINKED_QUANTITY * -1)

ELSE ILL2.LINKED_QUANTITY

END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'

THEN (ILL3.LINKED_QUANTITY * -1)

ELSE ILL3.LINKED_QUANTITY

END)

FROM INVOICE_LINE_LINK ILL3 (NOLOCK)

JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)

ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE

AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER

AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER

AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE

AND ILL3.STATUS = 'A'

AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'

AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)

FROM INVOICE_LINE_LINK ILL2 (NOLOCK)

WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL2.STATUS = 'A' AND ILL2.TYPE_OF_LINK = 'P'

AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER

AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE

AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)

FROM ORDER_LINE OL (NOLOCK)

WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER

AND OL.ORDER_TYPE = ILL.ORDER_TYPE

AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) 'TOTAL_PRICE_INVOICED',

(SELECT M.RETURN_ADVICE_NUMBER

FROM MOVEMENT M (NOLOCK)

WHERE M.ORDER_NUMBER = ILL.ORDER_NUMBER

AND M.ORDER_TYPE = ILL.ORDER_TYPE

AND M.LINE_NUMBER = ILL.ORDER_LINE_NUMBER

AND M.COMPLETION_DATE = ILL.COMPLETION_DATE

AND M.MOVEMENT_FROM IN ('STO', 'INS')

AND M.MOVEMENT_TO = 'SUP' AND M.DATE_TIME = (SELECT MAX(M2.DATE_TIME)

FROM MOVEMENT M2 (NOLOCK) WHERE M2.ORDER_NUMBER = M.ORDER_NUMBER

AND M2.ORDER_TYPE = M.ORDER_TYPE

AND M2.LINE_NUMBER = M.LINE_NUMBER

AND M2.COMPLETION_DATE = M.COMPLETION_DATE

AND M2.MOVEMENT_FROM = M.MOVEMENT_FROM

AND M2.MOVEMENT_TO = M.MOVEMENT_TO)) 'RETURN_ADVICE',

ILL.ORDER_NUMBER, ILL.ORDER_LINE_NUMBER,

CONVERT(VARCHAR, ILL.COMPLETION_DATE , 103) ,

ILL.COMMENTS, (TCP.TAX_PERCENTAGE / 100),

CONVERT(CHAR(11), ILL.CAPTURE_DATE, 100) + ' ' + CONVERT(VARCHAR, ILL.CAPTURE_DATE, 114),

CONVERT(CHAR(11), ILL.COMPLETION_DATE, 100) + ' ' + CONVERT(VARCHAR, ILL.COMPLETION_DATE, 114) ,

ILL.LINK_CLASS,

ILL.EXEMPT_INDICATOR,

ILL.EXEMPT_AMOUNT,

ILL.TAX_REASON_CODE,

ILL.SETTLEMENT_TERM

FROM INVOICE_LINE_LINK ILL (NOLOCK)

JOIN ORDER_LINE OL (NOLOCK)

ON ILL.ORGANISATION_CODE = ILL.ORGANISATION_CODE

AND ILL.ORDER_NUMBER = OL.ORDER_NUMBER

AND ILL.ORDER_LINE_NUMBER = OL.LINE_NUMBER

JOIN TAX_CODE_PERCENTAGE TCP

ON TCP.TAX_CODE = ILL.TAX_CODE

AND TCP.EFFECTIVE_DATE = (SELECT MAX(TCP1.EFFECTIVE_DATE)

FROM TAX_CODE_PERCENTAGE TCP1

WHERE TCP1.TAX_CODE = ILL.TAX_CODE AND TCP1.EFFECTIVE_DATE < ILL.CAPTURE_DATE) JOIN #TEMP

ON ILL.ORDER_NUMBER = #TEMP.ORDER_NUMBER AND ILL.ORDER_TYPE = #TEMP.ORDER_TYPE AND ILL.ORDER_LINE_NUMBER = #TEMP.LINE_NUMBER AND ILL.COMPLETION_DATE = #TEMP.COMPLETION_DATE WHERE ILL.ORGANISATION_CODE = 'AA01' AND ILL.STATUS = 'A' AND ILL.TYPE_OF_LINK = 'P' AND ILL.CAPTURE_DATE = (SELECT MAX(ILL2.CAPTURE_DATE) FROM INVOICE_LINE_LINK ILL2 (NOLOCK) WHERE ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE AND ILL2.STATUS = 'A' AND

ILL2.TYPE_OF_LINK = ILL.TYPE_OF_LINK )

ORDER BY ILL.CAPTURE_DATE DESC

DROP TABLE #TEMP

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Err... did you read Steve's post correct?

You have to specify a name for the column if you use SELECT INTO.

... which creates a new table and requires a name for each column...

Greets

Flo

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

lungy (4/29/2009)
ja i have removed commented part AND still my front gives me the same error so it does not work
The original code is this one which gives me this bug:
Select M.ORDER_NUMBER,
M.ORDER_TYPE,
M.LINE_NUMBER,
CONVERT(VARCHAR, M.COMPLETION_DATE , 103)
INTO #TEMP
FROM MOVEMENT M (NOLOCK)
JOIN ORDER_MASTER OM (NOLOCK)
ON M.ORDER_NUMBER = OM.ORDER_NUMBER
AND M.ORDER_TYPE = OM.ORDER_TYPE
JOIN ORDER_LINE OL (NOLOCK)
ON M.ORDER_NUMBER = OL.ORDER_NUMBER
AND M.ORDER_TYPE = OL.ORDER_TYPE
AND M.LINE_NUMBER = OL.LINE_NUMBER
AND M.COMPLETION_DATE = OL.COMPLETION_DATE
--AND M.COMPLETION_DATE = M.COMPLETION_DATE
WHERE OM.ORGANISATION_CODE = 'AA01'
AND M.MOVEMENT_FROM IN ('STO', 'INS')
AND M.MOVEMENT_TO = 'SUP'
AND M.ORDER_TYPE = 'PUR'
AND M.RETURN_ADVICE_NUMBER LIKE 'RA002114%'
GROUP BY M.ORDER_NUMBER,
M.ORDER_TYPE,
M.LINE_NUMBER,
M.COMPLETION_DATE
HAVING (ISNULL((SELECT SUM(LINKED_QUANTITY * CHARINDEX('P', TYPE_OF_LINK)) - SUM(LINKED_QUANTITY * CHARINDEX('C', TYPE_OF_LINK))
FROM ORDER_LINE OL (NOLOCK)
JOIN INVOICE_LINE_LINK ILL (NOLOCK) ON ILL.ORDER_NUMBER = OL.ORDER_NUMBER
AND ILL.ORDER_TYPE = OL.ORDER_TYPE
AND ILL.ORDER_LINE_NUMBER = OL.LINE_NUMBER
AND ILL.COMPLETION_DATE = OL.COMPLETION_DATE
WHERE OL.ORDER_NUMBER = M.ORDER_NUMBER
AND OL.ORDER_TYPE = M.ORDER_TYPE
AND OL.LINE_NUMBER = M.LINE_NUMBER
AND OL.COMPLETION_DATE = M.COMPLETION_DATE
AND ILL.STATUS = 'A'), 0)- (SELECT SUM(QUANTITY_COMPLETE + QUANTITY_IN_INSPECTION_CRATED)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = M.ORDER_NUMBER
AND OL.ORDER_TYPE = M.ORDER_TYPE
AND OL.LINE_NUMBER = M.LINE_NUMBER
AND OL.COMPLETION_DATE = M.COMPLETION_DATE)) > 0
SELECT ILL.INVOICE_NUMBER, ILL.LINE_NUMBER, CONVERT(VARCHAR, ILL.CAPTURE_DATE, 109),
((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1)
ELSE ILL3.LINKED_QUANTITY
END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A' AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A'
AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) 'NEW_QUANTITY',
((SELECT ISNULL
((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1)
ELSE ILL3.LINKED_QUANTITY
END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A'
AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A' AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER
AND OL.ORDER_TYPE = ILL.ORDER_TYPE
AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) 'TOTAL PRICE' , (((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1) ELSE ILL3.LINKED_QUANTITY END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A'
AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A'
AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A'
AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER
AND OL.ORDER_TYPE = ILL.ORDER_TYPE
AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) * ((SELECT TCP2.TAX_PERCENTAGE
FROM TAX_CODE_PERCENTAGE TCP2
WHERE TCP2.TAX_CODE = ILL.TAX_CODE
AND TCP2.EFFECTIVE_DATE = (SELECT MAX(TCP1.EFFECTIVE_DATE)
FROM TAX_CODE_PERCENTAGE TCP1
WHERE TCP1.TAX_CODE = ILL.TAX_CODE
AND TCP1.EFFECTIVE_DATE < ILL.CAPTURE_DATE))/100)) 'TAX AMOUNT' , ILL.TAX_CODE,
((SELECT ISNULL((SELECT SUM(CASE WHEN ILL2.TYPE_OF_LINK = 'C'
THEN (ILL2.LINKED_QUANTITY * -1)
ELSE ILL2.LINKED_QUANTITY
END) + ISNULL((SELECT SUM(CASE WHEN ILL3.TYPE_OF_LINK = 'C'
THEN (ILL3.LINKED_QUANTITY * -1)
ELSE ILL3.LINKED_QUANTITY
END)
FROM INVOICE_LINE_LINK ILL3 (NOLOCK)
JOIN INVOICE_LINE_LINK ILL4 (NOLOCK)
ON ILL3.ORGANISATION_CODE = ILL4.ORGANISATION_CODE
AND ILL3.SOURCE_INVOICE_NUMBER = ILL4.INVOICE_NUMBER
AND ILL3.SOURCE_LINE_NUMBER = ILL4.LINE_NUMBER
AND ILL3.SOURCE_CAPTURE_DATE = ILL4.CAPTURE_DATE
AND ILL3.STATUS = 'A'
AND ILL4.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL4.STATUS = 'A' AND ILL4.TYPE_OF_LINK = 'P'
AND ILL4.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL4.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL4.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL4.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)
FROM INVOICE_LINE_LINK ILL2 (NOLOCK)
WHERE ILL2.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL2.STATUS = 'A' AND ILL2.TYPE_OF_LINK = 'P'
AND ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER
AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE
AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE), 0.00)) - (OL.QUANTITY_COMPLETE + OL.QUANTITY_IN_INSPECTION_CRATED)) * (ISNULL((SELECT SUM(OL.UNIT_PRICE)
FROM ORDER_LINE OL (NOLOCK)
WHERE OL.ORDER_NUMBER = ILL.ORDER_NUMBER
AND OL.ORDER_TYPE = ILL.ORDER_TYPE
AND OL.LINE_NUMBER = ILL.ORDER_LINE_NUMBER),0)) 'TOTAL_PRICE_INVOICED',
(SELECT M.RETURN_ADVICE_NUMBER
FROM MOVEMENT M (NOLOCK)
WHERE M.ORDER_NUMBER = ILL.ORDER_NUMBER
AND M.ORDER_TYPE = ILL.ORDER_TYPE
AND M.LINE_NUMBER = ILL.ORDER_LINE_NUMBER
AND M.COMPLETION_DATE = ILL.COMPLETION_DATE
AND M.MOVEMENT_FROM IN ('STO', 'INS')
AND M.MOVEMENT_TO = 'SUP' AND M.DATE_TIME = (SELECT MAX(M2.DATE_TIME)
FROM MOVEMENT M2 (NOLOCK) WHERE M2.ORDER_NUMBER = M.ORDER_NUMBER
AND M2.ORDER_TYPE = M.ORDER_TYPE
AND M2.LINE_NUMBER = M.LINE_NUMBER
AND M2.COMPLETION_DATE = M.COMPLETION_DATE
AND M2.MOVEMENT_FROM = M.MOVEMENT_FROM
AND M2.MOVEMENT_TO = M.MOVEMENT_TO)) 'RETURN_ADVICE',
ILL.ORDER_NUMBER, ILL.ORDER_LINE_NUMBER,
CONVERT(VARCHAR, ILL.COMPLETION_DATE , 103) ,
ILL.COMMENTS, (TCP.TAX_PERCENTAGE / 100),
CONVERT(CHAR(11), ILL.CAPTURE_DATE, 100) + ' ' + CONVERT(VARCHAR, ILL.CAPTURE_DATE, 114),
CONVERT(CHAR(11), ILL.COMPLETION_DATE, 100) + ' ' + CONVERT(VARCHAR, ILL.COMPLETION_DATE, 114) ,
ILL.LINK_CLASS,
ILL.EXEMPT_INDICATOR,
ILL.EXEMPT_AMOUNT,
ILL.TAX_REASON_CODE,
ILL.SETTLEMENT_TERM
FROM INVOICE_LINE_LINK ILL (NOLOCK)
JOIN ORDER_LINE OL (NOLOCK)
ON ILL.ORGANISATION_CODE = ILL.ORGANISATION_CODE
AND ILL.ORDER_NUMBER = OL.ORDER_NUMBER
AND ILL.ORDER_LINE_NUMBER = OL.LINE_NUMBER
JOIN TAX_CODE_PERCENTAGE TCP
ON TCP.TAX_CODE = ILL.TAX_CODE
AND TCP.EFFECTIVE_DATE = (SELECT MAX(TCP1.EFFECTIVE_DATE)
FROM TAX_CODE_PERCENTAGE TCP1
WHERE TCP1.TAX_CODE = ILL.TAX_CODE AND TCP1.EFFECTIVE_DATE < ILL.CAPTURE_DATE) JOIN #TEMP
ON ILL.ORDER_NUMBER = #TEMP.ORDER_NUMBER AND ILL.ORDER_TYPE = #TEMP.ORDER_TYPE AND ILL.ORDER_LINE_NUMBER = #TEMP.LINE_NUMBER AND ILL.COMPLETION_DATE = #TEMP.COMPLETION_DATE WHERE ILL.ORGANISATION_CODE = 'AA01' AND ILL.STATUS = 'A' AND ILL.TYPE_OF_LINK = 'P' AND ILL.CAPTURE_DATE = (SELECT MAX(ILL2.CAPTURE_DATE) FROM INVOICE_LINE_LINK ILL2 (NOLOCK) WHERE ILL2.ORDER_NUMBER = ILL.ORDER_NUMBER AND ILL2.ORDER_TYPE = ILL.ORDER_TYPE AND ILL2.ORDER_LINE_NUMBER = ILL.ORDER_LINE_NUMBER AND ILL2.COMPLETION_DATE = ILL.COMPLETION_DATE AND ILL2.STATUS = 'A' AND
ILL2.TYPE_OF_LINK = ILL.TYPE_OF_LINK )
ORDER BY ILL.CAPTURE_DATE DESC
DROP TABLE #TEMP

I'm glad someone else took the time to read through your unformatted code, as I really didn't have the time. A good suggestion, especially when posting such lengthy SQL code, make sure you have formatted it well with good use of white space and indention.

john.arnott SSChampion Points: 11882 More actions · Answer 5

Florian Reischl (4/30/2009)
Err... did you read Steve's post correct?
You have to specify a name for the column if you use SELECT INTO.
... which creates a new table and requires a name for each column...
Greets
Flo

In other words, do not remove the comment, but rather remove the "--" that makes it a comment. Change your third line from CONVERT(VARCHAR, M.COMPLETION_DATE , 103) --As COMPLETION_DATE

orCONVERT(VARCHAR, M.COMPLETION_DATE , 103)

to actually use the alias name:CONVERT(VARCHAR, M.COMPLETION_DATE , 103) As COMPLETION_DATE

Notice also that by using "code" tags (available in the portion of the screen to the left of the input panel), it's a lot easier to see the different elements of the SQL. Do take a look at the links in Lynn's signature area at the end of his post.

Paul White SSC Guru Points: 150468 More actions · Answer 6

Lynn Pettis (4/30/2009)
lungy (4/29/2009)
...heaps of sql...
I'm glad someone else took the time to read through your unformatted code, as I really didn't have the time. A good suggestion, especially when posting such lengthy SQL code, make sure you have formatted it well with good use of white space and indention.

Did you have to quote it all again? :blink:

My eyes!!! :pinch:

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Paul White (4/30/2009)
Lynn Pettis (4/30/2009)
lungy (4/29/2009)
...heaps of sql...
I'm glad someone else took the time to read through your unformatted code, as I really didn't have the time. A good suggestion, especially when posting such lengthy SQL code, make sure you have formatted it well with good use of white space and indention.
Did you have to quote it all again? :blink:
My eyes!!! :pinch:
Paul

Yes, for emphasis. 😛

lungy-1072376 Valued Member Points: 60 More actions · Answer 8

lungy-1072376

Valued Member

Points: 60

May 7, 2009 at 6:22 am

#990275

okay will do that next time

Thanx guyz