on 08-04-2009 4:03 PM
Hi All,
I have been trying to design a query and have come across a very annoying problem. My query below works fine until I try to change the WHERE T0.CreateDate statement to ask the user for an input date.
SELECT DISTINCT
T0.CardCode, T0.CardName, CASE WHEN t0.cardtype = 'L' THEN 'Lead' WHEN t0.cardtype = 'C' THEN 'Customer' WHEN
t0.cardtype = 'S' THEN 'Supplier'
END AS , T0.CreateDate, T0.Currency, dbo.OCRG.GroupName, T0.Address, T0.Block,
T0.City, T0.County, T0.Country, T0.ZipCode, T0.State1, T0.U_BNVATNo AS , CASE WHEN t0.vatstatus = 'N' THEN
'Exempted' WHEN t0.vatstatus = 'Y' THEN 'Liable' WHEN t0.vatstatus = 'E' THEN 'EU' END AS ,
bo.OCTG.PymntGroup, CASE WHEN T0.QryGroup1 = 'Y' THEN 'Y' END AS , CASE WHEN T0.QryGroup2 = 'Y' THEN 'Y' END
AS , CASE WHEN T0.QryGroup3 = 'Y' THEN 'Y' END AS , CASE WHEN T0.QryGroup4 = 'Y' THEN 'Y' END AS ,
CASE WHEN T0.QryGroup5 = 'Y' THEN 'Y' END AS Decorative, CASE WHEN T0.QryGroup6 = 'Y' THEN 'Y' END AS Labelling
FROM dbo.OCRD AS T0 INNER JOIN
dbo.OCRG ON T0.GroupCode = dbo.OCRG.GroupCode INNER JOIN
dbo.CRD1 AS T1 ON T0.CardCode = T1.CardCode INNER JOIN
dbo.OCTG ON T0.GroupNum = dbo.OCTG.GroupNum
WHERE T0.CreateDate <= '01.08.09'
When I change the last line to
WHERE T0.CreateDate <= '[%0]'
with or without ' ' marks. I get an error saying
1). [ODBC SQL Server Driver][SQL Server]Incorrect syntax near '0'. 2).
[ODBC SQL Server Driver][SQL Server]Statement '' (SEWSY)(s) could not be prepared.
Can anyone help point out what I have done wrong please?
Thanks In Advance
John
Try this query
Declare @dt as datetime
Set @dt =/*Select Z.CreateDate from OCRD Z Where Z.CreateDate=*/'[%0]'
SELECT DISTINCT
T0.CardCode, T0.CardName, CASE WHEN t0.cardtype = 'L' THEN 'Lead' WHEN t0.cardtype = 'C' THEN 'Customer' WHEN
t0.cardtype = 'S' THEN 'Supplier' END AS [BP Type], T0.CreateDate, T0.Currency, dbo.OCRG.GroupName, T0.Address, T0.Block,
T0.City, T0.County, T0.Country, T0.ZipCode, T0.State1, T0.U_BNVATNo AS [VAT Number], CASE WHEN t0.vatstatus = 'N' THEN
'Exempted' WHEN t0.vatstatus = 'Y' THEN 'Liable' WHEN t0.vatstatus = 'E' THEN 'EU' END AS [VAT Status],
bo.OCTG.PymntGroup, CASE WHEN T0.QryGroup1 = 'Y' THEN 'Y' END AS [GBP(£)], CASE WHEN T0.QryGroup2 = 'Y' THEN 'Y' END
AS [EURO(E)], CASE WHEN T0.QryGroup3 = 'Y' THEN 'Y' END AS [US($)], CASE WHEN T0.QryGroup4 = 'Y' THEN 'Y' END AS [HK($)],
CASE WHEN T0.QryGroup5 = 'Y' THEN 'Y' END AS Decorative, CASE WHEN T0.QryGroup6 = 'Y' THEN 'Y' END AS Labelling
FROM dbo.OCRD AS T0 INNER JOIN
dbo.OCRG ON T0.GroupCode = dbo.OCRG.GroupCode INNER JOIN
dbo.CRD1 AS T1 ON T0.CardCode = T1.CardCode INNER JOIN
dbo.OCTG ON T0.GroupNum = dbo.OCTG.GroupNum
WHERE T0.CreateDate <= @dt
Jeyakanthan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Maybe this instead,
SELECT DISTINCT
T0.CardCode, T0.CardName, CASE WHEN t0.cardtype = 'L' THEN 'Lead' WHEN t0.cardtype = 'C' THEN 'Customer' WHEN
t0.cardtype = 'S' THEN 'Supplier' END AS [BP Type], T0.CreateDate, T0.Currency, T2.GroupName, T0.Address, T0.Block,
T0.City, T0.County, T0.Country, T0.ZipCode, T0.State1, T0.U_BNVATNo AS [VAT Number], CASE WHEN t0.vatstatus = 'N' THEN
'Exempted' WHEN t0.vatstatus = 'Y' THEN 'Liable' WHEN t0.vatstatus = 'E' THEN 'EU' END AS [VAT Status],
T3.PymntGroup, CASE WHEN T0.QryGroup1 = 'Y' THEN 'Y' END AS [GBP(£)], CASE WHEN T0.QryGroup2 = 'Y' THEN 'Y' END
AS [EURO(E)], CASE WHEN T0.QryGroup3 = 'Y' THEN 'Y' END AS [US($)], CASE WHEN T0.QryGroup4 = 'Y' THEN 'Y' END AS [HK($)],
CASE WHEN T0.QryGroup5 = 'Y' THEN 'Y' END AS Decorative, CASE WHEN T0.QryGroup6 = 'Y' THEN 'Y' END AS Labelling
FROM OCRD T0 INNER JOIN
OCRG T2 ON T0.GroupCode = T2.GroupCode INNER JOIN
CRD1 T1 ON T0.CardCode = T1.CardCode INNER JOIN
OCTG T3 ON T0.GroupNum = T3.GroupNum
WHERE T0.CreateDate <= [%0]
regards,
Lucas
Try this one:
Declare @dt as datetime
Set @dt =/*Select Z.CreateDate from OCRD Z Where Z.CreateDate=*/'[%0]'
SELECT DISTINCT T0.CardCode, T0.CardName, CASE WHEN t0.cardtype = 'L' THEN 'Lead' WHEN t0.cardtype = 'C' THEN 'Customer' WHEN
t0.cardtype = 'S' THEN 'Supplier' END AS [BP Type], T0.CreateDate, T0.Currency, T2.GroupName, T0.Address, T0.Block,
T0.City, T0.County, T0.Country, T0.ZipCode, CASE WHEN t0.vatstatus = 'N' THEN
'Exempted' WHEN t0.vatstatus = 'Y' THEN 'Liable' WHEN t0.vatstatus = 'E' THEN 'EU' END AS [VAT Status],
T4.PymntGroup, CASE WHEN T0.QryGroup1 = 'Y' THEN 'Y' END AS [GBP(£)], CASE WHEN T0.QryGroup2 = 'Y' THEN 'Y' END
AS [EURO(E)], CASE WHEN T0.QryGroup3 = 'Y' THEN 'Y' END AS [US($)], CASE WHEN T0.QryGroup4 = 'Y' THEN 'Y' END AS [HK($)],
CASE WHEN T0.QryGroup5 = 'Y' THEN 'Y' END AS Decorative, CASE WHEN T0.QryGroup6 = 'Y' THEN 'Y' END AS Labelling
FROM dbo.OCRD T0 INNER JOIN
dbo.OCRG T2 ON T0.GroupCode = T2.GroupCode INNER JOIN
dbo.CRD1 T1 ON T0.CardCode = T1.CardCode INNER JOIN
dbo.OCTG T4 ON T0.GroupNum = T4.GroupNum
WHERE T0.CreateDate <= @dt
I have omitted your UDF to test. You may add it when it works. The date format can be 'MM/DD/YYYY'.
The " (SEWSY) error means query could not be prepared. SEWSY is a table name in SBO-COMMON.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
95 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.