I'm looking for a formula like 'if=> then' I normally use in query's.
I have lots of columns but it comes down to 2: Tbl. Elementcode 1 and tbl. belnr.
I'm looking for the sql for the following:
if tbl. Elemencode 1 starts with 'C' then 'TBL. Belnr NOT LIKE 000002%'.
Could someone please help me out? Thanks!!
please see the below links .. hope these help 
It's hard to understand what you're after.
Give us a CREATE TABLE script and a handful sample data with INSERT INTO scripts. Then, based on the sample data, define what your expected results are.
I have no idea what you want. Best guess:
SELECT *
FROM tbl
WHERE CASE WHEN LEFT(tbl.[Elemencode 1], 1) = 'C'
AND tbl.Belnr NOT LIKE '000002%'
THEN 'OK'
ELSE 'NOK'
ENDhello Wim,
This is the current SQL:
SELECT TBL.ELEMENTCODE1 AS JournalElementCode_1
, TBL.ELEMENTCODE2 AS JournalElementCode_2
, TBL.BASEYEAR - 1 AS TransActionYear
, 'R' AS Kind
, TBL.GROSSAMOUNT AS Amount
, TBL.DESCRIPTION AS Description
, TBL.INVOICEURI AS InvoiceURI
, '20180101' AS InvoiceDate
, TBL.TRANSACTIONYEAR AS InvoiceNumber
, TBL.JOURNALDATE AS JournalDate
, '20180101' AS PaymentDate
, TBL.CREDDEBNUM AS DebtorNumber
, TBL.CREDDEBNAME AS Debtor
, TBL.BELNR AS VoucherNumber
, 'False' AS IsStructural
FROM [LIAS_BOEK_2019_DET.TXT] TBL
WHERE (TBL.ELEMENTCODE2 NOT LIKE '999999%') AND (TBL.BELNR NOT LIKE '000002%')
AND (TBL.ATTR_IDENT = 'COLUMN_06')
This is OK but not 100%. I want to rule out the 'TBL. BELNR NOT LIKE '000002%' but ONLY when the data in the column 'JournalElementCode_1' starts with a 'C'. if it doesnt start with a 'C' I want it too be included.
Hope this gives you the right information, I'm kinda new to this. Thanks a lot!
DYNAMIC SQL ... to the rescue ..
FOR EXAMPLE ...
DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = ' SELECT * FROM TABLE'
FROM TABLE_1
WHERE TABLE_1.NAME = 'APPLE'
SELECT @SQL = ' SELECT TOP 100 FROM TABLE_456'
FROM TABLE_1
WHERE TABLE_1.NAME = 'JEFF'
EXEC(@SQL)Try this:
SELECT TBL.ELEMENTCODE1 AS JournalElementCode_1
,TBL.ELEMENTCODE2 AS JournalElementCode_2
,TBL.BASEYEAR - 1 AS TransActionYear
,'R' AS Kind
,TBL.GROSSAMOUNT AS Amount
,TBL.DESCRIPTION AS Description
,TBL.INVOICEURI AS InvoiceURI
,'20180101' AS InvoiceDate
,TBL.TRANSACTIONYEAR AS InvoiceNumber
,TBL.JOURNALDATE AS JournalDate
,'20180101' AS PaymentDate
,TBL.CREDDEBNUM AS DebtorNumber
,TBL.CREDDEBNAME AS Debtor
,TBL.BELNR AS VoucherNumber
,'False' AS IsStructural
FROM [LIAS_BOEK_2019_DET.TXT] TBL
WHERE (TBL.ELEMENTCODE2 NOT LIKE '999999%')
AND (TBL.ATTR_IDENT = 'COLUMN_06')
AND (
(LEFT(JournalElementCode_1, 1) = 'C')
OR
(LEFT(JournalElementCode_1, 1) <> 'C' AND TBL.BELNR NOT LIKE '000002%')
)Hey Wim, I think we're on to something here but when I insert above (see pic 1) I get the next notification ERROR (07002) Microsoft) ODBC Text Driver) Too few parameters. Expected 1. 
any thoughts?
How is the query send to SQL Server? in SSMS? Access? ...?
Can you run the script from SSMS?
You can exclude those rows by using something like this:
WHERE ...
AND NOT (left(JournalElementCode_1, 1) = 'C' AND tbl.BELNR LIKE '000002%')
If JournalElementCode starts with a C - and BELNR is like '000002%' the statement will be TRUE - and the NOT will exclude those rows.
Hi Sam,
Can you run the script from SSMS? And report back?
Unfortunately not. It's all in my workspace and I use above programma for making the query's directly on the database.
That's no problem. Just use whatever tool you're used to to access the SQL Server database and submit the query I provided.
Report back what your tool returned (correct data set or error message).
ERROR (07002) Microsoft) ODBC Text Driver) Too few parameters. Expected 1.
Can you post the CREATE TABLE script for the LIAS_BOEK_2019_DET.TXT table and the INSERT INTO scripts to put some data into that table.
Before posting them on the forum. Run your CREATE TABLE and INSERT scripts on a test-database and then run the script I provided. You must get the same error (Too few parameters. Expected 1.).
I must be able to run your CREATE TABLE and INSERT scripts on a test database of mine. I'll run my SQL script and see what error I get.
What's the story behind that LIAS_BOEK_2019_DET.TXT table? It looks like a CSV-file.
Wait. Are you sending your queries to a SQL Server database or to a CSV-file over ODBC?
This is the connection to the database (see pic). It's connected to daily refreshed TXT files that comes from a SAP database.
Hi Sam,
Sorry, I can't help you any further.
I'm not familiar with ODBC connections to CSV files.
Solutions could be:
The second one may be easier to accomplish.
Hey Wim,
I've found a way. See underneath. Thanks for you time and help!
SELECT TBL.ELEMENTCODE1 AS JournalElementCode_1
, TBL.ELEMENTCODE2 AS JournalElementCode_2
, TBL.BASEYEAR AS TransActionYear
, 'R' AS Kind
, TBL.GROSSAMOUNT AS Amount
, TBL.DESCRIPTION AS Description
, TBL.INVOICEURI AS InvoiceURI
, '20190101' AS InvoiceDate
, TBL.TRANSACTIONYEAR AS InvoiceNumber
, TBL.JOURNALDATE AS JournalDate
, '20190101' AS PaymentDate
, TBL.CREDDEBNUM AS DebtorNumber
, TBL.CREDDEBNAME AS Debtor
, TBL.BELNR AS VoucherNumber
, 'False' AS IsStructural
FROM [LIAS_BOEK_2019_DET.TXT] TBL
WHERE (TBL.ELEMENTCODE2 NOT LIKE '999999%') AND (TBL.ATTR_IDENT = 'COLUMN_08')
AND (MID(TBL.ELEMENTCODE1, 1, 1) BETWEEN '0' AND '9')
UNION ALL
SELECT TBL.ELEMENTCODE1 AS JournalElementCode_1
, TBL.ELEMENTCODE2 AS JournalElementCode_2
, TBL.BASEYEAR AS TransActionYear
, 'R' AS Kind
, TBL.GROSSAMOUNT AS Amount
, TBL.DESCRIPTION AS Description
, TBL.INVOICEURI AS InvoiceURI
, '20190101' AS InvoiceDate
, TBL.TRANSACTIONYEAR AS InvoiceNumber
, TBL.JOURNALDATE AS JournalDate
, '20190101' AS PaymentDate
, TBL.CREDDEBNUM AS DebtorNumber
, TBL.CREDDEBNAME AS Debtor
, TBL.BELNR AS VoucherNumber
, 'False' AS IsStructural
FROM [LIAS_BOEK_2019_DET.TXT] TBL
WHERE (TBL.ELEMENTCODE2 NOT LIKE '999999%') AND (TBL.TRANSACTIONYEAR = 2019) AND
(TBL.BELNR NOT LIKE '000002%') AND (TBL.ATTR_IDENT = 'COLUMN_08') AND
(MID(TBL.ELEMENTCODE1, 1, 1) NOT BETWEEN '0' AND '9')
It's great you found a solution.
