Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Use fields as arguments in SQL (Read 1021 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Use fields as arguments in SQL
06.01.15 at 09:06:39
 
Use fields as arguments in SQL
 
In answer to the following question:
Quote:
Hi

i have on colmn A 100 numbers a1..a100.
i want to create sql that take every field store at a1..a100 and put it as argument at the sql like ,

select count(*) from items where itno = :A1;

i want at that point to drill the sql down so it modify the argument from :A1 TO :A2 UP TO :A100
so the sql will give me results per number every row

 
I would use the SQLQueryValue worksheet function e.g. in column B. Use in B1: =SQLQueryValue("select count(*) from mytable where mycol = " & A1)
Then copy B1 to B2..B100. That gives a count of the table for each id specifically.
 
Hope this helps,
 
Best regards,
 
Gerrit-Jan Linker
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Use fields as arguments in SQL
Reply #1 - 07.01.15 at 08:00:33
 
Use fields as arguments in SQL
 
Quote:
OK

I CAN I RUN THIS
=SQLQueryValue("select
count(*) from MITMAS where mmitno ='"&A1) ?

need to close quoted from right side like : count(*) from MITMAS where mmitno ='123456')

 
To add closing single quotes you need to add "'" (double single double) after your parameter A1:
=SQLQueryValue("select count(*) from MITMAS where mmitno ='" & A1 & "'")  
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1