Sunday, December 22, 2019

SQL SERVER - How to get min/max value from more values

declare @ra_min float = ( select min(x) from ( values ( @ra_from ), ( @ra_to ) ) as n(x) );
declare @ra_max float = ( select max(x) from ( values ( @ra_from ), ( @ra_to ) ) as n(x) );

Tuesday, December 3, 2019

SQL SERVER - How to generate more rows from one row (with count limited by source row value)

Suppose this situation, I have this query that returns two rows.
select a.prodid, a.itemid, a.qtycalc from prodtable a
where 
a.prodid in ( '19-047442', '19-047441' ) 
Output:
prodid               itemid                                   qtycalc
-------------------- ---------------------------------------- --------------------
19-047441            5801-989-012_1138-00_lak                 4.0000000000000000
19-047442            5801-989-012_1138-00_lak                 12.0000000000000000

(2 row(s) affected)
Now I want to make "exemplar query" - for every piece in PRODID to generate one row, so total 16 rows.

How to make it ? One variant is here:
with cte as (
select row_number() over ( order by itemid ) AS rownum from inventtable )

select n.rownum, a.prodid, a.qtycalc
from prodtable a
cross join 
 ( select * from cte 
   where 
   rownum < 999 ) n
where 
a.prodid in ( '19-047442', '19-047441' ) and
a.prodpoolid = 'SED_CES_ST' and
a.prodstatus = 4 and
n.rownum <= a.qtycalc
Output:
rownum               prodid               qtycalc
-------------------- -------------------- ---------------------
1                    19-047441            4.0000000000000000
2                    19-047441            4.0000000000000000
3                    19-047441            4.0000000000000000
4                    19-047441            4.0000000000000000
1                    19-047442            12.0000000000000000
2                    19-047442            12.0000000000000000
3                    19-047442            12.0000000000000000
4                    19-047442            12.0000000000000000
5                    19-047442            12.0000000000000000
6                    19-047442            12.0000000000000000
7                    19-047442            12.0000000000000000
8                    19-047442            12.0000000000000000
9                    19-047442            12.0000000000000000
10                   19-047442            12.0000000000000000
11                   19-047442            12.0000000000000000
12                   19-047442            12.0000000000000000

(16 row(s) affected)

Thursday, November 21, 2019

C# - How combine path with file name without problem

..function care about suitable separator:
string sFileName = Path.Combine( PIFiles.GetExePath(), "file.dat" );

MessageBox.Show( sFileName );
Output:

C# - List.AddRange() - how copy one List object to another in one row

I have two List:
List<ObjectCoordinateBase> AllObjects;

List<InfoStar> Rows;
When I want copy objects from InfoStar class to ObjectCoordinate class, I can use List.AddRange() method.
Node: Import is class, that contains Rows.
Map.AllObjects.AddRange( import.GetAsObjectCoordinateBase() );
And here:
public IEnumerable<ObjectCoordinateBase> GetAsObjectCoordinateBase() {
      
  List<ObjectCoordinateBase> list = new List<ObjectCoordinateBase>();

  this.Rows.ForEach( item => { 
      
    list.Add( new ObjectCoordinateBase( item ) );
      
  } );
      
  return list;
}

Tuesday, November 12, 2019

C# - How to create yes-no question

..last parameter is default button (=second button in dialog).
if ( MessageBox.Show( this, "All data will be deleted. Continue ?",
     "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, 
     MessageBoxDefaultButton.Button2 ) == DialogResult.No ) return;
Output:

Friday, October 4, 2019

C# - How to join two LINQ dataset

This example join COUNTRY with CONTINENT; and shows CONTINENT where exists COUNTRY with population greater than 300.

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

/* -- join */

List listContinent = new List();

listContinent.Add( new Continent( "europe", 1 ) );
listContinent.Add( new Continent( "america", 2 ) );
listContinent.Add( new Continent( "asia", 3 ) );

var list = ( from rowCountry in listCountry join rowContinent in listContinent
             on rowCountry.ContinentName equals rowContinent.ContinentName
             where
             rowCountry.Population > 300
             select new {
               Name = rowCountry.Name,                     
               RelationField = rowContinent.RelationField
             } );

