Create view error with Declare

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
vuongduongquoc
User
Posts: 133

Create view error with Declare

Post by vuongduongquoc »

Hi. I have this code and please help me how to add it to sql view, or where should i put the Declare ... Set part in Phpmaker?

DECLARE @taikhoanghinocochitiet nvarchar(50);
SET @taikhoanghinocochitiet = '112112'
SELECT
dbo.tbl_nks_Phieuthu.tkghinochitiet AS tkghinocochitiet,
'TKNO' AS phieu,
dbo.tbl_nks_Phieuthu.id,
dbo.tbl_nks_Phieuthu.idtaikhoannganhang,
CONVERT ( DATE, dbo.tbl_nks_Phieuthu.thoigian ) AS ngaythuchi,
CONVERT ( DATE, dbo.tbl_nks_Phieuthu.ngayhachtoan ) AS ngayhachtoan,
dbo.tbl_nks_Phieuthu.sotienhachtoan AS sotienhachtoan,
dbo.tbl_nks_Phieuthu.tkghino AS taikhoan,
dbo.tbl_nks_Phieuthu.nguoinop AS nguoinopguoinhan,
dbo.tbl_nks_Phieuthu.noidung,
dbo.tbl_nks_Phieuthu.isgdcn,
dbo.tbl_nks_Phieuthu.doichieusaoke,
dbo.tbl_nks_Phieuthu.ischeck,
dbo.tbl_nks_Phieuthu.isdauky,
dbo.tbl_nks_Phieuthu.lock
FROM
dbo.tbl_nks_Phieuthu
WHERE
( dbo.tbl_nks_Phieuthu.ngayhachtoan >= ( SELECT tbl_nks_ngaybaocao.tungay FROM tbl_nks_ngaybaocao WHERE tbl_nks_ngaybaocao.id= 1 ) )
AND ( dbo.tbl_nks_Phieuthu.ngayhachtoan <= ( SELECT tbl_nks_ngaybaocao.denngay FROM tbl_nks_ngaybaocao WHERE tbl_nks_ngaybaocao.id= 1 ) )
AND ( dbo.tbl_nks_Phieuthu.tkghinochitiet = @taikhoanghinocochitiet )


arbei
User
Posts: 9286

Post by arbei »

You should remove DECLARE and use only the SELECT statement to create a view. Search criteria should be passed to the List page of the view as URL parameters.


vuongduongquoc
User
Posts: 133

Post by vuongduongquoc »

in a Custom File i can pass data from Usertable to the Select SQL as bellow.

<div class="row">
        <?php
    $sql = "SELECT
	dbo.tbl_nks_Phieuthu.tkghinochitiet AS tkghinocochitiet,
	'TKNO' AS phieu,
	dbo.tbl_nks_Phieuthu.id,
	dbo.tbl_nks_Phieuthu.idtaikhoannganhang,
	CONVERT ( DATE, dbo.tbl_nks_Phieuthu.thoigian ) AS ngaythuchi,
	CONVERT ( DATE, dbo.tbl_nks_Phieuthu.ngayhachtoan ) AS ngayhachtoan,
	dbo.tbl_nks_Phieuthu.sotienhachtoan AS sotienhachtoan,
	dbo.tbl_nks_Phieuthu.tkghino AS taikhoan,
	dbo.tbl_nks_Phieuthu.nguoinop AS nguoinopguoinhan,
	dbo.tbl_nks_Phieuthu.noidung,
	dbo.tbl_nks_Phieuthu.isgdcn,
	dbo.tbl_nks_Phieuthu.doichieusaoke,
	dbo.tbl_nks_Phieuthu.ischeck,
	dbo.tbl_nks_Phieuthu.isdauky,
	dbo.tbl_nks_Phieuthu.lock 
FROM
	dbo.tbl_nks_Phieuthu 
WHERE
	( dbo.tbl_nks_Phieuthu.ngayhachtoan >= ( SELECT tbl_nks_ngaybaocao.tungay FROM tbl_nks_ngaybaocao WHERE tbl_nks_ngaybaocao.id= 1 ) ) 
	AND ( dbo.tbl_nks_Phieuthu.ngayhachtoan <= ( SELECT tbl_nks_ngaybaocao.denngay FROM tbl_nks_ngaybaocao WHERE tbl_nks_ngaybaocao.id= 1 ) )
	AND ( dbo.tbl_nks_Phieuthu.tkghinochitiet = (SELECT filtertaikhoan FROM tbl_nks_filtertaikhoansocai WHERE User_EmployeeID = '".CurrentUserInfo("EmployeeID")."') )" ;
    Write(ExecuteHtml($sql, ["fieldcaption" => true, "tablename" => ["tbl_nks_Phieuthu","tbl_nks_Phieuthu"]]));
	?>

  </div>

BUT in a VIEW. How can i pass data from Usertable to WHERE clause?

SELECT
	dbo.tbl_nks_Phieuthu.tkghinochitiet AS tkghinocochitiet,
	'TKNO' AS phieu,
	dbo.tbl_nks_Phieuthu.id,
	dbo.tbl_nks_Phieuthu.idtaikhoannganhang,
	CONVERT ( DATE, dbo.tbl_nks_Phieuthu.thoigian ) AS ngaythuchi,
	CONVERT ( DATE, dbo.tbl_nks_Phieuthu.ngayhachtoan ) AS ngayhachtoan,
	dbo.tbl_nks_Phieuthu.sotienhachtoan AS sotienhachtoan,
	dbo.tbl_nks_Phieuthu.tkghino AS taikhoan,
	dbo.tbl_nks_Phieuthu.nguoinop AS nguoinopguoinhan,
	dbo.tbl_nks_Phieuthu.noidung,
	dbo.tbl_nks_Phieuthu.isgdcn,
	dbo.tbl_nks_Phieuthu.doichieusaoke,
	dbo.tbl_nks_Phieuthu.ischeck,
	dbo.tbl_nks_Phieuthu.isdauky,
	dbo.tbl_nks_Phieuthu.lock 
FROM
	dbo.tbl_nks_Phieuthu 
WHERE
	( dbo.tbl_nks_Phieuthu.ngayhachtoan >= ( SELECT tbl_nks_ngaybaocao.tungay FROM tbl_nks_ngaybaocao WHERE tbl_nks_ngaybaocao.id= 1 ) ) 
	AND ( dbo.tbl_nks_Phieuthu.ngayhachtoan <= ( SELECT tbl_nks_ngaybaocao.denngay FROM tbl_nks_ngaybaocao WHERE tbl_nks_ngaybaocao.id= 1 ) )
	AND ( dbo.tbl_nks_Phieuthu.tkghinochitiet = (SELECT filtertaikhoan FROM tbl_nks_filtertaikhoansocai WHERE User_EmployeeID = '".CurrentUserInfo("EmployeeID")."') )" ; 

Please help to fix this.


arbei
User
Posts: 9286

Post by arbei »

arbei wrote:

Search criteria should be passed to the List page of the view as URL parameters.

Then you may simply get it by $_GET and put the value in your SQL.


Post Reply