PostgreSQL - Sorgu Sorunu

08-04-2023, 10:44:00

#1

tworun

Selamlar,
Bağlantı kurmak istediğim bir ERP sistemi var, bu ERP sistemi db yapısı olarak PostgreSQL kullanıyor.
Bu erp sistemine bağlanıp aylık hedef ve tutan miktarları çekmek istediğimi ERP destek ekibine ilettim ve bana aşağıdaki gibi bir sorgu ilettiler.
Ben bu sorgudan almak istediğim cevabı ekrana yazdırmak istediğim zaman sayfa hataya düşüyor ve 76. satırda hata görüyor.
Sorguda mı hata var? Bende mi hata var? Bunu çözemedim.

//çözülmüştür.

08-04-2023, 10:52:13

satırda $row['column1'] ve $row['column2'] şeklinde iki adet sütun adı belirtilmiş, ancak sorguda bu sütun adları kullanılmamış. Bu nedenle, kodun düzeltilmiş hali aşağıdaki gibi olacaktır diyor chatgpt amca düzeltilmiş kod ise :

<?php
$dbname = "ip";
$dbuser = "user";
$dbpass = "pass";
$dbhost = "db";
 
$conn = pg_connect("dbname=$dbname user=$dbuser password=$dbpass host=$dbhost");
 
// Sorgu
$query = "WITH PERYOT AS (
                 SELECT DET.BPERIOD_D_ID,
                        MAS.BPERIOD_M_ID,
                        MAS.BPERIOD_M_CODE,
                        DET.BPERIOD_D_CODE
               FROM BGTT_BPERIOD_M MAS
        INNER  JOIN BGTT_BPERIOD_D DET   ON MAS.BPERIOD_M_ID  = DET.BPERIOD_M_ID
              WHERE MAS.BPERIOD_M_CODE         = TO_CHAR(CURRENT_DATE,'YYYY')
                AND TO_CHAR(det.end_date,'MM') = TO_CHAR(CURRENT_DATE,'MM')
                   )