foreach ( var row in list ) 
  Console.WriteLine( 
    string.Format( "ContinentName = {0} RelationField={1} ", row.Name, row.RelationField ) );
      
Console.WriteLine();
Output:
ContinentName = usa RelationField=2 
ContinentName = china RelationField=3 

Thursday, August 22, 2019

C# - How to use IEnumerable interface with LINQ

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

/* -- IEnumerable -> list of europe items */

IEnumerable<country> europeList = 
  from row in listCountry where row.ContinentName == "europe" select row;

foreach ( Country row in europeList ) Console.WriteLine( row.Name );

Console.WriteLine();
Output:
czech
germany
france

Wednesday, August 21, 2019

C# - How to get sum for field in LINQ, where condition, with both syntax variants

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

double value;

/* -- sum - query syntax variant */

value = ( from row in listCountry
          where row.ContinentName == "europe" 
          select row.Population ).Sum();

Console.WriteLine( string.Format( "Sum of population, europe (variant 1) = {0}", value ) );

/* -- sum - fluent syntax variant */

value = listCountry.Where( w => w.ContinentName == "europe" ).Sum( x => x.Population );

Console.WriteLine( string.Format( "Sum of population, europe (variant 2) = {0}", value ) );
Output:
Sum of population, europe (variant 1) = 130
Sum of population, europe (variant 2) = 130

C# - How to get sum for field in LINQ with both syntax variants

/* -- declare class */

public class Country {
  public string Name;
  public string ContinentName;
  public int Population;

  public Country( string _name, string _continentName, int _population ) {
    this.Name = _name;
    this.ContinentName = _continentName;
    this.Population = _population;
  }
}

/* -- init */

List<country> listCountry = new List();

listCountry.Add( new Country( "czech", "europe", 10 ) );
listCountry.Add( new Country( "germany", "europe", 60 ) );
listCountry.Add( new Country( "france", "europe", 60 ) );
listCountry.Add( new Country( "usa", "america", 350 ) );
listCountry.Add( new Country( "china", "asia", 1200 ) );

double value;

/* -- sum - query syntax variant */

value = ( from row in listCountry                      
          select row.Population ).Sum();

Console.WriteLine( string.Format( "Sum of population(variant 1) = {0}", value ) );

/* -- sum - fluent syntax variant */

value = listCountry.Sum( x => x.Population );

Console.WriteLine( string.Format( "Sum of population (variant 2) = {0}", value ) );
Output:
Sum of population(variant 1) = 1680
Sum of population (variant 2) = 1680

Monday, August 5, 2019

AX - How to work with time zone

This example shows actual hour:

First row is actual time; second row is shifted to user predefined time zone; third row is shifted to specific fixed time zone.
info( int2str( DateTimeUtil::hour( DateTimeUtil::getSystemDateTime() ) ) );
    
info( int2str( DateTimeUtil::hour( 
  DateTimeUtil::applyTimeZoneOffset( DateTimeUtil::getSystemDateTime(), 
  DateTimeUtil::getUserPreferredTimeZone() ) ) ) );

info( int2str( DateTimeUtil::hour( 
  DateTimeUtil::applyTimeZoneOffset( DateTimeUtil::getSystemDateTime(), 
  Timezone::GMTPLUS0100_AMSTERDAM_BERLIN_BERN_ROME ) ) ) );
Output:
7
9
9

Friday, May 3, 2019

JAVAFX - How to fill LineChart

This LineChart has X String axis and Y Number axis.
@FXML LineChart Chart;
...
/* when is TRUE, is problem with X axis labels */
Chart.setAnimated( false );
  
/* -- get axis */
  
CategoryAxis bottomAxis = (CategoryAxis) Chart.getXAxis();
Axis leftAxis = Chart.getYAxis();
  
/* -- set axis title */
  
bottomAxis.setLabel( "Bottom axis" );
leftAxis.setLabel( "Left axis" );
  
/* -- create series */
  
/* first */
Series serie = new XYChart.Series();
serie.setName( "First" );
  
serie.getData().add( new XYChart.Data( "Jan", 23 ) );
serie.getData().add( new XYChart.Data( "Feb", 32 ) );
serie.getData().add( new XYChart.Data( "Mar", 50 ) );
  
/* second */
Series serie2 = new XYChart.Series();
serie2.setName( "Second" );
  
serie2.getData().add( new XYChart.Data( "Jan", 100 ) );
serie2.getData().add( new XYChart.Data( "Feb", 72 ) );
serie2.getData().add( new XYChart.Data( "Mar", 20 ) );

/* remove point symbols */
Chart.setCreateSymbols(false);
  
/* add series to chart */
Chart.getData().clear();
Chart.getData().add( serie );
Chart.getData().add( serie2 );
Output:

Monday, March 25, 2019

SQL SERVER - How to get end of month date

select eomonth( getdate(), -1 )
select eomonth( getdate() )    
select eomonth( getdate(), 1 )
Output:
----------
2019-02-28

(1 row(s) affected)


----------
2019-03-31

(1 row(s) affected)


----------
2019-04-30

(1 row(s) affected)

Tuesday, March 19, 2019

DELPHI - How to go through all rows in dataset with bookmark

Bookmarks store your position in dataset and  after action you can return back to original record.
var
  iCount : integer;
  pBookmark : TBookmark;
  pDataSetSource : TDataSet;
begin
  ...
  Screen.Cursor := crHourGlass;
  iCount := 1;
  pBookmark := pDatasetSource.GetBookmark;
  pDatasetSource.DisableControls;
  try

    pDatasetSource.First;
    while not pDatasetSource.Eof do
      begin
        try

          { action with record }

        finally
          inc( iCount );
          pDatasetSource.Next;
        end;

      end;
  finally
    pDatasetSource.GotoBookmark( pBookmark );
    pDatasetSource.EnableControls;

    Screen.Cursor := crDefault;
  end;

DELPHI - How to show enum name as string (TFieldType)

Add System.TypInfo to your uses statement:
var
  i : integer;
  pColumn : TcxGridDBColumn;
begin
  ...
  for i := 0 to pGridView.VisibleColumnCount - 1 do
    begin
      pColumn := TcxGridDBColumn( pGridView.VisibleColumns[i] );

      ShowMessage( pColumn.DataBinding.FieldName + ' , ' + 
                   GetEnumName( TypeInfo(TFieldType), 
                                ord( pColumn.DataBinding.Field.DataType ) ) );
    end;
  ...
Output:

Thursday, March 7, 2019

Thursday, January 31, 2019

JAVA - How to solve REF/OUT method parameters

JAVA doesn`t support REF/OUT method parameters (=parameters that could be changed in method to another value).

But you can define class instance, which can be passed and used as REF:

1) Prepare this help class with one int variable:
public class ParamWrapperInt {
  public int value;
}
2) Use it:
private boolean methodName( ParamWrapperInt _maxMinorDimension ) {
  ...
  /* work with int */
  _maxMinorDimension.value = ...
  ...
  return true;
}

Wednesday, January 30, 2019

WIN OS - How to get IP address from MAC address

"arp -a" command display context of actual ARP table.

You could use this code for find IP address by MAC address:


PS: For refreshing ARP cache you can use:
netsh interface ip delete arpcache

Wednesday, January 9, 2019

AX - How to split string to parts

The field custVendExternalItem.Description contains value "PO885895/C".

For split to strings with "/" delimiter use this code:
List list = new List( Types::String );
ListEnumerator listEnumerator;
int iCounter;
...
/* split field custVendExternalItem.Description */

list = strSplit( custVendExternalItem.Description, "/" );
                                    
listEnumerator = list.getEnumerator();    
iCounter = 1;
while ( listEnumerator.moveNext() ) {                
   if ( iCounter == 1 ) tmpTable.PO_number = listEnumerator.current();
   if ( iCounter == 2 ) tmpTable.revision = listEnumerator.current();
                
   iCounter++;  
}