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.
Page Contents
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
