Query Using Variable - DB2

May 27, 2008

Hi all,
Sorry if this is the wrong place to ask but I have tried a few places with no joy.
In SQL when querying using a variable I can use @ symbol.
ie:
SELECT COUNT(*) FROM dbo.Persons WHERE SocialSecurityNumber = @SocialSecurityNumber
Then in say visual web developer this would prompt me to enter the variable for @SocialSecurityNumber
This all works perfect.
My issue is when I query a DB2 DB directly say
SELECT COUNT(*)
FROM DB2.table
WHERE (MMITNO = @MMITNO)
I get ther error "Column @MMITNO does not exist"
My question is. What symbol do I use to represent variable in DB2.
In SQL it is @ ..I think these symbols are known as sigils
Please Help
Ray..

View 3 Replies


ADVERTISEMENT

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;


I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

View 4 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?

Nov 15, 2007

do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like

SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)

View 1 Replies View Related

Using A Variable For A Column Name In A Query

Sep 22, 2006

Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE      CASE WHEN @Field = 'Fname' THEN Fname = @Value END,     CASE WHEN @Field  = 'Lname' THEN Lname = @Value END,     CASE WHEN @Field  = 'Phone' THEN Phone = @Value END;

View 1 Replies View Related

Sql Query Variable Problem

Aug 8, 2004

Hello


I have a sql statement that i want to assign a variable to a field. Can anybody tell me how to do it.



queryString = "select [problemdescr] from [Problemlist] where [problemnumber] = abc "



what i want is to assign the value of the variable abc to problemnumber. I have tried to use @ to solve the problem, but i couldn't.


any help

View 5 Replies View Related

New, Need Help: Build Query With Variable

Oct 12, 2007

I am building my SELECT on the fly by use of IF statements something like:
SET @searchParam = @searchParam + ' AND tblXYZ.country = ' + @searchStringCountry

Then I am trying to use @searchParam as my WHERE clause:
WHERE@searchParam

How do I get the string out of @searchParam to use as my WHERE clause?

Thanks in advance!

View 1 Replies View Related

How Do I Use A Variable In A Select Query

Nov 10, 2005

How do i use a variable in a select query
---------------
Declare @host varchar(20)
set @host = '"Fab"'

Declare @DB_name varchar(25)
set @DB_name = 'config_mpl_18May2005'

Declare @Full_Path varchar(60)
set @Full_Path = @host + '.' + @DB_name + '.dbo.cfg_dn'
Print '@Full_Path = ' + @Full_Path

------ cfg_dn ----------

Print 'Starting on CFG DN'

select * from @Full_Path

where dbid = 5441 or dbid = 5389 or dbid = 562


Error:

Server: Msg 137, Level 15, State 2, Line 16

Must declare the variable '@Full_Path'.

View 1 Replies View Related

Passing A Query Into A Variable

Aug 9, 2007

What is the easiest way to pass a value into a variable...
TSQL...

declare @@test int

--select count(*) from authors
select count(*) into @@test from authors

View 1 Replies View Related

How Do I Set A Variable Value Based On A SQL Query

Apr 25, 2007

I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done.

google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it.

Here is the basic gist of my query:

exec SP_GET_FILE_NAME @file_name OUTPUT

I need to take the resulting @file_name and store it in an ssis variable. How?

View 1 Replies View Related

System Variable In A Query

Jan 23, 2008

Hi ,
Is it possible to use a system variable in a 'Execute sql task'
or any other query component in SSIS?

View 4 Replies View Related

Query Result In A Variable

Sep 5, 2006

Hello Again, did you miss me?

ok now i have this problem. I need to make a query that return one single data. I need to store this data in a variable. Something like that:

DECLARE @A VARCHAR(50)

@A=SELECT mycolumn FROM mytable WHERE mycolumn = 'Something'

There is a form to do it?

Thanks Friends

View 1 Replies View Related

Passing A Variable To SQL QUERY

Oct 24, 2007



Hi
I am extracting data from Oracle via SSIS. There are three smilar schemas from where the data has to be extracted.
Say My query is " Select * from abc.dept" where abc is the schema name. I want to pass this schema name through a variable and it should loop as there are total 3 schemas. There is a table which provided list of schemas.
Can somebody please guide me how to do this. There are multiple references of this table is SSIS package.

View 11 Replies View Related

Declaring A Sql Query To A Variable...

Sep 10, 2007

Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?

-Thanks,
Rich

View 5 Replies View Related

How To Store The Results Of A Query In A Variable.

Jun 25, 2007

Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.

View 2 Replies View Related

Assigning Data From An SQL Query To A Variable

Apr 27, 2006

