April 5, 2017 at 3:02 am
Hi ,
I need some help in a sql query.
I have an existing query: SELECT CAPBANK.BANK_LOAN,
CAPBANK.LOAN_YEAR,
CAPBANK.LOAN_NUM,
(SELECT TOP 1 CONVERT(VARCHAR,AR_DOCS.DOC_DATE,103) FROM AR_DOCS WHERE USERID=CAPCALL.USERID AND CATEGORYID=3 AND SUBCATEGORYID=(CASE WHEN CAPBANK.LOAN_NUM=1 THEN 11
WHEN CAPBANK.LOAN_NUM=2 THEN 37
WHEN CAPBANK.LOAN_NUM=3 THEN 38
WHEN CAPBANK.LOAN_NUM=4 THEN 39 END)
AND IS_CURRENT='Y' AND IS_ACTIVE='Y'ORDER BY ARCH_ID DESC)CREDIT_AGRMT_DATE
FROM CP_BANK_LOAN CAPBANK,CP_CAPITAL_CALL CAPCALL
WHERE CAPBANK.CAPITALID=CAPCALL.CAPITAL_ID
AND CAPCALL.CAPITAL_ID=75947
ORDER BY LOAN_NUM ;
I wish to remove 37,38,39 from the above query and trying to make it dynamic .
Here is my modified dynamic query which is getting syntax issue..
SELECT CAPBANK.BANK_LOAN,
CAPBANK.LOAN_YEAR,
CAPBANK.LOAN_NUM,
(SELECT TOP 1 CONVERT(VARCHAR,AR_DOCS.DOC_DATE,103) FROM AR_DOCS WHERE USERID=CAPCALL.USERID AND CATEGORYID=3 AND SUBCATEGORYID=(CASE WHEN CAPBANK.LOAN_NUM=1 THEN 11
WHEN CAPBANK.LOAN_NUM=2 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+CAPBANK.LOAN_NUM+'-'+CAPCALL.BankName)
WHEN CAPBANK.LOAN_NUM=3 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+CAPBANK.LOAN_NUM+'-'+CAPCALL.BankName)
WHEN CAPBANK.LOAN_NUM=4 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+CAPBANK.LOAN_NUM+'-'+CAPCALL.BankName) END)
AND IS_CURRENT='Y' AND IS_ACTIVE='Y'ORDER BY ARCH_ID DESC)CREDIT_AGRMT_DATE
FROM CP_BANK_LOAN CAPBANK,CP_CAPITAL_CALL CAPCALL
WHERE CAPBANK.CAPITALID=CAPCALL.CAPITAL_ID
AND CAPCALL.CAPITAL_ID=75947
ORDER BY LOAN_NUM ;
How do I fix this dynamic query ?
April 5, 2017 at 3:57 am
What is the error you're receiving? At a glance I can't see anything wrong, however, we can't run your code, as we don't have any Sample data. Perhaps could you provide that as well?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 5, 2017 at 4:15 am
Error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Offer Letter' to data type int.
executed this :
SELECT CAPBANK.BANK_LOAN,
CAPBANK.LOAN_YEAR,
CAPBANK.LOAN_NUM,
(SELECT TOP 1 CONVERT(VARCHAR,AR_DOCS.DOC_DATE,103) FROM AR_DOCS WHERE USERID=CAPCALL.USERID AND CATEGORYID=3 AND SUBCATEGORYID=(CASE WHEN CAPBANK.LOAN_NUM=1 THEN 11
WHEN CAPBANK.LOAN_NUM=2 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+to_char(CAPBANK.LOAN_NUM)+'-'+CAPCALL.BANK_NAME)
WHEN CAPBANK.LOAN_NUM=3 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+CAPBANK.LOAN_NUM+'-'+CAPCALL.BANK_NAME)
WHEN CAPBANK.LOAN_NUM=4 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+CAPBANK.LOAN_NUM+'-'+CAPCALL.BANK_NAME) END)
AND IS_CURRENT='Y' AND IS_ACTIVE='Y'ORDER BY ARCH_ID DESC)CREDIT_AGRMT_DATE
FROM CP_BANK_LOAN CAPBANK,CP_CAPITAL_CALL CAPCALL
WHERE CAPBANK.CAPITALID=CAPCALL.CAPITAL_ID
AND CAPCALL.CAPITAL_ID=75947
ORDER BY LOAN_NUM ;
April 5, 2017 at 4:23 am
The error is quite clear here, you have the value 'Offer Letter' and you're trying to convert it to an integer, which clearly can't be done.
Without consumable sample data, which shows the problem, I can't tell you where this is. You reference several fields with Integers, so one of those could be the cause. Without knowing your column types either though, then it could be anywhere (for example, what is the data type of CAPBANK.LOAN_NUM? If it's INT then it needs to be converted as you can't concatenate strings and numerics).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 5, 2017 at 4:26 am
for example, what is the data type of CAPBANK.LOAN_NUM? If it's INT then it needs to be converted as you can't concatenate strings and numerics
yes..how do I convert that ? I'd like to give it a try.
I'd like to convert that int to varchar there. to_char seems does not work.
April 5, 2017 at 4:29 am
Also, a more readable format, which also uses a JOIn rather than a CROSS APPLY:SELECT CAPBANK.BANK_LOAN,
CAPBANK.LOAN_YEAR,
CAPBANK.LOAN_NUM,
(SELECT TOP 1 CONVERT(VARCHAR,AR_DOCS.DOC_DATE,103)
FROM AR_DOCS
WHERE USERID = CAPCALL.USERID
AND CATEGORYID = 3
AND SUBCATEGORYID = (CASE WHEN CAPBANK.LOAN_NUM = 1 THEN 11
WHEN CAPBANK.LOAN_NUM = 2 THEN (select SUBCATEGORYID
from AR_DOCS_SUBCAT_MASTER
where subcategory = 'Credit Facility with Bank Loan -' + CAPBANK.LOAN_NUM + '-' + CAPCALL.BankName)
WHEN CAPBANK.LOAN_NUM = 3 THEN (select SUBCATEGORYID
from AR_DOCS_SUBCAT_MASTER
where subcategory = 'Credit Facility with Bank Loan -' + CAPBANK.LOAN_NUM + '-' + CAPCALL.BankName)
WHEN CAPBANK.LOAN_NUM = 4 THEN (select SUBCATEGORYID
from AR_DOCS_SUBCAT_MASTER
where subcategory = 'Credit Facility with Bank Loan -' + CAPBANK.LOAN_NUM + '-' + CAPCALL.BankName) END)
AND IS_CURRENT = 'Y'
AND IS_ACTIVE = 'Y'
ORDER BY ARCH_ID DESC) CREDIT_AGRMT_DATE
FROM CP_BANK_LOAN CAPBANK
JOIN CP_CAPITAL_CALL CAPCALL ON CAPBANK.CAPITALID = CAPCALL.CAPITAL_ID
WHERE CAPCALL.CAPITAL_ID = 75947
ORDER BY LOAN_NUM;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 5, 2017 at 4:33 am
spectra - Wednesday, April 5, 2017 4:26 AMfor example, what is the data type of CAPBANK.LOAN_NUM? If it's INT then it needs to be converted as you can't concatenate strings and numerics
yes..how do I convert that ? I'd like to give it a try.
I'd like to convert that int to varchar there. to_char seems does not work.
You've got a CONVERT in your statement already, it'll be the same format as that, apart from you don't need a style:CONVERT(varchar([x]), [MyField])
CAST and CONVERT (Transact-SQL)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 5, 2017 at 5:18 am
spectra - Wednesday, April 5, 2017 4:15 AMError:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Offer Letter' to data type int.executed this :
SELECT CAPBANK.BANK_LOAN,
CAPBANK.LOAN_YEAR,
CAPBANK.LOAN_NUM,
(SELECT TOP 1 CONVERT(VARCHAR,AR_DOCS.DOC_DATE,103) FROM AR_DOCS WHERE USERID=CAPCALL.USERID AND CATEGORYID=3 AND SUBCATEGORYID=(CASE WHEN CAPBANK.LOAN_NUM=1 THEN 11
WHEN CAPBANK.LOAN_NUM=2 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+to_char(CAPBANK.LOAN_NUM)+'-'+CAPCALL.BANK_NAME)
WHEN CAPBANK.LOAN_NUM=3 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+CAPBANK.LOAN_NUM+'-'+CAPCALL.BANK_NAME)
WHEN CAPBANK.LOAN_NUM=4 THEN (select SUBCATEGORYID from AR_DOCS_SUBCAT_MASTER where subcategory = 'Credit Facility with Bank Loan -'+CAPBANK.LOAN_NUM+'-'+CAPCALL.BANK_NAME) END)
AND IS_CURRENT='Y' AND IS_ACTIVE='Y'ORDER BY ARCH_ID DESC)CREDIT_AGRMT_DATE
FROM CP_BANK_LOAN CAPBANK,CP_CAPITAL_CALL CAPCALL
WHERE CAPBANK.CAPITALID=CAPCALL.CAPITAL_ID
AND CAPCALL.CAPITAL_ID=75947
ORDER BY LOAN_NUM ;
Is this Oracle?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 5, 2017 at 11:21 am
Two things. To_char is an Oracle function, and this is a SQL Server site. The problem with your syntax error is likely to have been caused by the data type of the SUBCATEGORYID field, which is probably NOT the same as the implied data type of the first possible result in your CASE statement - 11; which is likely to be int, so if SUBCATEGORYID is varchar, that's the cause. If that's it, you can change 11 to '11' and possibly solve the problem, but that might still result in char(2) instead of varchar, so you might need to CAST(11 AS varchar(n)) where the varchar(n) matches the data type exactly of the SUBCATEGORYID field.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 5, 2017 at 11:27 am
One more thing. That CASE statement does not need to have it's latter elements repeated that way. Try the following as an alternative, where I'll assume that SUBCATEGORYID is a varchar(10):
(CASE WHEN CAPBANK.LOAN_NUM = 1 THEN CAST(11 AS varchar(10))
WHEN CAPBANK.LOAN_NUM IN (2,3,4) THEN
(
select SUBCATEGORYID
from AR_DOCS_SUBCAT_MASTER
where subcategory = 'Credit Facility with Bank Loan -'+ CAST(CAPBANK.LOAN_NUM AS char(1)) + '-' + CAPCALL.BANK_NAME
)
END)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply