Sign in to follow this  
Followers 0
up2late

Help with SQL

7 posts in this topic

Good morning,

I am hoping someone can help me out. I have a SQL query via AutoIt that will return a list of device names. I can get the list to populate to the console but I am trying to filter a specific device as a variable.

For example:

I query my database for the list of clients. I then want to display a message if the client already exists in the table. If not, just let the user know they can proceed.

Here is the script I already have. I would appreciate if someone can help me out.

Thanks!

MsgBox(1, "test", @ComputerName)

$objConn = ObjCreate("ADODB.Connection")

$objConn.Open("Provider='sqloledb';Data Source='ServerName';Initial Catalog='Database';User ID='Username';Password='PAssword';")

$rsClientName = $objConn.Execute("SELECT RTRIM(A_V_Client.ClientName) AS ClientName FROM A_V_Client WHERE A_V_Client.ClientName = ' " & @ComputerName & "'")

With $rsClientName

While Not .EOF

; ;ConsoleWrite(.Fields("ClientName").Value & " - " & .Fields("ClientUID").Value & @LF)

ConsoleWrite(.Fields("ClientName").Value & @LF)

.MoveNext

WEnd

If $rsClientName = @ComputerName Then

MsgBox(1, "DeviceStatus", "This device has not been reset.")

Else

MsgBox(1, "DeviceStatus", "Ready to Provision")

EndIf

.Close

EndWith

$objConn.Close

Share this post


Link to post
Share on other sites



Without any chance to test it, I'd try this but don't sue me if I guess wrong:

MsgBox(1, "test", @ComputerName)
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open("Provider='sqloledb';Data Source='ServerName';Initial Catalog='Database';User ID='Username';Password='PAssword';")
$rsClientName = $objConn.Execute("SELECT count(*) FROM A_V_Client WHERE ClientName like ' " & @ComputerName & "'")
With $rsClientName
Local $count = .Fields("ClientName").Value
ConsoleWrite(@ComputerName & ' found in table' & @LF)
If $count Then
  MsgBox(1, "DeviceStatus", "This device has not been reset.")
Else
  MsgBox(1, "DeviceStatus", "Ready to Provision")
EndIf
.Close
EndWith
$objConn.Close

BTW, please use quotation for inserting snippets (the blue A icon).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thank you. I had to make a slight change but it is working. Thanks again for your help!

Share this post


Link to post
Share on other sites

Few things: first you just had to check for existence, not grab potentially sevral rows. Hence you don't need a loop including .MoveNext().

Then at the SQL level, it's cautious to use LIKE instead of = if there is a potential for different casing. Not neede in your particular case.

Next, using rtrim was obviously useless since you were comparing for equality in the where clause without rtrim and that it did grab something.

Then your RowSet is an object, so you can't compare it to a string.

BTW you don't have to fully qualify columns (i.e. write <table>.<column>) when there is no ambiguity, thus shortenning statements.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Hi Friends,

I was getting the result for selected week... at the same time I would like to show the field for last year same week like this

Cant paste Image here it is like 5 column table,

this is the code

ALTER PROCEDURE [dbo].[KPI_WORK_IN_PROGESS]

@RetailWeek varchar(6), @reportType AS char(1)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @MinRetailWeek AS Varchar(6), @qtr AS varchar(7), @monthStartDate AS datetime

SELECT @qtr=qtr FROM tRetailWeeks WHERE AgrWeek=@RetailWeek

SELECT @monthStartDate=Month_Start_Date FROM tRetailWeeks WHERE AgrWeek=@RetailWeek

SELECT @MinRetailWeek = CASE WHEN @reportType='Y' THEN LEFT(@RetailWeek,4)+'01'

WHEN @reportType='Q' THEN (SELECT MIN(AgrWeek) FROM tRetailWeeks WHERE qtr=@qtr GROUP BY qtr)

WHEN @reportType='M' THEN (SELECT AgrWeek FROM tRetailWeeks WHERE Date=@monthStartDate)

WHEN @reportType='W' THEN @RetailWeek

END

SELECT Stores.Trading_Region_ID

, CASE WHEN Stores.Trading_Region_ID='0010' AND LEFT(RIGHT(Stores.Location_Type_ID,4),2)='20' THEN 'UK Concessions'

WHEN Stores.Trading_Region_ID='0010' AND (LEFT(RIGHT(Stores.Location_Type_ID,4),2)='30' OR LEFT(RIGHT(Stores.Location_Type_ID,4),2)='40') THEN 'UK Other'

WHEN Stores.Trading_Region_ID='0010' THEN 'UK Main Stores'

WHEN Stores.Trading_Region_ID='0020' THEN 'USA'

WHEN Stores.Trading_Region_ID='0030' THEN 'Europe'

WHEN Stores.Trading_Region_ID='0080' THEN 'Canada' END AS Trading_Region

, RIGHT(Stores.Location_Type_ID,4) AS Location_Type_ID

, CASE WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='20' THEN 'Concession'

WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='30' THEN 'Outlet'

WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='40' THEN 'Other'

WHEN LEFT(RIGHT(Stores.Location_Type_ID,4),2)='50' THEN 'Airport'

WHEN Stores.Trading_Region_ID='0020' THEN 'USA'

WHEN RIGHT(Stores.Location_Type_ID,2)='10' THEN 'Financial District'

WHEN RIGHT(Stores.Location_Type_ID,2)='20' THEN 'Central'

WHEN RIGHT(Stores.Location_Type_ID,2)='30' THEN 'Region' END AS Location_Type

, Trading.Store_ID

, Stores.Store_Name

, SUM(Net_Amount+Tax_Amount) AS Amount

, SUM(Transactions) AS Transactions

, SUM(Quantity) AS Quantity

FROM Sales.Stores_Weekly_Trading AS Trading

INNER JOIN Stores AS Stores ON Trading.Store_ID=RIGHT('000' + RTRIM(Stores.Store_ID),5)

WHERE Retail_Week BETWEEN @MinRetailWeek AND @RetailWeek

GROUP BY Stores.Trading_Region_ID, Trading.Store_ID, Stores.Location_Type_ID, Stores.Store_Name

ORDER By Store_ID

END

Could any one help me out Pleaseeeeeeeeee....Thank you

Logo Design

Share this post


Link to post
Share on other sites

Welcome to the forum!

Please note that hijacking a thread is against general netiquette.

Then you will have better time asking such pure SQL question on some SQL forum, preferably a forum dedicated to the engine you use.

Finally, without knowledge of your schema, readers can only make guesswork. (NOT that I ask you to post your schema here!)


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

ALTER PROCEDURE [dbo].[KPI_WORK_IN_PROGESS]

@RetailWeek varchar(6), @reportType AS char(1)

If you need to execute stored procedure then you can do it by something like this:

Local $rowcount
$oConn.Execute('dbo.KPI_WORK_IN_PROGESS', $rowcount, 132) ; adCmdStoredProc=4 + adExecuteNoRecords=0x80 (128)
Edited by Zedna

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0