สอบถามครับ ใช่ CodeIgniter เขียน
ผมส่ง Data มาที่ model ได้ค่ามาแล้ว แต่ไป ยัดค่าใส่ query pivot (ปัญหาคือ data ที่ใส่ query มาไม่ครบ)
ข้อมูลที่จะใส่ query
query error data มาไม่ครบ
Code (SQL)
public function FSaMPRGDataTable($paData, $paDataDpcDisCodeYSet){
$tDpcDisCodeYSet = $paDataDpcDisCodeYSet;
print_r($tDpcDisCodeYSet);
$nLngID = $paData['FNLngID'];
$tSQL = " SELECT PPV.*,
PLL.FTDisName,
CASE PLC.FTDisGroup
WHEN 1 THEN 'ITEM'
WHEN 2 THEN 'PROMOTION'
WHEN 3 THEN 'SUB'
ELSE 'N/A'
END AS FTDisGroup,
CASE WHEN PLC.FTDisStaPrice = 1 THEN 'Full Price/Set Price' ELSE 'Net Peice' END AS FTDisStaPrice
FROM (
SELECT
--แทนค่าตัวแปร tYColumnShow ตรงนี้
FTDpcDisCodeX,
FTDpcDisCodeY1,
FTDpcStaAlw1,
FTDpcDisCodeY2,
FTDpcStaAlw2,
FTDpcDisCodeY3,
FTDpcStaAlw3,
FTDpcDisCodeY4,
FTDpcStaAlw4,
FTDpcDisCodeY5,
FTDpcStaAlw5,
FTDpcDisCodeY6,
FTDpcStaAlw6
FROM(
SELECT FTDpcDisCodeX,
col + CAST(seq AS VARCHAR(10)) AS col,
value
FROM
(
SELECT FTDpcDisCodeX,
FTDpcStaAlw,
FTDpcDisCodeY,
ROW_NUMBER() OVER(PARTITION BY FTDpcDisCodeX
ORDER BY FTDpcDisCodeX) seq
FROM TPSTDiscPolicy
) d
CROSS APPLY
(
SELECT 'FTDpcStaAlw',
CAST(FTDpcStaAlw AS VARCHAR(20))
UNION ALL
SELECT 'FTDpcDisCodeY',
FTDpcDisCodeY
) c(col, value)
) src PIVOT(MAX(value) FOR col IN(
$tDpcDisCodeYSet
)) piv ) PPV
INNER JOIN TSysDisPolicy PLC ON PPV.FTDpcDisCodeX = PLC.FTDisCode
INNER JOIN TSysDisPolicy_L PLL ON PPV.FTDpcDisCodeX = PLL.FTDisCode AND PLL.FNLngID = $nLngID
";
$oQuery = $this->db->query($tSQL);
}
public function FSaMPRGDataTable($paData, $paDataDpcDisCodeYSet, $paDataYColumnShow){
// $tDpcDisCodeYSet = $paDataDpcDisCodeYSet;
// $tDpcDisCodeYSet = str_replace(",", "','", $tDpcDisCodeYSet);
// $tDpcDisCodeYSet = str_replace($tDpcDisCodeYSet, "'".$tDpcDisCodeYSet."'", $tDpcDisCodeYSet);
// print_r($paDataDpcDisCodeYSet);
$nLngID = $paData['FNLngID'];
$tSQL = " SELECT PPV.*,
PLL.FTDisName,
CASE PLC.FTDisGroup
WHEN 1 THEN 'ITEM'
WHEN 2 THEN 'PROMOTION'
WHEN 3 THEN 'SUB'
ELSE 'N/A'
END AS FTDisGroup,
CASE WHEN PLC.FTDisStaPrice = 1 THEN 'Full Price/Set Price' ELSE 'Net Peice' END AS FTDisStaPrice
FROM (
SELECT
--แทนค่าตัวแปร tYColumnShow ตรงนี้
FTDpcDisCodeX,
FTDpcDisCodeY1,
FTDpcStaAlw1,
FTDpcDisCodeY2,
FTDpcStaAlw2,
FTDpcDisCodeY3,
FTDpcStaAlw3,
FTDpcDisCodeY4,
FTDpcStaAlw4,
FTDpcDisCodeY5,
FTDpcStaAlw5,
FTDpcDisCodeY6,
FTDpcStaAlw6
FROM(
SELECT FTDpcDisCodeX,
col + CAST(seq AS VARCHAR(10)) AS col,
value
FROM
(
SELECT FTDpcDisCodeX,
FTDpcStaAlw,
FTDpcDisCodeY,
ROW_NUMBER() OVER(PARTITION BY FTDpcDisCodeX
ORDER BY FTDpcDisCodeX) seq
FROM TPSTDiscPolicy
) d
CROSS APPLY
(
SELECT 'FTDpcStaAlw',
CAST(FTDpcStaAlw AS VARCHAR(20))
UNION ALL
SELECT 'FTDpcDisCodeY',
FTDpcDisCodeY
) c(col, value)
) src PIVOT(MAX(value) FOR col IN(
$tDpcDisCodeYSet
)) piv ) PPV
INNER JOIN TSysDisPolicy PLC ON PPV.FTDpcDisCodeX = PLC.FTDisCode
INNER JOIN TSysDisPolicy_L PLL ON PPV.FTDpcDisCodeX = PLL.FTDisCode AND PLL.FNLngID = $nLngID
";
$oQuery = $this->db->query($tSQL);
Load balance : Server 04
