Oracle 查询中列定义不明确的错误

Question

我有一个这样的代码。

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {

        Calculate.Visible = false;
        MV_Label.Visible = false;
        RISK_Label.Visible = false;
        KV_Label.Visible = false;

    }

    protected void Calculate_Click(object sender, EventArgs e)
    {
        MV_Label.Visible = true;
        RISK_Label.Visible = true;
        Calculate.Visible = true;
        KV_Label.Visible = true;

       double sumMV = 0;
        foreach (GridViewRow gvr in GridView1.Rows)
        {
            CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField2");
            if (cb.Checked == true)
            {
                double amount = Convert.ToDouble(gvr.Cells[9].Text);      
                sumMV += amount;
            }
        }
        MV_Label.Text = sumMV.ToString();

        double sumRISK = 0;
        foreach (GridViewRow gvr in GridView1.Rows)
        {
            CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField1");
            if (cb.Checked == true)
            {
                double amount = Convert.ToDouble(gvr.Cells[7].Text);
                sumRISK += amount;
            }
        }
        RISK_Label.Text = sumRISK.ToString();

        double sumKV = 0;
        foreach (GridViewRow gvr in GridView1.Rows)
        {
            CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField3");
            if (cb.Checked == true)
            {
                double amount = Convert.ToDouble(gvr.Cells[11].Text);
                sumKV += amount;
            }
        }
        KV_Label.Text= sumRISK.ToString();

    }
    protected void SendToGridview_Click(object sender, EventArgs e)
    {
        DateTime dt_stb;

        Calculate.Visible = true;
        MV_Label.Visible = true;
        RISK_Label.Visible = true;
        KV_Label.Visible = true;

        string strQuery = string.Empty;

        string ConnectionString = ConfigurationManager.ConnectionStrings["ora"].ConnectionString;

        OracleConnection myConnection = new OracleConnection(ConnectionString);

        string txtBoxText1 = ((TextBox)Page.FindControl("TextBox1")).Text;
        if (txtBoxText1 != "")
        {
            strQuery = @"SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF, A.MUS_K_ISIM AS MUSTERI, 
                    B.MARKA, C.SASI_NO, C.SASI_DURUM, D.TAS_MAR, NVL(RISK_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS RISK,
                    NVL(MV_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.SIRA_NO, C.URUN_SIRA_NO, :S_TARIH_B),0) AS MV,
                    MV_K(TEKLIF_NO1,TEKLIF_NO2, :S_TARIH_B) AS KV
                    FROM S_TEKLIF A,  S_URUN B, S_URUN_DETAY C, KOC_KTMAR_PR D
                    WHERE A.TEKLIF_NO1 || A.TEKLIF_NO2 = B.TEKLIF_NO1 || B.TEKLIF_NO2
                    AND A.TEKLIF_NO1 || A.TEKLIF_NO2 = C.TEKLIF_NO1 || C.TEKLIF_NO2
                    AND B.SIRA_NO = C.URUN_SIRA_NO
                    AND B.DISTRIBUTOR = D.DIST_KOD
                    AND B.MARKA = D.MARKA_KOD
                    AND B.URUN_KOD = D.TAS_KOD ";

        }
        string param = "";
        foreach (ListItem l in CheckBoxList1.Items)
        {
            if (l.Selected)
            {
                param += string.Format("'{0}'", l.Value);
                param += ",";
            }
        }


            param = param.Remove(param.Length - 1);

            strQuery = strQuery + " AND A.HESAP_NO IN (" + param + ")";

            OracleCommand myCommand = new OracleCommand(strQuery, myConnection);
            myCommand.CommandType = System.Data.CommandType.Text;
            myCommand.Connection = myConnection;

            myCommand.CommandText = strQuery;
            dt_stb = DateTime.Parse(txtBoxText1);
            myCommand.Parameters.AddWithValue(":S_TARIH_B", dt_stb);

            myConnection.Open();

            OracleDataReader dr = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            GridView1.DataSource = dr;
            GridView1.DataBind();
            GridView1.Visible = true;

            myConnection.Close();

            double sumMV = 0;
            foreach (GridViewRow gvr in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField2");
                if (cb.Checked == true)
                {
                    double amount = Convert.ToDouble(gvr.Cells[9].Text);
                    sumMV += amount;
                }
            }
            MV_Label.Text = sumMV.ToString();

            double sumRISK = 0;
            foreach (GridViewRow gvr in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField1");
                if (cb.Checked == true)
                {
                    double amount = Convert.ToDouble(gvr.Cells[7].Text);
                    sumRISK += amount;
                }
            }
            RISK_Label.Text = sumRISK.ToString();

            double sumKV = 0;
            foreach (GridViewRow gvr in GridView1.Rows)
            {
                CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField3");
                if (cb.Checked == true)
                {
                    double amount = Convert.ToDouble(gvr.Cells[11].Text);
                    sumKV += amount;
                }
            }
            KV_Label.Text = sumRISK.ToString();


    }
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">

        #form1
        {
            height: 729px;
            width: 1083px;
        }

        .style1
        {
            width: 265px;
        }

      </style>