Hello all,
for a project I am trying to implement PayPal processing for orders
public void CheckOut(Object s, EventArgs e)    {        String cartBusiness = "0413086@chester.ac.uk";        String cartProduct;        int cartQuantity = 1;        Decimal cartCost;        int itemNumber = 1;        SqlConnection objConn3;        SqlCommand objCmd3;        SqlDataReader objRdr3;        objConn3 = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);        objCmd3 = new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);        objConn3.Open();        objRdr3 = objCmd3.ExecuteReader();        cartProduct = "Cheese";        cartCost = 1;        objRdr3.Close();        objConn3.Close();                cartBusiness = Server.UrlEncode(cartBusiness);        String strPayPal = "https://www.paypal.com/cgi-bin/webscr?cmd=_cart&upload=1&business=" + cartBusiness;        strPayPal += "&item_name_" + itemNumber + "=" + cartProduct;        strPayPal += "&item_number_" + itemNumber + "=" + cartQuantity;        strPayPal += "&amount_" + itemNumber + "=" + Decimal.Round(cartCost);        Response.Redirect(strPayPal); 
 
Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query.
So I want cartProduct = Title and cartCost = Price from the SQL query.
How do I do this?
 
Thanks
 

View 1 Replies View Related

Storing Query Results In A Variable

Mar 28, 2001

Can someone tell me how to store the results of a multi-row query in a text or varchar variable so that I might do something like this:

declare @var1 text

select @var1 = {results of select date, count(*) from testtable2 group by date)


Thanks!

joe

View 2 Replies View Related

Simplest Way To Get A Query Result Into A Variable

May 25, 2004

Hello,

I am interested in what the simplest was to get a query result that will only ever have one result (ie One column, one row) into a variable. An ugly way is to use a cursor that simply fetches the first row but that seems to be a horrible way to do it and it has sometimes major drawbacks sometimes (mainly if I have to dynamically choose the table). Surely there is a better way?

What do you think? A simple example would be nice.

Cheers
J

View 11 Replies View Related

Database Name As Variable In A Join Query In SQL

Feb 2, 2007

I have a very simple Join Query like
Select A, B , C
From Database1.dbo.Table1 Inner Join Database2.dbo.Table2

Now problem is that Database name for the project freuqently changes though Table1 and Table2 names remain constant.
How can I store Datbase name at a central place in a table or as a constant so that I can use them in above query. It will allow me to change them at one place without making changes in all the sps where they are used

View 1 Replies View Related

QUERY Exceeding Variable Max Length

Jul 20, 2005

Hi,A query is exceeding the length of varchar and nvarchar variable.Because I'm picking the data from each record from table and giving itto the query.suggest me some way to do it.sample query:SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotalFROM (SELECT Year,SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) ASQ1,SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) ASQ2,SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) ASQ3,SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4FROM Pivot1 AS PGROUP BY P.Year) AS P1GO---> even the P.QUARTER .... FIELD NAME IS BEING GENERATEDDYNAMICALLY.MY QUERY IS EXCEEDING VARCHAR AND NVARCHAR LIMIT.THANX IN ADV.

View 1 Replies View Related

Can We Set Result Of Dynamic Query To Variable?

Dec 13, 2007

Is this possible as given below

declare @Qry as varchar(8000)
declare @Cnt as int
begin
set @Qry = 'select @Cnt=count(*) from ' + @TableName
exec @Qry
select @Cnt
end


But its not working....

can any one help me out in this.....

Thnx

Parag

View 1 Replies View Related

How To Assign Query Results Into A Variable

Apr 4, 2008

Hello,

Could anyone help me please,

I need a select to add the results into a variable of type string

id(int) name (varchar2)
1 a
2 b,c
3 d,e,f,g


tried wit the following:
DECLARE @variable1
SELECT @variable1 = name
FROM table

The problem with this sentece is that the only value assigned to @variable1 is the last row value, i.e.
@variable1 = "d,e,f,g"

What I need is to concatenate all values returned by the query, i.e.
@variable1 = "a,b,c,d,e,f,g"


Thank you very much in advance,

Marco

View 9 Replies View Related

Loop By Passing Variable Into Query

Nov 2, 2007



Hi all,

I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.

The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.

View 3 Replies View Related

SQL Query Results Into Local Variable

Oct 11, 2006

Hi,

I'm trying to put the results from a SQL query that returns only one filed but one or more rows into a local variable in a comma separated format.

Any help is appreciated.

Thanks.

View 2 Replies View Related

Assign A Variable Based Upon Query Results...how To Do It?

Feb 23, 2007

I have the following code which is incomplete.  Where it says: txtVendorID = I need it to equal the results of the field VendorID from my query...here is my code.  What do I need to add there?
 
Dim cmdSelect As SqlCommandDim intRecordIDintRecordID = Request.QueryString("RecordID")strConn = ConfigurationManager.AppSettings("conn")conn = New SqlConnection(strConn)cmdSelect = New SqlCommand("spMfgRepListAddaspxByRecordID", conn)cmdSelect.CommandType = CommandType.StoredProcedurecmdSelect.Parameters.AddWithValue("@RecordID", intRecordID)conn.Open()cmdSelect.ExecuteReader()txtVendorID.Text = conn.Close()

