Home>

Development terminal information: Windows10/VisualStudio2017 Professinal
Database server: Oracle12C

We are introducing a development library called ODT in Visual Studio to facilitate communication with Oracle DataBase.
I created a SQL statement that can be safely issued in the library in advance so that the result of SELECT issuance can be obtained in a typed data set.

It is as shown in image 1 below. (Results have been obtained safely)

Image 1

If i use the finished SELECT statement in the query statement of the dataset, it will look like image 2 below.

Image 2 Please let me know if you have any countermeasures. Is it a problem that the CASE statement cannot be interpreted?
SELECT A.ITEMCODE AS parent item code, TO_CHAR (SUBSTR (A.RECIPECODE, -4)) AS configuration pattern, B.INPUTORDER AS process order, B.RECIPEORDER AS output order,
'' AS alternate group code, TO_CHAR (SYSDATE,'YYYYMMDD') AS start date,
B.INPUTITEMCODE AS child item code,'R00001' AS procedure code,
C.OUTPUTVOLUME AS Parent requirement,
CASE WHEN D.STRENGTHUNITFLAGEX = -1 THEN ROUND ((B.VOLUME * ABS (B.RATIOFLG)/D.STANDARDSTRENGTHUNITRATIO), B.DECIMALPOINTDEGIT) ELSE ROUND (B.VOLUME * ABS (B.RATIOFLG), B.DECIMALPOINTDEGIT) END AS Child input,
CASE WHEN D.STRENGTHUNITFLAGEX = -1 THEN ROUND ((B.VOLUME * ABS (B.RATIOFLG)/D.STANDARDSTRENGTHUNITRATIO), B.DECIMALPOINTDEGIT) ELSE ROUND (B.VOLUME * ABS (B.RATIOFLG), B.DECIMALPOINTDEGIT) END AS Child requirement,
'0' AS Inventory Allocation Category,'' AS Substitute Item Selection Category, 1 AS Substitute Item Ratio, 0 AS Allocation Priority,
'' AS start date start time lead time,'' AS completion date start time lead time, 29991231 AS use stop date, '0' AS main raw material flag,
'0' AS Excipient Flag,'' AS Revision,'' AS Revision Date, B.BUDOMARIVOLUME AS Child Fixed Required,
'0' AS MRP exclusion flag,''' AS item category,'' AS item text,'' AS device code,
D.STANDARDSTRENGTHUNITRATIO AS Standard titer conversion factor, D.STRENGTHUNITFLAGEX AS Special titer classification
FROM SMV_RECIPEPROCESS A INNER JOIN
SMV_PROCESSINPUT B ON A.INSTRUCTID = B.INSTRUCTID INNER JOIN
SMV_PROCESSOUTPUT C ON B.INSTRUCTID = C.INSTRUCTID INNER JOIN
CMV_ITEM D ON B.INPUTITEMCODE = D.ITEMCODE
  • Answer # 1

    I was thinking for a few days
    If you make the database table name and layout of the purchased external package public, it can be troublesome, so I will not publish CREATE TABLE.
    Due to the relationship between Visual Studio and ODT with ODAC (library provided by Oracle), I have experienced the development procedure that I have to give up in the development process, but this time I will take it as such and close this case.

    Thank you for your time.