Case When Then In Where Clause Sql Server EDI: Electronic Document Interchange for Microsoft Great Plains – Overview for Software Developer/Pr

You are searching about Case When Then In Where Clause Sql Server, today we will share with you article about Case When Then In Where Clause Sql Server was compiled and edited by our team from many sources on the internet. Hope this article on the topic Case When Then In Where Clause Sql Server is useful to you.

EDI: Electronic Document Interchange for Microsoft Great Plains – Overview for Software Developer/Pr

Microsoft Great Plains – Microsoft Business Solutions accounting and ERP system, which is managed to its size – now, with the advancement and reliability of its database – Microsoft SQL Server, Great Plains is an attractive solution for large companies. Large companies often have purchasing and ordering systems through what is called Electronic Document Interchange or EDI. EDI was introduced a long time ago on UNIX systems and is often seen as Header, Lines and Trailer of previously defined fields.

We would like to provide you – programmer, software developer, database administrator with basic knowledge of creating EDI documents from the Microsoft Great Plains database. Please note however that Great Plains is used as an example, the same method will work for other SQL systems: Navision (SQL Database or C / SIDE database), Microsoft RMS, Solomon and Oracle and other non-Microsoft products. In the case of non-SQL systems, such as the former Great Plains Dynamics, ACCPAC on Pervasive SQL – the IDE interface will include ADO/ODBC or Microsoft Access applications.

1. Sample Query – The query below uses the CAST construct to keep the result static and to fill in its properties. In addition, this interface allows creating a Title and Details in a single selected word. Here we are dealing with Retail Products

select

case

when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CAST('Header' as char(10))

else CAST('Detail' as char(10))

end

as FIELD0,

case

when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then cast(a.CUSTNAME as char(65))

else cast(cast(b.QTYDMGED as decimal(19,5)) as char(65))

end

as FIELD01,

case

when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CONVERT(char(51), a.DOCDATE, 101)

else cast(b.ITEMDESC as char(51))

end

as FIELD03

--Additional fields go here

from SOP10100 a join SOP10200 b on a.SOPTYPE=b.SOPTYPE and a.SOPNUMBE=b.SOPNUMBE join

RM00101 d on a.CUSTNMBR=d.CUSTNMBR

join

(select SOPTYPE as SOPTYPE, SOPNUMBE as SOPNUMBE,

LNITMSEQ

as LNITMSEQ,

CMPNTSEQ

as CMPNTSEQ from SOP10200

union

select SOPTYPE as SOPTYPE, SOPNUMBE as SOPNUMBE, MIN(LNITMSEQ)-1 as LNITMSEQ, MIN(CMPNTSEQ)-1 as CMPNTSEQ from SOP10200

group by SOPTYPE, SOPNUMBE

) c on a.SOPTYPE=c.SOPTYPE and a.SOPNUMBE=c.SOPNUMBE

and ((b.LNITMSEQ=c.LNITMSEQ and b.CMPNTSEQ=c.CMPNTSEQ) or (b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1))

left join SOP10106 udf on a.SOPTYPE=udf.SOPTYPE and a.SOPNUMBE=udf.SOPNUMBE

where a.ADDRESS3'Exported' and

a.SOPTYPE=3 and upper(d.USERDEF2)='YES'

order by a.SOPTYPE, a.SOPNUMBE, b.LNITMSEQ asc

2. Mark the modified text – for this purpose we use the field SOP10100.ADDRESS3 – which was not used in the version of Great Plains Dynamics/eEnterprise 6.0:

update SOP10100 set ADDRESS3='Exported' where SOPTYPE=3

3. Communicating with a UNIX EDI Client or Server – each issue requires an individual approach. You may need to provide a directory for the file, displayed in UNIX or use an old DOS command to move the file, or you may just have an email address. A good idea is to write an interactive program in your favorite language

4. Prepared DTS package – you need to create a DTS package to do everything: call the SQL Query and save it as a file, then call a DOS command or a simple EXE program – that communicates with UNIX.

Happy programming! If you want us to do the work – give us a call at 1-866-528-0577! [email protected]

Video about Case When Then In Where Clause Sql Server

You can see more content about Case When Then In Where Clause Sql Server on our youtube channel: Click Here

Question about Case When Then In Where Clause Sql Server

If you have any questions about Case When Then In Where Clause Sql Server, please let us know, all your questions or suggestions will help us improve in the following articles!

The article Case When Then In Where Clause Sql Server was compiled by me and my team from many sources. If you find the article Case When Then In Where Clause Sql Server helpful to you, please support the team Like or Share!

Rate Articles Case When Then In Where Clause Sql Server

Rate: 4-5 stars
Ratings: 6846
Views: 67734708

Search keywords Case When Then In Where Clause Sql Server

Case When Then In Where Clause Sql Server
way Case When Then In Where Clause Sql Server
tutorial Case When Then In Where Clause Sql Server
Case When Then In Where Clause Sql Server free
#EDI #Electronic #Document #Interchange #Microsoft #Great #Plains #Overview #Software #DeveloperPr

Source: https://ezinearticles.com/?EDI:-Electronic-Document-Interchange-for-Microsoft-Great-Plains—Overview-for-Software-Developer/Pr&id=16018

Related Posts

default-image-feature

When You Touch Me I Become A Hero Ideas Rule the World

You are searching about When You Touch Me I Become A Hero, today we will share with you article about When You Touch Me I Become A…

default-image-feature

Case When Sd.Type In 2 Then Else 1 End Top Ways to Prevent Data Loss

You are searching about Case When Sd.Type In 2 Then Else 1 End, today we will share with you article about Case When Sd.Type In 2 Then…

default-image-feature

When You Re Too In Love To Let It Go How to Make a Guy Work Hard For Your Attention! Here is How to Easily Make Him Chase You Around

You are searching about When You Re Too In Love To Let It Go, today we will share with you article about When You Re Too In…

default-image-feature

Carry On When I See You Again Chords How To Play Christmas Sheet Music

You are searching about Carry On When I See You Again Chords, today we will share with you article about Carry On When I See You Again…

default-image-feature

When You Owe The Bank A Million Dollars The Infinite Benefits of a Revocable Trust

You are searching about When You Owe The Bank A Million Dollars, today we will share with you article about When You Owe The Bank A Million…

default-image-feature

When You Love Someone You Open Up Your Heart Lyrics Slaves and Cowards

You are searching about When You Love Someone You Open Up Your Heart Lyrics, today we will share with you article about When You Love Someone You…