Help with SQL

[up2late]

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

[jchd]

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).

[up2late]

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

[jchd]

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.

[Guest johnlogo]

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

[jchd]

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!)

[Zedna]

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 November 23, 2011 by Zedna