View 2 Replies View Related

How To Subtract Days From Date Variable In Sql Query?

Mar 12, 2007

Hi,
I've searched quite a bit for help with this syntax but have given up.
I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date.  I have tried this and it's incorrect syntax:
WHERE   (fldDate < ({ fn NOW() - 500 })
Can someone please help me out with correct syntax for this?  thanks much.
 

View 3 Replies View Related

Passing Variable To Where Clause In Sql Query From A Grid

Mar 28, 2007

Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience.
Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it.
///javascript 
function DisplayReciept(transactionnum)
{
recieptdis = transactionnum;
 
}
 
////field in grid 
<asp:BoundField htmlEncode=false DataFormatString="<a href=javascript:DisplayReciept{0}>Display</a>" DataField="transid" HeaderText="Show Reciept" SortExpression="transid" />
 
 //////////////query//////////////
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbsgcConnectionString %>"
SelectCommand="SELECT [fulldata] FROM [data] WHERE ([transid] = @transid)">
<SelectParameters>
<asp:FormParameter FormField="transid" Name="transid" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

View 1 Replies View Related

Using A Date Function To Declare A Variable Used In A SQL Query

Feb 1, 2006

Hi all can you help me, I know that I am doing some thing wrong. What I need to do is set a variable to the current date so I can use it in a SQL query to an access database. This is what I have so far
<script runat="server"">
Sub Page_Load
dim --all the variables for my sql connections--
dim ff1 As Date
then my sql connection and queries
sql="SELECT FullRate, " & ff1 &" FROM table1 WHERE hotelnumber = " & hotel
This works is I set ff1 as a string and specify the string (my column headings are set as dates in my table)
dim ff1 As string
ff1="30/01/2006"
but I need ff1 to be the current date and is I use ff1 As date it returns time and date
Is there any way to set ff1 to the current date in this format "dd/mm/yyyy"
 
Any help is greatly appreciated

View 2 Replies View Related

Variable Declaration In A Stored Procedure Query

Feb 15, 2002

I have the following lines embedded in a stored procedure

---
----
----
select @querystr = "select @lkinpos = pos_lkin_pos from "+@database+" where pos_clnt_id = "+str(@clntid)+" and pos_isin ="+"'"+ @isinno+"'"

print @querystr
exec(@querystr)
---
---
---

When i execute the above stored procedure, it returns an error as follows:

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@lkinpos'.

When i see the print @querystr statement, it returns the query str as follows:

select @lkinpos = pos_lkin_pos from nsdldpm..pos_mstr where pos_clnt_id = 10000045 and pos_isin ='ine227a01011'

This when executed independently, gives me the required output.

Can anybody solve this for me?
Thanks in advance

View 1 Replies View Related

Using A Variable That Stores The Query String For WHERE Condition

Feb 20, 2001

Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.

View 2 Replies View Related

Apply The Result Of A Query That Includes Variable

Jun 19, 2008

The following query is failing when trying to apply the
MAX(field_x_order)
to the variable @max
Note the x is represented by the string variable @stri

declare @i int
declare @stri varchar(10)
declare @max int
set @i = 18
set @max = 0
while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
select @max = MAX(field_ + @stri + _ORDER) FROM table_name WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near the keyword 'IS'.
exec ('UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL')
end

I have also tried:
select MAX(field_ + @stri + _ORDER) INTO @max = FROM display_1a WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near '@max'.

and:
select @max = ('SELECT MAX(field_' + @stri + '_ORDER) FROM display_1a WHERE field_' + @stri + ' IS NOT NULL') -- error: Conversion failed when converting the varchar value 'SELECT MAX(field_19_ORDER) FROM display_1a WHERE field_19 IS NOT NULL' to data type int.

Thanks,
Lou

Lou

View 9 Replies View Related

Variable On Open Query - Insert Statement

Nov 6, 2014

i have a linked server that i use a variable to get the last invoice date and only pull in the latest records that are after the last invoice date but i dont know how to insert that into a table?

DECLARE @TSQL varchar(MAX), @LastDate As Datetime, @LastRunDate As Varchar(6)
SET @LastDate = (SELECT MAX(OrderDate) FROM [SYNC_REPORT].[DPY_SyncReport_Prod].[Stage].[Sales])
Set @LastRunDate = ( select right(convert(char(4), year(@LastDate)), 2) + right('000' + convert(varchar(3), datediff(dd, convert(datetime, '01/01/' + convert(char(4), year(@LastDate))), @LastDate+1)), 3) +100000 )

[code]....

but i get msdtc on server xxxxx is unavaiable?how to get my linked server query into my table

View 3 Replies View Related

Change Local Variable Inside Query

Jul 20, 2005

/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved