Thursday, September 26, 2024

SQLSERVER DATE FORMATS SAMPLES

CONVERT(VARCHAR(10), aaa, 101) +' ' + CONVERT(VARCHAR(8), aaa, 108) AS ACTION_DATE ,

o/p :-09/20/2018 10:36:00


IIF(CONVERT(INT, col) = 0, 

    CONVERT(VARCHAR(2), DAY('2014-05-17')) + '/' + 

    CONVERT(VARCHAR(2), MONTH('2014-05-17')) + '/' + 

    CONVERT(VARCHAR(4), YEAR('2014-05-17')), 

    CONVERT(VARCHAR(2), DAY(col)) + '/' + 

    CONVERT(VARCHAR(2), MONTH(col)) + '/' + 

    CONVERT(VARCHAR(4), YEAR(col))) AS DATEC





Thursday, September 19, 2024

FETCH XML from Dynamics 365

FetchXml

 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="account">

<attribute name="name" />

<attribute name="aa_accountrecordtype" />

<attribute name="aa_outletno" />

<attribute name="accountid" />

<attribute name="ownerid" />

<attribute name="parentaccountid" />

<attribute name="statecode" />

<attribute name="territoryid" />

<attribute name="aa_preferredagencymodifiedon" />

<attribute name="aa_preferredagencycomments" />

<attribute name="aa_preferred_agency" />

<attribute name="aa_additionalaccowner" />

<attribute name="aa_pref_agy_eff_start_dt" />

<attribute name="aa_pref_agy_eff_end_dt" />

<attribute name="aa_coveragecode" />

<attribute name="accountnumber" />

<order descending="false" attribute="name" />

<filter type="and">

<condition attribute="aa_accountrecordtype" operator="eq" value="100000001" />

<condition attribute="aa_outletno" operator="not-null" />

</filter>

<link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" visible="false" link-type="outer" alias="a_6ad8133d2f1e4c43a3da460bacb3d6a5">

<attribute name="name" />

</link-entity>

</entity>

</fetch>

 


Join using FetchXml

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="account">

<attribute name="name" />

<attribute name="aa_accountrecordtype" />

<attribute name="aa_outletno" />

<attribute name="accountid" />

<attribute name="ownerid" />

<attribute name="aa_preferredagencymodifiedon" />

<order descending="true" attribute ="aa_preferredagencycomments" />

<attribute name="aa_preferred_agency" />

<attribute name="aa_additionalaccowner" />

<attribute name="aa_pref_agy_eff_start_dt" />

<attribute name="aa_pref_agy_eff_end_dt" />

<attribute name="aa_coveragecode" />

<attribute name="accountnumber" />

<order descending="false" attribute="name" />

<filter type="and">

<condition attribute="aa_accountrecordtype" operator="eq" value="100000001" />

<condition attribute="aa_outletno" operator="not-null" />

</filter>

<link-entity name="systemuser" from="systemuserid" to="aa_additionalaccowner" link-type="outer" alias="su">

<attribute name="fullname" />

<attribute name="employeeid" />

</link-entity>

</entity>

</fetch>


Order rows using FetchXml

To specify the sort order for the rows in tables, use the order element within entity or link-entity elements. The default sort order is ascending.

<fetch>

  <entity name='account'>

    <attribute name='name' />

    <attribute name='accountnumber' />

    <attribute name='createdon' />

    <order attribute='createdon' />

    <order attribute='name' />

    <order attribute='accountnumber' />

  </entity>

</fetch>




Dataverse always orders attributes specified by the link-entity after attributes for the entity element.

The following example shows a conventional ordering pattern for both link-entity attributes and entity attributes.

XML
<fetch>
  <entity name='account'>
    <attribute name='name' />
    <attribute name='accountnumber' />
    <attribute name='createdon' />
    <link-entity name='account'
      from='accountid'
      to='parentaccountid'
      link-type='inner'
      alias='parentaccount'>
      <attribute name='name'
        alias='parentaccount' />
        <!-- The link-entity parentaccount name -->
      <order attribute='name' />
    </link-entity>
    <!-- The entity account name -->
    <order attribute='name' />
  </entity>
</fetch>

In this case, the results are ordered using following attributes:

  • First => account.name
  • Last => parentaccountname.name

To ensure the link-entity order is applied first, move the order element from the link-entity element to the entity element above the other order element, and use the entityname attribute on the order element to refer to the link-entity alias value.


Aggregate data using FetchXml






Tuesday, September 10, 2024

How to find special characters in a file using Notebook++

 To check if a file has special characters in Notepad++, open the file, go to the "Search" menu, select "Find", then in the "Find what" field, use the regular expression [^\x00-\x7F] to identify any non-ASCII characters (which are generally considered special characters); this will highlight any special characters present in the file. 

https://testguild.com/qtp-ascii-chr-code-chart/



 
Key steps: 
 
  • Open the file: Open the file you want to check in Notepad++. 
     
  • Access the Find function: Go to the "Search" menu and click "Find". 
     
  • Enter the regular expression: In the "Find what" field, type [^\x00-\x7F]. 
     
  • Perform the search: Click "Find Next" to identify any special characters in the document. 


  • Explanation: 
     
    • [ ]:
      Square brackets indicate a character class, meaning any character within the brackets will be matched. 
       
    • ^:
      The caret symbol when used inside a character class negates the match, so [^\x00-\x7F] means "any character that is not within the range of ASCII codes from 0 to 127". 
       
     
  • Alternative method to view special characters: 
     
    • Show all characters: Go to "View" > "Show Symbol" > "Show All Characters" to visually see all hidden characters including spaces, tabs, and line breaks, which can help identify potential special characters. 
       

SAMPLES:-

The "�" is inserted when there are two or more consecutive spaces. It is trying to convert a space to a non-breaking space, but is using the wrong character encoding. Avoid putting two spaces after a sentence to avoid the problem.'

Test again.� Test. test.

no period� no period no period

three spaces�� two spaces� one space x

Solutions:-
The problem occurs regardless of whether the checkbox is checked or not, and it occurs when the outbound encoding is UTF-8 or ISO 8859-1. the characters are hex codes EF, BF, BD, which in UTF-8 happens to be the Unicode "replacement" character to be used when the receiver does not understand the encoding.

� U+FFFD uFFFD
awk '{gsub(/�/, ""); print}'  /path/file.txt >/path/file_1.txt

ADF:
replace(replace(replace(replace(Columns,'\u00A0',''),'\u00B4',''),'\uff08','('),'\uff09',')')


ADF:-
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( account_name, '�',''),'�',''),'’',''),'¿½',''),'’',''),"''",''),'€',''),'½',''),'–',''), 'º',''), '°',''),'ª',''), '•',''), '”',''), '®',''), '™',''), '‘',''), 'Æ','E'), '–','-'), 'ß','B'), 'À', 'A'),'Á', 'A'),'Â', 'A'),'Ã', 'A'), 'Ä', 'A'), 'Å', 'A'), 'A‰', 'A'),'Ù', 'U'),'Ú', 'U'),'Û', 'U'),'Ü', 'U'), 'Ò', 'O'), 'Ó', 'O'), 'Ô', 'O'), 'Õ', 'O'), 'Ö', 'O'), 'Ø', 'O'),'Ç', 'C'),'ç', 'c'),'È', 'E'),'É', 'E'),'Ê', 'E'),'Ë', 'E'),'è', 'e'),'é', 'e'),'ê', 'e'),'ë', 'e'),'e‰', 'e'),'Ì', 'I'),'Í', 'I'),'Î', 'I'),'Ï', 'I'),'ì', 'i'),'í', 'i'),'î', 'i'),'ï', 'i'),'Ñ', 'N'),'ñ', 'n'),'ò', 'o'),'ó', 'o'),'ô', 'o'),'õ', 'o'),'ö', 'o'),'ø', 'o'),'ù', 'u'),'ú', 'u'),'û', 'u'),'ü', 'u'),'Ý', 'Y'),'ÿ', 'y'),'ý', 'y'),'ä','a'),'å','a'),'ǻ','a'),'ḁ','a'),'ă','a'),'ẚ','a'),'ắ','a'),'ằ','a'),'ằ','a'),'ẳ','a'),'ẵ','a'),'ȃ','a'),'â','a'),'ậ','a'),'ấ','a'),'ầ','a'),'ẫ','a'),'ẩ','a'),'ả','a'),'ǎ','a'),'ȧ','a'),'ǡ','a'),'ạ','a'),'ǟ','a'),'à','a'),'ȁ','a'),'á','a'),'ā','a'),'ã','a'),'ą','a'),'ą','a'),"“",' '),'\n',' '),'\r',' '),'·',''),'•',''), '™',''),'•',''),'©',''),'·',''),'—',''),'�',''),'ō','o'),'ߥ',''),'',''),'-',''),'(','('),')',')'),'—',''),'„',''),'â€',''),'Ž',''),'”',''),'—',''),'‐',''),'—',''),'‎',''),'´', ''),'>',''),'i¼ˆ', ''),'i¼‰',''),'¢',''),'¬',''),'',''),'¿',''),' ',' '),'š',''),'‚',''),'\u00A0',' '),'\u0082',''),'‚',','),'"',',')






==================================================================
regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(Columns, '�', ''), '’',''),'¿½',''), '’',"'"), '€',''),'½',''), '–',''), 'º',''), '°',''), 'ª',''), '•',''), '”',''), '®',''), '™',''), '‘',''), '–','-'), 'ß','B'), '[ÀÁÂÃÄÅA‰]', 'A'),'[ÙÚÛÜ]', 'U'), '[ÒÓÔÕÖØ]', 'O'),'[Ç]', 'C'),'[ç]', 'c'),'[ÈÉÊÊ]', 'E'),'[èéêëe‰]', 'e'),'[ÌÍÎÏ]', 'I'),'[ìíîï]', 'i'),'[Ñ]', 'N'),'[ñ]', 'n'),'[òóôõöø]', 'o'),'[ùúûü]', 'u'),'[Ý]', 'Y'),'[ÿý]', 'y'),'[äåǻḁăẚặắằẳẵȃâậấầẫẩảǎȧǡạǟàȁáāāãąą]','a')

=======
regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace (regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace (regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace (regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(regexReplace(name, '�', ''), '’',''),'¿½',''), '’',"'"), '€',''),'½',''), '–',''), 'º',''), '°',''), 'ª',''), '•',''), '”',''), '®',''), '™',''), '‘',''), '–','-'), 'ß','B'), '[ÀÁÂÃÄÅA‰]', 'A'),'[ÙÚÛÜ]', 'U'), '[ÒÓÔÕÖØ]', 'O'),'[Ç]', 'C'),'[ç]', 'c'),'[ÈÉÊÊ]', 'E'),'[èéêëe‰]', 'e'),'[ÌÍÎÏ]', 'I'),'[ìíîï]', 'i'),'[Ñ]', 'N'),'[ñ]', 'n') ,'[òóôõöø]', 'o'),'[ùúûü]', 'u'),'[Ý]', 'Y'),'[ÿý]', 'y'),'[äåǻḁăẚặắằẳẵȃâậấầẫẩảǎȧǡạǟàȁáāāãąą]','a'),'"',''),'\n',' '),'\r',' '),'"',' ')




IICS

REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1, REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1, REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1,REPLACECHR(1, COL, '�',''),'�',''),'’',''),'¿½',''),'’',CHR(39)),'€',''),'½',''),'–',''), 'º',''), '°',''),'ª',''), '•',''), '”',''), '®',''), '™',''), '‘',''), 'Æ','E'), '–','-'), 'ß','B'), 'À', 'A'),'Á', 'A'),'Â', 'A'),'Ã', 'A'), 'Ä', 'A'), 'Å', 'A'), 'A‰', 'A'),'Ù', 'U'),'Ú', 'U'),'Û', 'U'),'Ü', 'U'), 'Ò', 'O'), 'Ó', 'O'), 'Ô', 'O'), 'Õ', 'O'), 'Ö', 'O'), 'Ø', 'O'),'Ç', 'C'),'ç', 'c'),'È', 'E'),'É', 'E'),'Ê', 'E'),'Ë', 'E'),'è', 'e'),'é', 'e'),'ê', 'e'),'ë', 'e'),'e‰', 'e'),'Ì', 'I'),'Í', 'I'),'Î', 'I'),'Ï', 'I'),'ì', 'i'),'í', 'i') ,'î', 'i'),'ï', 'i'),'Ñ', 'N'),'ñ', 'n'),'ò', 'o'),'ó', 'o'),'ô', 'o'),'õ', 'o'),'ö', 'o'),'ø', 'o') ,'ù', 'u'),'ú', 'u'),'û', 'u'),'ü', 'u'),'Ý', 'Y'),'ÿ', 'y'),'ý', 'y') ,'ä','a'),'å','a'),'ǻ','a'),'ḁ','a'),'ă','a'),'ẚ','a'),'ắ','a'),'ằ','a'),'ằ','a'),'ẳ','a'),'ẵ','a'),'ȃ','a'),'â','a'),'ậ','a'),'ấ','a'),'ầ','a'),'ẫ','a'),'ẩ','a'), 'ả','a'),'ǎ','a'),'ȧ','a'),'ǡ','a'),'ạ','a'),'ǟ','a'),'à','a'),'ȁ','a'),'á','a'),'ā','a'),'ã','a'),'ą','a'),'ą','a') ,chr(160),''),chr(180),''),chr(183),''),chr(149),'') , '™',''),'•',''),'©',''),'·',''),chr(169),'')


Unable to read Japanese characters from Oracle Database with UTF8 code page defined in Informatica Cloud


REPLACECHR(0,REPLACECHR(0,REPLACECHR(0, name, 'ぁあぃいぅうぇえぉおかがきぎくぐけげこごさざしじすずせぜそぞただちぢっつづてでとどなにぬねのはばぱひびぴふぶぷへべぺほぼぽまみむめもゃやゅゆょよらりるれろゎわゐゑをん',''), 'ァィゥェォカガキギクケコサザシジスセソタチツテトナニヌネノハバパヒビピフブプヘベペホボポマミムメモャヤユヨラリルレロ',''), '千代田区丸の内二丁目番1号(丸の内パークビルディング)東京都港区赤坂赤坂Bizタワー階新闸路号邮编中央区日本橋本町欴中央区新港区東新橋西区高島一丁目1番1号神奈川県横浜市神奈川県横浜市', '')

Description
Solution


NBSP ==Non-Breaking Space (NBSP): chr(160) CRLF ==Carriage Return Line Feed (CRLF) cr = chr(13) lf = chr(10) CHR(39)='' chr(34)=='"' chr(10)=='\n' chr(13)== Carriage Return (CR) '\r',' ' '−' en_dash = chr(8210)

Here are some of the most common QTP ASCII character codes I often use:

QTP CODESYMBOLDESCRIPTION
Chr(34)Double Quotes
Chr(10)Line Feed
Chr(13)Carriage Return
Chr(32)Space

Chart for All the Valid Chr() Codes

QTP CODESYMBOLDESCRIPTION
Chr(0)NULNull char
Chr(1)SOHStart of Heading
Chr(2)STXStart of Text
Chr(3)ETXEnd of Text
Chr(4)EOTEnd of Transmission
Chr(5)ENQEnquiry
Chr(6)ACKAcknowledgment
Chr(7)BELBell
Chr(8)BSBack Space
Chr(9)HTHorizontal Tab
Chr(10)LFLine Feed
Chr(11)VTVertical Tab
Chr(12)FFForm Feed
Chr(13)CRCarriage Return
Chr(14)SOShift Out / X-On
Chr(15)SIShift In / X-Off
Chr(16)DLEData Line Escape
Chr(17)DC1Device Control 1 (oft. XON)
Chr(18)DC2Device Control 2
Chr(19)DC3Device Control 3 (oft. XOFF)
Chr(20)DC4Device Control 4
Chr(21)NAKNegative Acknowledgement
Chr(22)SYNSynchronous Idle
Chr(23)ETBEnd of Transmit Block
Chr(24)CANCancel
Chr(25)EMEnd of Medium
Chr(26)SUBSubstitute
Chr(27)ESCEscape
Chr(28)FSFile Separator
Chr(29)GSGroup Separator
Chr(30)RSRecord Separator
Chr(31)USUnit Separator
Chr(32)Space
Chr(33)!Exclamation mark
Chr(34)Double quotes (or speech marks)
Chr(35)#Number
Chr(36)$Dollar
Chr(37)%Procenttecken
Chr(38)&Ampersand
Chr(39)Single quote
Chr(40)(Open parenthesis (or open bracket)
Chr(41))Close parenthesis (or close bracket)
Chr(42)*Asterisk
Chr(43)+Plus
Chr(44),Comma
Chr(45)Hyphen
Chr(46).Period, dot or full stop
Chr(47)/Slash or divide
Chr(48)0Zero
Chr(49)1One
Chr(50)2Two
Chr(51)3Three
Chr(52)4Four
Chr(53)5Five
Chr(54)6Six
Chr(55)7Seven
Chr(56)8Eight
Chr(57)9Nine
Chr(58):Colon
Chr(59);Semicolon
Chr(60)<Less than (or open angled bracket)
Chr(61)=Equals
Chr(62)>Greater than (or close angled bracket)
Chr(63)?Question mark
Chr(64)@At symbol
Chr(65)AUppercase A
Chr(66)BUppercase B
Chr(67)CUppercase C
Chr(68)DUppercase D
Chr(69)EUppercase E
Chr(70)FUppercase F
Chr(71)GUppercase G
Chr(72)HUppercase H
Chr(73)IUppercase I
Chr(74)JUppercase J
Chr(75)KUppercase K
Chr(76)LUppercase L
Chr(77)MUppercase M
Chr(78)NUppercase N
Chr(79)OUppercase O
Chr(80)PUppercase P
Chr(81)QUppercase Q
Chr(82)RUppercase R
Chr(83)SUppercase S
Chr(84)TUppercase T
Chr(85)UUppercase U
Chr(86)VUppercase V
Chr(87)WUppercase W
Chr(88)XUppercase X
Chr(89)YUppercase Y
Chr(90)ZUppercase Z
Chr(91)[Opening bracket
Chr(92)\Backslash
Chr(93)]Closing bracket
Chr(94)^Caret – circumflex
Chr(95)_Underscore
Chr(96)`Grave accent
Chr(97)aLowercase a
Chr(98)bLowercase b
Chr(99)cLowercase c
Chr(100)dLowercase d
Chr(101)eLowercase e
Chr(102)fLowercase f
Chr(103)gLowercase g
Chr(104)hLowercase h
Chr(105)iLowercase i
Chr(106)jLowercase j
Chr(107)kLowercase k
Chr(108)lLowercase l
Chr(109)mLowercase m
Chr(110)nLowercase n
Chr(111)oLowercase o
Chr(112)pLowercase p
Chr(113)qLowercase q
Chr(114)rLowercase r
Chr(115)sLowercase s
Chr(116)tLowercase t
Chr(117)uLowercase u
Chr(118)vLowercase v
Chr(119)wLowercase w
Chr(120)xLowercase x
Chr(121)yLowercase y
Chr(122)zLowercase z
Chr(123){Opening brace
Chr(124)|Vertical bar
Chr(125)}Closing brace
Chr(126)~Equivalency sign – tilde
Chr(127)Delete

 

SQLSERVER DATE FORMATS SAMPLES

CONVERT(VARCHAR(10), aaa, 101) +' ' + CONVERT(VARCHAR(8), aaa, 108) AS ACTION_DATE , o/p :-09/20/2018 10:36:00 IIF(CONVERT(INT, col)...

DELTALAKE AZURE CLOUD DATABRICS -PYSPARK SNOWFLAKE