I have a select query on table unify, here i want to display the "Desc" value from JKS table instead of "u.code" field
SELECT
u.id,
(
+ISNULL(u.desc,'')+' '
+ISNULL(u.code,'')+' '
)
from Unify u
another table 'JKS'
code | Desc
-------------
001 | LCC
002 | HHT
003 | ICH
004 | Unify
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
any idea When try to retrieve record from UNIFY database thru ODBC i received below error message
Server: Msg 7311, Level 16, State 2, Line 1
Could not obtain the schema rowset for OLE DB provider 'MSDASQL'. The provider supports the interface, but returns a failure code when it is used.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e21: ].
and the command I use is as follow in SQL Query Analyzer.
select * from OpenDataSource( 'MSDASQL',
'Data Source="DEVSVR_DSN";DRIVER={Unify DBIntegrator Client};UID=XXXXX;PWD=XXXX')...dnation
Thanks
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]...
I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View 2 Replies View RelatedTill now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -
Result should be -
ProCodeProductName
PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two
PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three
PRO00002TestingPromotion Two
Tables -
select * from ProMaster
CodeName
PRO00001Promotion One
PRO00002Promotion Two
PRO00003Promotion Three
select * from ProDetail
IDProCodeProduct
1PRO00001;PRO00002Product Test Searched
2PRO00001;PRO00002;PRO00002Product Final Searched
3PRO00002Testing
Dear MS SQL Experts,I have to get the number of datasets within several tables in my MSSQL2000 SP4 database.Beyond these tables is one table with about 13 million entries.If I perform a "select count(*) from table" it takes about 1-2 min toperform that task.Since I know other databases like MySQL which take less than 1 sec forthe same taskI'm wondering whether I have a bug in my software or whether there areother mechanisms to get the number of datasets for tables or the numberof datasets within the whole database.Can you give me some hints ?Best regards,Daniel Wetzler
View 5 Replies View RelatedI am wondering if there is a direct query in this case:
I am developing a program to a company which simply sells services
One service may have different prices for different types of clients
The price of any service for any client can change at any time, and I should be able to trace these changes at any time
I made the following tables (simplified): (asterisk for primary key)
(Table) (Fields)
CLIENT_TYPES : ID*, ClientTypeName
SERVICES : ID*, ServiceName
PRICES : ServiceID*, ClientTypeID*, Price, Date*
ORDERS : ID*, Date, ClientTypeID
ORDER_SERVICES : OrderID*, ServiceID*
The field in bold is the area of the question
This is a sample data in the PRICES table:
ServiceID ClientTypeID Price Date1 1 100 1/1/20072 1 150 1/1/20071 2 90 1/1/20072 2 135 1/1/2007
Now if I want to update a price of service 1 for clienttype 1, I add the following row:
1 1 100 1/1/2008
So one product for one client can have any number of prices with different dates
The following query:
SELECT * FROM PRICES WHERE ClientTypeID = 1
will retrieve all prices with repeats for a specific client (#1 here)
What I want is a query to retrieve the most recent prices for specific client for all products, even if a query on query
If there is commemts on table design please tell me
thanks for any one who provides help
How Can I select Table Dynamically from in Side SQL Query
i.e.,
Select * from (Here I want Select the Dynamically from other Query)
I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)
Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'
What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.
I had a query and i need to insert record in table and also want to select output of query.
Actually I need to insert and show data in same query.
;with cte as (
select id , status
from tbl_main
where id = 15555
)
insert into testinsert (id , status)
select * from cte
I want to convert xml data with select query and insert into table.
Xml sample like this :
<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>
[code]....
And data should be like this with select query :
NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL
I want simple query in form of above format.
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
Thanks
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
I have  created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
 ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROMÂ
(SELECT a.meu, a.mep2, SUM(a.mest) as excst        Â
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> '' Â Â Â
[code]....
Hi,
I am a newbie in SSIS.
Can anybody please help me in the following.
Here is the task that I want to achieve:
1. continously poll a db table every 1 minute,
if the value of a paticular cell in the table has changed since last poll,
then initiate the second task
2. do a select query that picks about 10,000 new rows off another db table,
the 10,000 rows should then be stored in a in-memory dataset.
Every time the poll initiates a new select query, it should insert the new rows to the existing in-memory dataset.
thus if the select runs for 2 times in 2 minutes, the the in-memory dataset would contain a maximum of 20,000 rows.
3. Then I want to apply a set of transformations on the dataset and then finally update some db tables, push some records to the ssas database. (push mode incremental processing)
which sub tasks can be achieved and which cannot.
if not, Is there a workaround?
Please do provide some specific links that accomplish some of these similar tasks.
I have tested some functionality, like
doing a full processing of a ssas database.
reading from a database table and inserting into a flat file.
I tired to use the ExecuteSQLTask, and i also assigned the resultant to an user:variable. the execution completed succesfully but I am not able to see the value of the variable change. also I am not able to use the variable to figure out a change in previous value and thus initiate a sql select. or use the variable to do anything.
Regards
Vijay R
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable.
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in
-- descending order
SELECT * FROM @tblCustomRange
WHERE (RangeMonth < @Month) OR
(RangeMonth = @Month AND RangeDay <= @Day)
ORDER BY RangeMonth, RangeDay DESC
I am getting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
2 1 27
1 1 2
3 6 10
I am expecting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
3 6 10
2 1 27
1 1 2
I have the following insert query which works great. The purpose of this query was to flatten out the Diagnosis codes (ex: SecDx1, SecDx2, etc.) [DX_Code field] in a table.
Code Snippet
INSERT INTO reports.Cardiology_Age55_Gender_ACUTEMI_ICD9
SELECT
Episode_Key,
SecDX1 = [1],
SecDX2 = [2],
SecDX3 = [3],
SecDX4 = [4],
SecDX5 = [5],
SecDX6 = [6],
SecDX7 = [7],
SecDX8 = [8],
SecDX9 = [9],
SecDX10 = [10],
SecDX11 = [11],
SecDX12 = [12],
SecDX13 = [13],
SecDX14 = [14],
SecDX15 = [15]
FROM (SELECT
Episode_Key, DX_Key,
ROW_NUMBER() OVER ( PARTITION BY Episode_Key ORDER BY DX_Key ) AS 'RowNumber', DX_Code
FROM srm.cdmab_dx_other
WHERE Episode_key is not null
) data
PIVOT
( max( DX_Code ) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13], [14], [15] )) pvt
ORDER BY Episode_Key
The query below also works fine by itself. You may notice that the Episode_Key field appears in both the query above and below therefore providing a primary key / foreign key relationship. The srm.cdmab_dx_other table also appears in both queries. I would like to add the fields in the select statement below to the select statement above. Using the relationships in my FROM statements, can anyone help me figure this one out?
Code Snippet
SELECT
e.episode_key,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(p.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(p.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(p.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(p.patient_sname)), '') AS PatientName,
CONVERT(CHAR(50), e.admission_date, 112) as Admit_Date,
CONVERT(CHAR(50), e.episode_date, 112) as Disch_Date,
e.episode_type as VisitTypeCode,
d.VisitTypeName,
convert(int, pm.PatientAge) as PatientAge,
pm.PatientAgeGroup,
pm.patientsex,
p.race
FROM srm.episodes e inner join
srm.cdmab_dx_other dxo on dxo.episode_key=e.episode_key inner join
srm.cdmab_base_info cbi on cbi.episode_key=e.episode_key inner join
srm.item_header ih on ih.item_key = e.episode_key inner join
srm.patients p on p.patient_key = ih.logical_parent_key inner join
ampfm.dct_VisitType d on d.VisitTypeCode=e.episode_type inner join
dbo.PtMstr pm on pm.AccountNumber = e.Account_Number
i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.
View 1 Replies View RelatedCan Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies View RelatedI have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies View RelatedNot sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
I have a query that has the following structure
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
When I run the query using field names the query gets much slower, and I cannot understand Why!
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)
I'm talking about three times slower using the Select MyField sintax.
Any ideas???
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TESTÂ TO Satish.
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies View Related