</head>
<body>
    <form id="form1" runat="server">
    <br />
    <img src="../images/Scania_Logo.gif" style="height: 49px; width: 193px" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="X-Large" 
        ForeColor="Blue" Height="40px" Text="BV &amp; RISK SIMULATOR" 
        Width="329px" style="text-align: center"></asp:Label>
&nbsp;
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <br />
    <div style="OVERFLOW-Y:scroll; WIDTH:362px; HEIGHT:177px">
        <br />

    <table border="5" bordercolor=blue style="height: 116px; width: 343px">
    <tr>
    <td class="style1">
        <asp:CheckBoxList ID="CheckBoxList1" runat="server" 
            DataSourceID="ChechkBoxDataSource" DataTextField="MUS_K_ISIM" 
            DataValueField="HESAP_NO" Font-Size="12pt">
        </asp:CheckBoxList>
        </td>
        </tr>
    </table>
    </div>
    <div style="width: 331px">
        <br />
        <asp:Textbox ID="TextBox1" runat="server" Font-Size="X-Small" Height="13px" Font-Names="Verdana" Width="75px" ></asp:Textbox>   
        <asp:CalendarExtender Format="dd/MM/yyyy" ID="TextBox1_CalendarExtender" runat="server" 
            TargetControlID="TextBox1">
        </asp:CalendarExtender>
        <asp:Image ID="ImageButton3" runat="server"  ImageUrl="~/images/SmallCalendar.gif"/>
        <br />
       </div>
        <asp:SqlDataSource ID="ChechkBoxDataSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ora %>" 
            ProviderName="<%$ ConnectionStrings:ora.ProviderName %>" 
            SelectCommand="SELECT DISTINCT(A.HESAP_NO),A.MUS_K_ISIM
FROM S_TEKLIF A
ORDER BY A.MUS_K_ISIM">
        </asp:SqlDataSource>
    <br />
    <asp:Button ID="SendToGridview" runat="server" Text="Send" Width="59px" 
        onclick="SendToGridview_Click" />
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server"  
         Width="16px" CellPadding="4" 
        GridLines="None" Height="16px" ForeColor="#333333" 
        AutoGenerateColumns="False" DataKeyNames="RISK,MV" BorderColor="White" 
        BorderStyle="Ridge" >
        <AlternatingRowStyle BackColor="White" />
        <Columns>
        <asp:BoundField HeaderText="HESAP" DataField="HESAP_NO" />
     <asp:BoundField HeaderText="TEKLIF" DataField="TEKLIF" />
     <asp:BoundField HeaderText="MUSTERI" DataField="MUSTERI" />
     <asp:BoundField HeaderText="MARKA" DataField="MARKA" />
     <asp:BoundField HeaderText="SASI" DataField="SASI_NO" />
     <asp:BoundField HeaderText="DURUM" DataField="SASI_DURUM" />
     <asp:BoundField HeaderText="TASIT MARKA" DataField="TAS_MAR" />
     <asp:BoundField HeaderText="RISK" DataField="RISK" />


            <asp:templatefield headertext="">
            <itemtemplate>
            <asp:CheckBox DataField="NameCheckBoxField1" ID="NameCheckBoxField1" Checked="True" runat="server"></asp:CheckBox>
            </itemtemplate>
            </asp:templatefield>
            <asp:BoundField HeaderText="MV" DataField="MV" />
            <asp:templatefield headertext="">
            <itemtemplate>
            <asp:CheckBox DataField="NameCheckBoxField2" ID="NameCheckBoxField2" Checked="True" runat="server"></asp:CheckBox>
            </itemtemplate>
            </asp:templatefield>
            <asp:BoundField HeaderText="KV" DataField="KV" />
            <asp:templatefield headertext="">
            <itemtemplate>
            <asp:CheckBox DataField="NameCheckBoxField3" ID="NameCheckBoxField3" Checked="True" runat="server"></asp:CheckBox>
            </itemtemplate>
            </asp:templatefield>

        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" ForeColor="White" Font-Bold="True" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <sortedascendingcellstyle backcolor="#F4F4FD" />
        <sortedascendingheaderstyle backcolor="#5A4C9D" />
        <sorteddescendingcellstyle backcolor="#D8D8F0" />
        <sorteddescendingheaderstyle backcolor="#3E3277" />