,BAGIS AS (
            SELECT SUM(BGS.AMT_WITH_DISC) BAGIS_CIRO
              FROM
                   (
                    SELECT
                          SUM(CASE WHEN PIM.PURCHASE_SALES = 2
                                   THEN PID.AMT_WITH_DISC
                                   ELSE -PID.AMT_WITH_DISC END)     AMT_WITH_DISC
                         FROM PSMT_INVOICE_D          PID
                    LEFT JOIN PSMT_INVOICE_M          PIM ON PID.INVOICE_M_ID   = PIM.INVOICE_M_ID
                    LEFT JOIN GNLD_COMPANY            CO1 ON PID.CO_ID          = CO1.CO_ID
                    LEFT JOIN INVD_ITEM               ITE ON PID.ITEM_ID        = ITE.ITEM_ID
                    LEFT JOIN GNLD_CATEGORIES        CAT1 ON ITE.CATEGORIES1_ID = CAT1.CATEGORIES_ID
                    LEFT JOIN INVD_UNIT                UN ON PID.UNIT_ID        = UN.UNIT_ID
                    LEFT JOIN INVD_UNIT               UN1 ON ITE.UNIT_ID        = UN1.UNIT_ID
 
                       WHERE (CO1.CO_CODE = 'efeel')
                          AND PIM.PURCHASE_SALES                 IN (2,3)
                          AND PIM.IS_CANCEL_INVOICE               = 0
                          AND PIM.E_INVOICE_STATUS           NOT IN (1,7)
                          AND PID.LINE_TYPE                       = 1
                          AND PIM.SOURCE_APP                     <> 165
                          AND PIM.CAT_CODE1_ID                IN (SELECT CAT_CODE_ID FROM GNLD_CATEGORY WHERE CAT_CODE LIKE 'BAGIS%')
                          AND TO_CHAR(PIM.DOC_DATE,'YYYY-MM')= TO_CHAR(CURRENT_DATE,'YYYY-MM')
 
                 UNION ALL
                   SELECT
                    COALESCE(
                     SUM(CASE WHEN PIM.PURCHASE_SALES = 2
                              THEN PID.AMT_WITH_DISC
                              ELSE -PID.AMT_WITH_DISC END),0)       AMT_WITH_DISC
                       FROM PSMT_INVOICE_D          PID
                  LEFT JOIN PSMT_INVOICE_M          PIM ON PID.INVOICE_M_ID   = PIM.INVOICE_M_ID
                  LEFT JOIN GNLD_COMPANY            CO1 ON PID.CO_ID          = CO1.CO_ID
                  LEFT JOIN INVD_EXPENSE            EXP ON PID.DCARD_ID       = EXP.EXPENSE_ID
                  LEFT JOIN FIND_ACC                ACC ON PID.DCARD_ID       = ACC.ACC_ID
                  LEFT JOIN GNLD_CATEGORIES        CAT1 ON EXP.CATEGORIES1_ID = CAT1.CATEGORIES_ID
                  LEFT JOIN GNLD_CATEGORIES        CATE ON EXP.CATEGORIES1_ID = CATE.CATEGORIES_ID
                  LEFT JOIN INVD_UNIT                UN ON PID.UNIT_ID        = UN.UNIT_ID
                  LEFT JOIN INVD_UNIT               UN1 ON EXP.UNIT_ID        = UN1.UNIT_ID
 
                      WHERE (CO1.CO_CODE                        = 'efeel')
                        AND PIM.PURCHASE_SALES                 IN (2,3)
                        AND PIM.IS_CANCEL_INVOICE               = 0
                        AND PIM.E_INVOICE_STATUS           NOT IN (1,7)
                        AND PID.LINE_TYPE                      <> 1
                        AND PIM.SOURCE_APP                     <> 165
                        AND PIM.CAT_CODE1_ID                   IN (SELECT CAT_CODE_ID FROM GNLD_CATEGORY WHERE CAT_CODE LIKE 'BAGIS%')
                        AND TO_CHAR(PIM.DOC_DATE,'YYYY-MM')     = TO_CHAR(CURRENT_DATE,'YYYY-MM')
                        ) BGS
                    )
 , BUTCE AS (
               SELECT
                      SUM(BSD2.BGT_AMT - BSD2.BGT_AMT_DISC)               AS \"BTC_CIRO_TL\" ,
 
                      SUM(BSD2.AMT - BSD2.AMT_DISC - BSD2.AMT_RETURN)-
 
                      (SELECT BAGIS_CIRO FROM  BAGIS)     AS \"GERCEKLESEC_CIRO_TL\"
 
 
 
                      FROM BGTT_SALES_D           BSD
                INNER JOIN BGTT_SALES_M           BSM ON BSD.SALES_M_ID             = BSM.SALES_M_ID
                INNER JOIN BGTD_CO_BUDGET         BGC ON BSM.BUDGET_ID            = BGC.BUDGET_ID
                                                     AND BSM.CO_ID                = BGC.CO_ID
                                                     AND BSM.CO_BUDGET_ID           = BGC.CO_BUDGET_ID
                INNER JOIN BGTD_BUDGETS           BGB ON BGC.BUDGET_ID              = BGB.BUDGET_ID
                INNER JOIN GNLD_COMPANY           CO1 ON BSM.CO_ID                  = CO1.CO_ID
                INNER JOIN GNLD_BRANCH            BR1 ON BSM.BRANCH_ID              = BR1.BRANCH_ID
                LEFT  JOIN BGTT_SALES_D2         BSD2 ON BSD.SALES_D_ID             = BSD2.SALES_D_ID
                LEFT  JOIN BGTT_BPERIOD_D         PD1 ON BSD2.BPERIOD_D_ID          = PD1.BPERIOD_D_ID
                LEFT  JOIN BGTT_ITEM_GROUP_REL_M RELM ON BSM.CO_BUDGET_ID           = RELM.CO_BUDGET_ID
                LEFT  JOIN BGTT_ITEM_GROUP_REL_D RELD ON RELM.ITEM_GROUP_REL_M_ID   = RELD.ITEM_GROUP_REL_M_ID
                                                     AND BSD.DCARD_ID               = RELD.ITEM_GROUP_REL_D_ID
                LEFT  JOIN GNLD_CATEGORIES_RECORD CTG ON CTG.CATEGORIES_SEQ_M_ID    = BGC.SALES_ITEM_CAT_SEQ_ID
                                                     AND BGC.SALES_ITEM_ENTRY_TYPE  = 3
                                                     AND RELD.CATEGORIES_RECORD_ID  = CTG.CATEGORIES_RECORD_ID
                LEFT  JOIN INVD_EXPENSE           EXP ON BSD.DCARD_ID               = EXP.EXPENSE_ID
 
                LEFT  JOIN BGTT_ITEM_GROUP_REL_D  GRD ON BSD.DCARD_ID               = GRD.ITEM_GROUP_REL_D_ID
                LEFT  JOIN BGTD_BUDGET_ITEM       CAT ON GRD.BUDGET_ITEM_ID         = CAT.BUDGET_ITEM_ID
 
                LEFT  JOIN GNLD_CATEGORIES       CATE ON EXP.CATEGORIES1_ID         = CATE.CATEGORIES_ID
                LEFT  JOIN GNLD_CURRENCY        CUR ON BSD.CUR_TRA_ID             = CUR.CUR_ID
                LEFT  JOIN GNLD_CATEGORY         CAT2 ON BSM.CAT_CODE2_ID           = CAT2.CAT_CODE_ID AND CAT2.IS_CAT_CODE2 = 1
                LEFT  JOIN FIND_ENTITY            FEN ON BSD.ENTITY_ID            = FEN.ENTITY_ID
                LEFT  JOIN INVD_UNIT              UNI ON BSD.UNIT_ID                = UNI.UNIT_ID
 
 
                 WHERE
                          (BGB.BUDGET_CODE          = '2023') AND
                          (CO1.CO_CODE                  = 'efeel') AND
                          (BSD2.BPERIOD_D_ID          = (SELECT BPERIOD_D_ID
                                                           FROM PERYOT       )) AND
                          (BSM.CANAL_TYPE             = 1) AND
                          (BSM.CAT_CODE1_ID          <> 374)
            )
 
       SELECT \"BTC_CIRO_TL\"                          BUTCE_TUTAR,
              \"GERCEKLESEC_CIRO_TL\"                  GERCEKLESEN_TUTAR,
              \"BTC_CIRO_TL\" - \"GERCEKLESEC_CIRO_TL\"  SAPMA
         FROM BUTCE";
$result = pg_query($conn, $query);
 
while ($row = pg_fetch_array($result)) {
    echo $row['BUTCE_TUTAR'] . " " . $row['GERCEKLESEN_TUTAR'] . " " . $row['SAPMA'] . "<br>";
}
 
pg_close($conn);
 
?>

08-04-2023, 11:00:54

tworun

ysnyImz adlı üyeden alıntı: mesajı görüntüle

satırda $row['column1'] ve $row['column2'] şeklinde iki adet sütun adı belirtilmiş, ancak sorguda bu sütun adları kullanılmamış. Bu nedenle, kodun düzeltilmiş hali aşağıdaki gibi olacaktır diyor chatgpt amca düzeltilmiş kod ise :

<?php
$dbname = "ip";
$dbuser = "user";
$dbpass = "pass";
$dbhost = "db";
 
$conn = pg_connect("dbname=$dbname user=$dbuser password=$dbpass host=$dbhost");
 
// Sorgu
$query = "WITH PERYOT AS (
                 SELECT DET.BPERIOD_D_ID,
                        MAS.BPERIOD_M_ID,
                        MAS.BPERIOD_M_CODE,
                        DET.BPERIOD_D_CODE
               FROM BGTT_BPERIOD_M MAS
        INNER  JOIN BGTT_BPERIOD_D DET   ON MAS.BPERIOD_M_ID  = DET.BPERIOD_M_ID
              WHERE MAS.BPERIOD_M_CODE         = TO_CHAR(CURRENT_DATE,'YYYY')
                AND TO_CHAR(det.end_date,'MM') = TO_CHAR(CURRENT_DATE,'MM')
                   )
