Problem with Query

[former_member591805]

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

[former_member187989 · Accepted solution]

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

[former_member591805 · Accepted solution]

I get error

 " (SEWSY)

now.

Thanks for the quick response!

[lucas_fischer · Accepted solution]

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

[former_member591805 · Accepted solution]

Brilliant that works. How would I make it filter for a range of dates. I.e. 01/07/09 to 01/08/09?

Incidentally, what does the error I was getting mean?

Thanks

John

[lucas_fischer · Accepted solution]

John,

I'm not entirely sure what the error meant, I just cleaned up the code a bit to more resemble the syntax that B1 uses.

As for your second question, like this

WHERE T0.CreateDate >= [%0] and T0.CreateDate <= [%1]

hope this helps,

Lucas

[former_member591805 · Accepted solution]

Lucas, thanks again this worked perfectly!

[Former Member]

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