<SortedAscendingCellStyle BackColor="#F5F7FB"></SortedAscendingCellStyle>

<SortedAscendingHeaderStyle BackColor="#6D95E1"></SortedAscendingHeaderStyle>

<SortedDescendingCellStyle BackColor="#E9EBEF"></SortedDescendingCellStyle>

<SortedDescendingHeaderStyle BackColor="#4870BE"></SortedDescendingHeaderStyle>
  </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ora %>" 
        ProviderName="<%$ ConnectionStrings:ora.ProviderName %>" 

        SelectCommand="  SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF, A.MUS_K_ISIM , 
B.MARKA, C.SASI_NO, C.SASI_DURUM, D.TAS_MAR, NVL(RISK_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS RISK,
NVL(MV_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS MV,
MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, :S_TARIH_B) AS KV, 'NameCheckBoxField1' = 0x1, 'NameCheckBoxField2' = 0x1, 'NameCheckBoxField3' = 0x1
FROM S_TEKLIF A,  S_URUN B, S_URUN_DETAY C, KOC_KTMAR_PR D
WHERE A.TEKLIF_NO1 || A.TEKLIF_NO2 = B.TEKLIF_NO1 || B.TEKLIF_NO2
AND A.TEKLIF_NO1 || A.TEKLIF_NO2 = C.TEKLIF_NO1 || C.TEKLIF_NO2
AND B.SIRA_NO = C.URUN_SIRA_NO
AND C.SASI_DURUM IN ('A','R')
AND B.DISTRIBUTOR = D.DIST_KOD
AND B.MARKA = D.MARKA_KOD
AND B.URUN_KOD = D.TAS_KOD ">
    </asp:SqlDataSource>
    <br />
    <asp:Button ID="Calculate" runat="server" onclick="Calculate_Click" 
        Text="Calculate" />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Label ID="RISK_Label" runat="server" Font-Bold="True" ForeColor="#3333FF"></asp:Label>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Label ID="MV_Label" runat="server" Font-Bold="True" ForeColor="#3333FF"></asp:Label>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:Label ID="KV_Label" runat="server" Font-Bold="True" ForeColor="#3333FF"></asp:Label>
    <br />
    <br />
    </form>
</body>
</html>

但是当我运行此页面时，我收到这样的错误。

ORA-00918: column ambiguously defined
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-00918: column ambiguously defined


Source Error:

Line 128:            myConnection.Open();
Line 129:
Line 130:            OracleDataReader dr = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
Line 131:
Line 132:            GridView1.DataSource = dr;

Answer 1

可能就在这里 MV_K(TEKLIF_NO1,TEKLIF_NO2, :S_TARIH_B) AS KV。它应该类似于 MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, :S_TARIH_B) AS KV ，因为该列存在于 A、B 和 C 中

Answer 2

您使用了表别名和多个表，但没有在所有字段前面使用表别名前缀。这是出现不明确列的典型情况。