Hi
I am working on ProIV technology, in which i am writing SQL Query to extract data from the MS SQL Server Table and putting it into another table. Now the problem is that, there is some values which are passed through variable into a column, on some specific condition. For that i am using CASE statement within the SQL Statement with variables. So Please somebody help me out to put the values into the SQL TAble through variables.
The stament which i am using is as follows, in which the text writen in RED is showing error
SQL DYNAMIC
INSERT INTO EC_TEMP_COST_CATEGORY
SELECT Company_Code, :$XTERM, Cost_Category_Code, Cost_Category_Description,
Cost_Category_Type, GL_Debit_Account,
CASE Cost_Category_Type
WHEN '1' THEN 'FUEL'
WHEN '2' THEN 'LUBE & OIL'
WHEN '3' THEN 'TIRES'
WHEN '4' THEN :$TMP_CC4
WHEN '5' THEN :$TMP_CC5
WHEN '6' THEN :$TMP_CC6
WHEN '7' THEN :$TMP_CC7 END
FROM EC_COST_CATEGORY
WHERE Company_Code = :$XCOMP :$SSEL1 :$SSEL2
ENDSQL
Thanx
Prasoon
Click the link below to see the new game I'm developing!
ProIV with SQL
Started by
Guest_Prasoon Gupta_*
, Oct 12 2004 05:06 AM
2 replies to this topic
#2
si_ddharths1
-
- Members
-
- 9 posts
Newbie
- Gender:Male
Posted 12 October 2004 - 09:29 AM
Hi Prasoon,
below is an example of the script which is for oracle server -
$TEST = 'BBB'
SQL
INSERT INTO SIDTEST
SELECT SID_TERM,SID_1,
CASE WHEN SID_2 = '111' THEN :$TEST
ELSE 'TTT' END SID_2
FROM SIDTEST1
ENDSQL
below is an example of the script which is for oracle server -
$TEST = 'BBB'
SQL
INSERT INTO SIDTEST
SELECT SID_TERM,SID_1,
CASE WHEN SID_2 = '111' THEN :$TEST
ELSE 'TTT' END SID_2
FROM SIDTEST1
ENDSQL
#3
mdexter
-
- Members
-
- 70 posts
Advanced
- Gender:Male
Posted 26 October 2004 - 05:19 PM
How are your variables $TMP_CC4, $TMP_CC5, etc. defined? Do you have single quotes around them, such as
$TMP_CC4 = "'TEST VALUE'"
?
As you probably know, all variables with SQL DYNAMIC must have single quotes added manually, if they are needed in the SQL statement. If this isn't the problem, let me know and I'll try it here. We use the CASE construct a lot with SQL Server and it works well.
Mark Dexter
$TMP_CC4 = "'TEST VALUE'"
?
As you probably know, all variables with SQL DYNAMIC must have single quotes added manually, if they are needed in the SQL statement. If this isn't the problem, let me know and I'll try it here. We use the CASE construct a lot with SQL Server and it works well.
Mark Dexter
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
