Unknow column reference in Prepare Statement

(10 posts) (2 voices)

Tags:

No tags yet.

  1. spyhunter88, Member

    Hi,

    I am trying to use dbr.editable in cross tab report. And from the current version I run, it said "no support", the same result for trying dbr.report or dbr.colstyle. That let me come to following solution:
    I use prepare statement (from MySQL) to generate crosstab, also use while - do to loop through all cross tab column and set the dbr.report, dbr.colstyle.
    But after the report works, the dbr.editable, dbr.report ... say "Unknown column reference" for all column, except some. I dont know why.

    DROP TEMPORARY TABLE IF EXISTS tblTargetSale;

    -- this code will create all cross tab string SET @sql1 = null; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(case when DATE(Invoice_Date) =''', DATE(Invoice_Date), ''' then Sale end) AS Day.', LPAD(DAYOFMONTH(Invoice_Date),2,'0'), '[D', DAYOFMONTH(Invoice_Date) ,']' )) INTO @sql1 FROM Sales.target_all WHERE Year = inYear AND Month = inMonth ;

    SET @sql1 = IFNULL(@sql1, '''0'' as None '); -- SET @sql1 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tblTargetSale AS ( SELECT s.Com as Company[Company], s.BizLine as BU[BU], s.Center as Center[Center], s.Dept as Dept[Dept], s.Geo as Geo[Geo], SUM(Sale) as Month[xMonth],', @sql1 , 'FROM Sales.target_all s WHERE Year=', inYear, ' AND Month=', inMonth, ' GROUP BY s.Com, s.Bizline, s.Center, s.Dept, s.Geo)') ;

    -- this is for debug SELECT @sql1;

    PREPARE stm FROM @sql1; EXECUTE stm;

    SELECT * FROM tblTargetSale;

    The Company and Center don't show 'Unknown', but nothing happen, no editable, no link, no style effect. Other column like xMonth, BU. Dept, Geo all say 'Unknown'. The same result when I dont use Temporary table (tblTargetSale) and the @sql1 is (SELECT .... FROM ... GROUP BY ...) query.

    I saw the @sql1 after generated:
    CREATE TEMPORARY TABLE IF NOT EXISTS tblTargetSale AS ( SELECT s.Com as Company[Company], s.Bizline as BU[BU], s.Center as Center[Center], s.Dept as Dept[Dept], s.Geo as Geo[Geo], SUM(Sale) as Month[xMonth],SUM(case when DATE(Invoice_Date) ='2014-06-02' then Sale end) AS Day.02[D2],SUM(case when DATE(Invoice_Date) ='2014-06-04' then Sale end) AS Day.04[D4],SUM(case when DATE(Invoice_Date) ='2014-06-01' then Sale end) AS Day.01[D1]FROM Sales.target_all s WHERE Year=2014 AND Month=6 GROUP BY s.Com, s.Bizline, s.Center, s.Dept, s.Geo)

    Of course main column doesn't work, the cross tab column can't not too.

    Thanks,

  2. myDBR Team, Key Master

    Hi,
    we did not quite understand what was the problem. Where do you see the 'Unknown' appearing?

    If you run the query with &export=sql added what is the result?

    What is the purpose of using dynamic SQL as it looks that you could do the same with single query using dbr.crosstab?

    --
    myDBR Team

  3. spyhunter88, Member

    Hi,

    1. I see the error message in top of the report, the table below appears, but link-report does not work cause the error 'Unknown column reference: xMonth'.

    2. The &export=sql result here:
    select 'dbr.editable', '[Center]', 'sp_DBR_Sales_Target_edit_month', 'inCom=Company', 'inBU=BU', 'inCenter=Center', 'inDept=""', 'inGeo=""', 'inYear="2014"', 'inMonth="6"', 'inValue="2500"';

    select 'dbr.footerstyle', 'background-color: #F7F2E0;font-weight: normal;color:#0101DF;'; select 'dbr.colstyle', 'Company', '[background-color: #CCEEFF;font-weight: bold;]%.0f'; select 'dbr.colstyle', 'Center', '[background-color: #CCEEFF;font-weight: bold;]%.0f';

    select 'CREATE TEMPORARY TABLE IF NOT EXISTS tblTargetSale AS ( SELECT s.Com as Company[Company], s.Bizline as BU[BU], s.Center as Center[Center], s.Dept as Dept[Dept], s.Geo as Geo[Geo], SUM(Sale) as Month[xMonth],SUM(case when DATE(Invoice_Date) =\'2014-06-02\' then Sale end) AS Day.02[D2],SUM(case when DATE(Invoice_Date) =\'2014-06-04\' then Sale end) AS Day.04[D4],SUM(case when DATE(Invoice_Date) =\'2014-06-01\' then Sale end) AS Day.01[D1]FROM Sales.target_all s WHERE Year=2014 AND Month=6 GROUP BY s.Com, s.Bizline, s.Center, s.Dept, s.Geo)' as '@sql1';

    select 'F9' as 'Company[Company]', 'Nuke' as 'BU[BU]', 'Nuke' as 'Center[Center]', 'Vert' as 'Dept[Dept]', 'HN' as 'Geo[Geo]', 31151 as 'Month[xMonth]', 10000 as 'Day.02[D2]', 12101 as 'Day.04[D4]', 9050 as 'Day.01[D1]';

    3. I use this because the linked-report and colstyle does not work with crosstab. I tried then myDBR appear only notice about not support. I don't remember the version I checked but not too far.

    Back to my main purpose, I check for another report and it work good, the linked-report and colstyle effects successful for prepare statement. But I dont know why it does not work with this code above. And I dont know why the Company and Center does not error (but does not work, no linked report).

    Thanks,

  4. myDBR Team, Key Master

    Again
    there is no reason for using such a complex way of creating crosstabs. Linked-report and colstyle does work with standard crosstabs just fine. If you have problem with that, just post an question about that.

    In general, using dynamic SQL in reports is not recommended. It just makes the report code much harder to write, understand and to maintain.

    As for your problem, looks like you do not set the column names inside quotation marks. Instead of Month[xMonth] you should use "Month[xMonth]".

    --
    myDBR Team

  5. spyhunter88, Member

    Hi,

    I also choose try my best first, before request other because of time. Also, I tried with double quote ", grave ` or single quote ' and get the same result.

    Thanks,

  6. spyhunter88, Member

    Sorry, I make my big mistake is showing the @sql1 before the result set, so they don't find any BU as well. This topic can be deleted. Haha. ::D

    Also, other work fine. A little complex but I can handle it my self. Of course, you should add report-link and other settings for crosstab. It will be nice.

    Thanks,

  7. myDBR Team, Key Master

    Of course, you should add report-link and other settings for crosstab.

    Could you clarify what do you mean by this. Crosstab does have full support for report linking and editing.

    --
    myDBR Team

  8. spyhunter88, Member

    No, I mean for column in crosstab. In this example, my report show total Sales in each Quarter of Year.

    select 'dbr.report', 'sp_DBR_Details', 'new_window', '[Q.01]'; select 'dbr.colstyle', 'Q.01', mydbr_style('mystyle');

    select 'dbr.crosstab', 'Quarter';

    select Com, Bizline, concat(Quarter,'[Q',LPAD(Quarter,2,'0'),']') as Quarter, SUM(Sales) as Sales[Sales] from Sales.Sales_report GROUP BY Com, Bizline, Quarter;

    This '[Q.01]' mean the Quarter 01, I added it to the end of Quarter value, so when use crosstab, it go to the header column. The I use it in dbr.report. That's what I've tried, but the report said "Dont support reportlink in crosstab". That is an example, in most case, I have Q.02, Q.03 .... And I want to set style by odd and even Quarter.

    Now, I think clearly and see can not generate report link/colstyle like that because the value column can be Sales and Qty, not only Sales. But, how can I do if I face this situation again? Set report link and style for individual quarter in cross tab. How can I take the quarter parameter?

    Thanks,

  9. myDBR Team, Key Master

    There is no need to create the column references in so complex way.

    Also, note that you are trying to set the linked report to crosstab column. The crosstab column is the header. You should set it to the data. This is the reason for the message.

    Go through the documentation and take a look at the demos here. It will make your life much simpler.

    --
    myDBR Team

  10. spyhunter88, Member

    Thanks too much,


Reply

You must log in to post.