FlowHeater - The Data Import / Export Specialist
See also the general examples available, which may answer your question: Hello World 1 / 2 / 3 , format numbers as character strings , simple If-Then-Else , complex If-Then-ElseIf , filtering and grouping data , consolidate grouped values

SQL statement on an InMemory Table

  • JD Cox
  • Topic Author
  • Offline
  • Premium Member
  • Premium Member
More
1 month 3 weeks ago #3680 by JD Cox
SQL statement on an InMemory Table was created by JD Cox
Is it possible to execute a SQL statement on an InMemory Table inside a .NET Heater?
I don’t need to pass any parameters and I know I can get the results I’m looking for with a few IF heaters.  Looking for a cleaner option.

JD

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago - 1 month 3 weeks ago #3681 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL statement on an InMemory Table
Hi JD,

Yes, but only simple SQL selects allowed. For example, the below listed SQLs would be possible

  object o = AdapterWrite.Execute("select [FieldName] from [TableName] where [id] = " + id.ToString(), true);
  object o = AdapterWrite.Execute("select count(*) from [TableName] where [id] = " + id.ToString(), true);


Please find attached a simple example.

 

File Attachment:

File Name: inmemory-s...cute.zip
File Size:7 KB


 

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Last edit: 1 month 3 weeks ago by FlowHeater-Team.

Please Log in or Create an account to join the conversation.

  • JD Cox
  • Topic Author
  • Offline
  • Premium Member
  • Premium Member
More
1 month 3 weeks ago #3682 by JD Cox
Replied by JD Cox on topic SQL statement on an InMemory Table
The InMemory table I need the value from is not on the Read or Write side.
This is what I'm trying to do, I'm getting an error on lines 11 and 16.
Please note that C# is not my thing.

public object DoWork()
{
    if (InValues.Length == 0)
    throw new ArgumentException("min. 1 input parameters expected!");
    int i = (int)InValues[0].GetInt();

    switch (i)
    {
    //Pallet In
        case 1:
        return InMemoryAdapter.Execute("select PVAL from PRICING where PTYPE = 20", true);
        break;

    //Carton In
        case 2:
        return InMemoryAdapter.Execute("select PVAL from PRICING where PTYPE = 25", true);
        break;

    default:
    {
    //Others
        return 100;
        break;
    }
    }
}

Thanks, JD
 

Please Log in or Create an account to join the conversation.

More
1 month 3 weeks ago - 1 month 2 weeks ago #3683 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL statement on an InMemory Table
Hi JD,

you just have to create a InMemoryAdapter inside the script, see below.

Code:
public object DoWork() {         if (InValues.Length == 0)         throw new ArgumentException("min. 1 input parameters expected!");     // Just create a InMemoryAdapter instance     InMemoryAdapter adapter = new InMemoryAdapter();     int i = (int)InValues[0].GetInt();     switch (i)     {         //Pallet In             case 1:                 return adapter.Execute("select PVAL from PRICING where PTYPE = 20", true);             //Carton In             case 2:                 return adapter.Execute("select PVAL from PRICING where PTYPE = 25", true);            //Others             default:                 return 100;        } }


It would also be posible to lookup the data via the String Replace Heater like this.

 
 

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Last edit: 1 month 2 weeks ago by FlowHeater-Team.

Please Log in or Create an account to join the conversation.

  • JD Cox
  • Topic Author
  • Offline
  • Premium Member
  • Premium Member
More
1 month 3 weeks ago #3684 by JD Cox
Replied by JD Cox on topic SQL statement on an InMemory Table
Perfect
Thanks Robert

As far as the Replace Heater thanks for the tip. 
But, this is just the first step in the .NET Heater.

JD

Please Log in or Create an account to join the conversation.

  • JD Cox
  • Topic Author
  • Offline
  • Premium Member
  • Premium Member
More
1 month 2 weeks ago - 1 month 2 weeks ago #3685 by JD Cox
Replied by JD Cox on topic SQL statement on an InMemory Table
Thanks again Robert

Works great.  Sharing this if it helps anyone else.  
Here’s what I did to work out the math.

public object DoWork(){
      if (InValues.Length == 0)
      throw new ArgumentException("min. 1 input parameters expected!");
     
     
      // Create a InMemoryAdapter instance     
      InMemoryAdapter adapter1 = new InMemoryAdapter();
      InMemoryAdapter adapter2 = new InMemoryAdapter();
     
      int i = (int)InValues[0].GetInt();                  //pallet = 1 carton = 2
      int iremaining = 30-DateTime.Now.Day;     //days remaining in month for initial pallet charge
      decimal ival = 0;

      switch (i)
      {
      //Pallet In           
            case 1:

            //initial pallet month
            object oipallet = adapter1.Execute("select PVAL from PRICING where PTYPE = 40", true);
            
            //pallet in charge
            object opalletin = adapter2.Execute("select PVAL from PRICING where PTYPE = 20", true);
           
            return ival = ((Convert.ToDecimal(oipallet)*iremaining)/30)+Convert.ToDecimal(opalletin);
            break;

      //Carton In
           
           case 2:

            //carton in  charge
            return adapter2.Execute("select PVAL from PRICING where PTYPE = 25", true);
            break;
      default:
      {
      //Others           
            return 1000;
            break;
      }
      }
}


Image shows the difference it made.

JD

 
Last edit: 1 month 2 weeks ago by JD Cox.

Please Log in or Create an account to join the conversation.

More
1 month 2 weeks ago #3686 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL statement on an InMemory Table
Hi JD,

Thanks for the information. This be helpful for some others, great.

There was an error after migration the Forum software. Now it would be possible to add images. May I ask you to add your mentioned image, Thanks.
 

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in or Create an account to join the conversation.

  • JD Cox
  • Topic Author
  • Offline
  • Premium Member
  • Premium Member
More
1 month 2 weeks ago #3687 by JD Cox
Replied by JD Cox on topic SQL statement on an InMemory Table
 You got it, thanks again.
Attachments:

Please Log in or Create an account to join the conversation.

Time to create page: 0.390 seconds

other Languages

de

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

Copyright © 2009-2023 by FlowHeater GmbH.
All rights reserved.

Follow us on

twitter  facebook

YouTube

 de en