,BAGIS AS (
            SELECT SUM(BGS.AMT_WITH_DISC) BAGIS_CIRO
              FROM
                   (
                    SELECT
                          SUM(CASE WHEN PIM.PURCHASE_SALES = 2
                                   THEN PID.AMT_WITH_DISC
                                   ELSE -PID.AMT_WITH_DISC END)     AMT_WITH_DISC
                         FROM PSMT_INVOICE_D          PID
                    LEFT JOIN PSMT_INVOICE_M          PIM ON PID.INVOICE_M_ID   = PIM.INVOICE_M_ID
                    LEFT JOIN GNLD_COMPANY            CO1 ON PID.CO_ID          = CO1.CO_ID
                    LEFT JOIN INVD_ITEM               ITE ON PID.ITEM_ID        = ITE.ITEM_ID
                    LEFT JOIN GNLD_CATEGORIES        CAT1 ON ITE.CATEGORIES1_ID = CAT1.CATEGORIES_ID
                    LEFT JOIN INVD_UNIT                UN ON PID.UNIT_ID        = UN.UNIT_ID
                    LEFT JOIN INVD_UNIT               UN1 ON ITE.UNIT_ID        = UN1.UNIT_ID
 
                       WHERE (CO1.CO_CODE = 'efeel')
                          AND PIM.PURCHASE_SALES                 IN (2,3)
                          AND PIM.IS_CANCEL_INVOICE               = 0
                          AND PIM.E_INVOICE_STATUS           NOT IN (1,7)
                          AND PID.LINE_TYPE                       = 1
                          AND PIM.SOURCE_APP                     <> 165
                          AND PIM.CAT_CODE1_ID                IN (SELECT CAT_CODE_ID FROM GNLD_CATEGORY WHERE CAT_CODE LIKE 'BAGIS%')
                          AND TO_CHAR(PIM.DOC_DATE,'YYYY-MM')= TO_CHAR(CURRENT_DATE,'YYYY-MM')
 
                 UNION ALL
                   SELECT
                    COALESCE(
                     SUM(CASE WHEN PIM.PURCHASE_SALES = 2
                              THEN PID.AMT_WITH_DISC
                              ELSE -PID.AMT_WITH_DISC END),0)       AMT_WITH_DISC
                       FROM PSMT_INVOICE_D          PID
                  LEFT JOIN PSMT_INVOICE_M          PIM ON PID.INVOICE_M_ID   = PIM.INVOICE_M_ID
                  LEFT JOIN GNLD_COMPANY            CO1 ON PID.CO_ID          = CO1.CO_ID
                  LEFT JOIN INVD_EXPENSE            EXP ON PID.DCARD_ID       = EXP.EXPENSE_ID
                  LEFT JOIN FIND_ACC                ACC ON PID.DCARD_ID       = ACC.ACC_ID
                  LEFT JOIN GNLD_CATEGORIES        CAT1 ON EXP.CATEGORIES1_ID = CAT1.CATEGORIES_ID
                  LEFT JOIN GNLD_CATEGORIES        CATE ON EXP.CATEGORIES1_ID = CATE.CATEGORIES_ID
                  LEFT JOIN INVD_UNIT                UN ON PID.UNIT_ID        = UN.UNIT_ID
                  LEFT JOIN INVD_UNIT               UN1 ON EXP.UNIT_ID        = UN1.UNIT_ID
 
                      WHERE (CO1.CO_CODE                        = 'efeel')
                        AND PIM.PURCHASE_SALES                 IN (2,3)
                        AND PIM.IS_CANCEL_INVOICE               = 0
                        AND PIM.E_INVOICE_STATUS           NOT IN (1,7)
                        AND PID.LINE_TYPE                      <> 1
                        AND PIM.SOURCE_APP                     <> 165
                        AND PIM.CAT_CODE1_ID                   IN (SELECT CAT_CODE_ID FROM GNLD_CATEGORY WHERE CAT_CODE LIKE 'BAGIS%')
                        AND TO_CHAR(PIM.DOC_DATE,'YYYY-MM')     = TO_CHAR(CURRENT_DATE,'YYYY-MM')
                        ) BGS
                    )
 , BUTCE AS (
               SELECT
                      SUM(BSD2.BGT_AMT - BSD2.BGT_AMT_DISC)               AS \"BTC_CIRO_TL\" ,
 
                      SUM(BSD2.AMT - BSD2.AMT_DISC - BSD2.AMT_RETURN)-
 
                      (SELECT BAGIS_CIRO FROM  BAGIS)     AS \"GERCEKLESEC_CIRO_TL\"
 
 
 
                      FROM BGTT_SALES_D           BSD
                INNER JOIN BGTT_SALES_M           BSM ON BSD.SALES_M_ID             = BSM.SALES_M_ID
                INNER JOIN BGTD_CO_BUDGET         BGC ON BSM.BUDGET_ID            = BGC.BUDGET_ID
                                                     AND BSM.CO_ID                = BGC.CO_ID
                                                     AND BSM.CO_BUDGET_ID           = BGC.CO_BUDGET_ID
                INNER JOIN BGTD_BUDGETS           BGB ON BGC.BUDGET_ID              = BGB.BUDGET_ID
                INNER JOIN GNLD_COMPANY           CO1 ON BSM.CO_ID                  = CO1.CO_ID
                INNER JOIN GNLD_BRANCH            BR1 ON BSM.BRANCH_ID              = BR1.BRANCH_ID
                LEFT  JOIN BGTT_SALES_D2         BSD2 ON BSD.SALES_D_ID             = BSD2.SALES_D_ID
                LEFT  JOIN BGTT_BPERIOD_D         PD1 ON BSD2.BPERIOD_D_ID          = PD1.BPERIOD_D_ID
                LEFT  JOIN BGTT_ITEM_GROUP_REL_M RELM ON BSM.CO_BUDGET_ID           = RELM.CO_BUDGET_ID
                LEFT  JOIN BGTT_ITEM_GROUP_REL_D RELD ON RELM.ITEM_GROUP_REL_M_ID   = RELD.ITEM_GROUP_REL_M_ID
                                                     AND BSD.DCARD_ID               = RELD.ITEM_GROUP_REL_D_ID
                LEFT  JOIN GNLD_CATEGORIES_RECORD CTG ON CTG.CATEGORIES_SEQ_M_ID    = BGC.SALES_ITEM_CAT_SEQ_ID
                                                     AND BGC.SALES_ITEM_ENTRY_TYPE  = 3
                                                     AND RELD.CATEGORIES_RECORD_ID  = CTG.CATEGORIES_RECORD_ID
                LEFT  JOIN INVD_EXPENSE           EXP ON BSD.DCARD_ID               = EXP.EXPENSE_ID
 
                LEFT  JOIN BGTT_ITEM_GROUP_REL_D  GRD ON BSD.DCARD_ID               = GRD.ITEM_GROUP_REL_D_ID
                LEFT  JOIN BGTD_BUDGET_ITEM       CAT ON GRD.BUDGET_ITEM_ID         = CAT.BUDGET_ITEM_ID
 
                LEFT  JOIN GNLD_CATEGORIES       CATE ON EXP.CATEGORIES1_ID         = CATE.CATEGORIES_ID
                LEFT  JOIN GNLD_CURRENCY        CUR ON BSD.CUR_TRA_ID             = CUR.CUR_ID
                LEFT  JOIN GNLD_CATEGORY         CAT2 ON BSM.CAT_CODE2_ID           = CAT2.CAT_CODE_ID AND CAT2.IS_CAT_CODE2 = 1
                LEFT  JOIN FIND_ENTITY            FEN ON BSD.ENTITY_ID            = FEN.ENTITY_ID
                LEFT  JOIN INVD_UNIT              UNI ON BSD.UNIT_ID                = UNI.UNIT_ID
 
 
                 WHERE
                          (BGB.BUDGET_CODE          = '2023') AND
                          (CO1.CO_CODE                  = 'efeel') AND
                          (BSD2.BPERIOD_D_ID          = (SELECT BPERIOD_D_ID
                                                           FROM PERYOT       )) AND
                          (BSM.CANAL_TYPE             = 1) AND
                          (BSM.CAT_CODE1_ID          <> 374)
            )
 
       SELECT \"BTC_CIRO_TL\"                          BUTCE_TUTAR,
              \"GERCEKLESEC_CIRO_TL\"                  GERCEKLESEN_TUTAR,
              \"BTC_CIRO_TL\" - \"GERCEKLESEC_CIRO_TL\"  SAPMA
         FROM BUTCE";
$result = pg_query($conn, $query);
 
while ($row = pg_fetch_array($result)) {
    echo $row['BUTCE_TUTAR'] . " " . $row['GERCEKLESEN_TUTAR'] . " " . $row['SAPMA'] . "<br>";
}
 
pg_close($conn);
 
?>

Yazdırma aşamasına kadar gelemediği için o bölümü henüz değiştirmemiştim.
Aşağıda php error.log dosyamın hata içeriği yer alıyor;

[Sat Apr 08 10:57:06.421815 2023] [php:error] [pid 361508] [client 100.100.100.31:54715] PHP Parse error:  syntax error, unexpected identifier "BTC_CIRO_TL" in /var/www/_TURAN/test/index.php on line 73

"BTC_CIRO_TL" bölümüne takılıyor gibi gözüküyor.