locked
Problems with querys in my project RRS feed

  • Question

  • User662762443 posted

    Boa tarde galera, tenho o seguinte problema: Preciso inserir meus vouchers no banco de dados oracle, mas primeiro preciso encontrar o processo e o interessado pelo código de barras no banco de dados: No banco de dados posso encontrar usando esta consulta:

    --Por enquanto, tudo bem.

    Thursday, August 2, 2018 2:23 PM

Answers

  • User269602965 posted

    Let's break the problem down into two concepts.

    1. When returning a single value such as COUNT (with null handling), you can use SCALAR query.
    2. When you are passing variables to filter the COUNT then you should use BIND variables in your SELECT statement AND Oracle Parameters collection to populate the one or more bind variables.

    Example for concept 1:

    public void ExecCountQuery
    {
        OracleConnection conn = new OracleConnection(connectionString);
        try
        {
            String strCOUNT = "";
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select count(*) from SALES";
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = QUERY_TIMEOUT;
            Object output  = cmd.ExecuteScalar();
            if (output != null)
              strCount = output.ToString();
        }
        finally
        {
            conn.Close();
        }
    }
    
    

    Now do the same, but filter by shoe color to get count of brown shoes sold:

    public void ExecCountQuery
    {
        OracleConnection conn = new OracleConnection(connectionString);
        try
        {
            String strCOUNT = "";
            String strShoeColor = "BROWN";
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select count(*) from SALES where SHOE_COLOR = :bv_shoe_color";
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = QUERY_TIMEOUT;
            cmd.Parameters.Clear();
            cmd.Parameters.Add("bv_shoe_color", OracleDbType.VARCHAR2, strShoeColor, ParameterDirection.Input);
            Object output  = cmd.ExecuteScalar();
            if (output != null)
              strCount = output.ToString();
        }
        finally
        {
            conn.Close();
        }
    }
    
    
    

    Finally you did not state what connection driver you are using??  OracleDataAccess Components ODAC?  aka ODP.NET.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 5, 2018 2:14 PM

All replies

  • User269602965 posted

    Let's break the problem down into two concepts.

    1. When returning a single value such as COUNT (with null handling), you can use SCALAR query.
    2. When you are passing variables to filter the COUNT then you should use BIND variables in your SELECT statement AND Oracle Parameters collection to populate the one or more bind variables.

    Example for concept 1:

    public void ExecCountQuery
    {
        OracleConnection conn = new OracleConnection(connectionString);
        try
        {
            String strCOUNT = "";
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select count(*) from SALES";
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = QUERY_TIMEOUT;
            Object output  = cmd.ExecuteScalar();
            if (output != null)
              strCount = output.ToString();
        }
        finally
        {
            conn.Close();
        }
    }
    
    

    Now do the same, but filter by shoe color to get count of brown shoes sold:

    public void ExecCountQuery
    {
        OracleConnection conn = new OracleConnection(connectionString);
        try
        {
            String strCOUNT = "";
            String strShoeColor = "BROWN";
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select count(*) from SALES where SHOE_COLOR = :bv_shoe_color";
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = QUERY_TIMEOUT;
            cmd.Parameters.Clear();
            cmd.Parameters.Add("bv_shoe_color", OracleDbType.VARCHAR2, strShoeColor, ParameterDirection.Input);
            Object output  = cmd.ExecuteScalar();
            if (output != null)
              strCount = output.ToString();
        }
        finally
        {
            conn.Close();
        }
    }
    
    
    

    Finally you did not state what connection driver you are using??  OracleDataAccess Components ODAC?  aka ODP.NET.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 5, 2018 2:14 PM
  • User269602965 posted

    I should also mention COUNT(*) that can take some time to return a value if you have millions of rows to scan for a result.

    In that case you might build pre-count tables to support your front end business analysis where all the counting is done on a schedule off-line from the user such has using a materialized view with refresh schedule.

    Have to weigh performance and counting refresh rate required by the users.  Is daily refresh of counts sufficient for their needs?  Monthly?  These are things to determine when deciding if business intelligence pre-count tables is not the way to go for presenting data.

    Sunday, August 5, 2018 5:11 PM