WHERE clause is not working properly. Problem with Date Format.

[leon_laikan]

Dear All,

You may have seen something resembling this post in the past days. It's about dynamic pivot and the thread was closed but unanswered, never mind.

But my question here is about a different aspect of the problem.

I finally managed to make dynamic pivot work. See the query below which works perfectly:

DECLARE @listCol VARCHAR(2000)

DECLARE @query VARCHAR(4000)

SELECT @listCol = 
STUFF
(
( SELECT DISTINCT   '],['    +    CAST(month(T0.RefDate) AS varchar) 

FROM  JDT1 T0
FOR XML PATH('')

), 1, 2, ' ') +   ']'

SET @query =

'SELECT * FROM 

(SELECT Account, month (T0.RefDate) Month , Debit 

FROM JDT1 T0

------stops working if we add the WHERE clause just below this line----------------



GROUP BY Account, RefDate, Debit 
) S

PIVOT 
(
Sum(Debit) 
FOR Month IN ('+@listCol+')
) AS pvt'

EXECUTE (@query)

But if I add the foll. WHERE clause just below the dotted lines, it stops working. It appears to be a problem on date formatting.

Can anybody help me convert the date properly?

WHERE T0.RefDate >= '[%1]' and T0.RefDate <= '[%2]'

Error Message is:

Incorrect Syntax near '20110101'

if I input 01.01.11 in Selection Criteria (1 Jan 2011)

Thanks

Leon Lai

[jbrotto · Accepted solution]

Date has to be manually entered into the query when using sub queries, most views and some joins.

[leon_laikan · Accepted solution]

Hi jbrotto,

Thanks for your answer.

I have written a few queries involving date variables \[%1] , \[%2] etc passed to subqueries which worked, although these were more straightforward cases.

This is why I have some hope that this query might also work.

If we avoid subqueries, then the other alternative might be using temporary tables. Do you have any idea?

Best Regards,

Leon

[jbrotto · Accepted solution]

You could from the back-end use variables in SQL server and just enter new values as the variables would be on the top of your query. It is a relatively easy solution that I use. I am not sure if b1 could do the same. Also you could use crystal reports to query and it will be a beautiful report. My boss loves but takes time to make a report both coding and design. I am hoping in version 9 that many of the limitations get removed as I find many of the dynamics ERPs could do complex queries easily once the connections and profiles are setup.

I am currently reading Gordon Du's book master SQL queries in SAP BUSINESS ONE. This might be useful. very well written. There were features and functions that I did not know about which are very useful and unique to B1. You should give it a try.

[leon_laikan · Accepted solution]

Hi jbrotto

Thanks for your concern.

Yes, Gordon's book is great!. I learned all my basic sql principles from that book and developed great queries with help from the book and from the forum.

Don't worry about my query. I know it's a hard nut to crack. Very few people have replied to my previous thread.

But since dynamic pivot gives wonderful results, I will continue the struggle until I crack the nut. I know it can be done.

Unfortunately, although Gordon's book explains pivot clearly, it does not mention dynamic pivot

Best Regards,

Leon

[Former Member]

Hi Leon,

You need to declare those two dates as parameter too. [%\] variable can not be used here.

Thanks,

Gordon

[leon_laikan]

Hi Gordon,

When I add the following lines:

declare @refdt1 date
set @refdt =
/*select 1 from jdt1 t0 where t0.RefDate*/ '[%1]'

declare @refdt2 date
set @refdt =
/*select 1 from jdt2 t0 where t0.RefDate*/ '[%2]'

WHERE T0.RefDate >= @refdt1 and T0.RefDate <= @refdt2
 

... the error message is now:

Must declare the scalar variable @refdt1

Note: Before adding these lines, the query works perfectly, and returns totals from the whole database (with dynamically generated column headings!)

Thanks

Leon Lai

.

.

.

.

-

---

AMENDED QUERY:

declare @refdt1 date
set @refdt1 =
/*select 1 from jdt1 t0 where t0.RefDate*/ '[%1]'
------------------------
declare @refdt2 date
set @refdt2 =
/*select 1 from jdt1 t0 where t0.RefDate*/ '[%2]'

--------- I inserted the 6 lines above ---------------------



DECLARE @listCol VARCHAR(2000)
 
DECLARE @query VARCHAR(4000)
 
SELECT @listCol = 
STUFF
(
( SELECT DISTINCT   '],['    +    CAST(month(T0.RefDate) AS varchar) 
 
FROM  JDT1 T0
FOR XML PATH('')
 
), 1, 2, ' ') +   ']'
 
----------
SET @query =
 
'SELECT * FROM 
 
(SELECT Account, month (T0.RefDate) Month , Debit 
 
FROM JDT1 T0
 
------------------- I add the WHERE clause below --------------------

 WHERE T0.RefDate >= @refdt1 and T0.RefDate <= @refdt2
 
 
GROUP BY Account, RefDate, Debit 
) S
 
PIVOT 
(
Sum(Debit) 
FOR Month IN ('+@listCol+')
) AS pvt'
 
------------------------------------------
EXECUTE (@query)

Edited by: LEONLAI on Oct 21, 2011 2:36 PM

[Former Member]

HI,

change datetime instead of date and try.......

Regards,

Priya

[leon_laikan]

Hi Priya,

Thanks for your reply.

Unfortunately, datetime also gives same error message.

Best Regards,

Leon

[Former Member]

Change '[%1\]' to [%1\] to try.

[leon_laikan]

Hi Gordon,

Same error message

Thanks

Leon