Extracting Information Outside A Table
Feb 22, 2008
Hi,
I have two tables - a stock table and a transaction table. The stock table is called stock and the transaction table is called trans. The table make a point of sale database. Both table have a joining field called prod_no.
I want to extract the stock line by querying stock.prod_no that are not present in the trans table for a period of time specified.
Basically, I want to find dead stock lines that have not been sold and, therefore, are not in the transaction table but that have a stock figure. I have to enter in a start and end date paramater using the ddate field in the trans table. I have looked at left, right and outer joins but with no luck and don't even know if this is the correct query type to use.
any help would be much appreciated,
View 4 Replies
ADVERTISEMENT
May 21, 2008
Hey all
Is it possible to extract length(size) from an image?
Many thanks,
Rupa
View 13 Replies
View Related
Jan 31, 2014
I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.
I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.
Example below: >>>
The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.
Sample Data:
IDFullContent
459Authorized Privileges:
460ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466UPGRADE VOLPRO WORLD
467Default Privileges:
Output Expected:
"
ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CMKRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,
GROUP, GRPNAM,GRPPRV,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER…etcc"
The delimiter can be anything.
View 1 Replies
View Related
Jul 12, 2006
Hi all,
I am supposed to maintain a bunch of excels documenting all the mappings I'm doing in SSIS. The excels have the following format:
Target Field | Target Type | Source Table | Source Field | Source Type | Transformation Rule
Apart from being incredibly tedious, it is hard to keep every excel current, as there are other people adding and taking information from the database model.
I plan to extract this information from SSIS and create the excels dinamically, but, apart from parsing the .dtsx for each package, I see no other way of getting what I need.
Any suggestions on how I should do this? Is there an easier way?
Cheers,
David Leal
View 4 Replies
View Related
Nov 17, 2007
Hi,
I have 2 tables,
MembersTemp and Organisations
I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this.
Initiallt I only have the ExecID for the MembersTemp table
MembersType table:
ExecID 3013
OrganisationID 4550
Organisation table:
ID 4550 (PK)
Name "Microboff"
Any ideas??
View 5 Replies
View Related
Mar 22, 2007
This is my sample table Data
table1
TagID|TagName
--------------
1|xyz
2|123abc
3|a3bc
4|arsdew
5|xyc324
..............
..............
TagID--Primary Key
table2
TopicID|Topic
----------------
1|jkkj
2|kjgg
3|jhkj
4|refc
5|huhf
8|fyhj
9|jjk
...............
.............
..........
TopicID---Primary Key
table3
DetID|TagID|TopicID
-----------------------
1|1|1
2|1|2
3|1|3
4|2|4
5|2|5
8|3|8
9|3|9
....................
...................
.................
DetID---Primary key
TagID and TopicID----Foreign key
i need Topic based on TagName.
This xyz(TagName) have a 3 Topic. i need the 3 Topic
Once i select 123abc(TagName) that particular Topic I need..
any Query in Single Line.......................
View 2 Replies
View Related
Oct 13, 2007
Hi, im currently learning sql. I have a problem extracting a date from a table.
SELECT Date FROM Flight
Gives me a "syntax error in sql expression"
in the table the date coloum - fields are set Date/Time
an example field is 28.12.2007 00:00
Thanks in advanced!
Steve
p.s. im using openoffice base, if i force to run the code it display's the date. however I would like it to run without errors. Thanks again
View 4 Replies
View Related
Dec 4, 2007
I want to see all the product line provided they are there in the table or not,
i.e.
Product Line
Product Line Description
Total Usage Quantity
10000
Raw Material
0
20000
Intermediate Product
0
30000
Bearing
2713321
32000
High Strength
4258197
34000
High Temp
0
36000
Corrosion Resistant
639492
50000
High Speed
1452153
52000
Die Steel Hot Work
1614727
54000
Die Steel Cold Work
464943
88000
Misc
0
90000
Conversion
0
This is the result i am looking out for now the problem is that those record are not there in table so naturally they will not be shown in the result.
Can any one help me modifying below query? so that i can get the aforesaid result
Code Block
SELECT [PRODL] as 'Product Line'
, (case when prodl = 10000
then 'Raw Material'
when prodl = 20000
then 'Intermediate Product'
when prodl = 30000
then 'Bearing'
when prodl = 32000
then 'High Strength'
when prodl = 34000
then 'High Temp'
when prodl = 36000
then 'Corrosion Resistant'
when prodl = 50000
then 'High Speed'
when prodl = 52000
then 'Die Steel Hot Work'
when prodl = 54000
then 'Die Steel Cold Work'
when prodl = 88000
then 'Misc'
when prodl = 90000
then 'Conversion'
end)
as 'Product Line Description'
,sum(case
when [PRODL] = 10000
then [Usage Qty]
when [PRODL] = 20000
then [Usage Qty]
when [PRODL] = 30000
then [Usage Qty]
when [PRODL] = 32000
then [Usage Qty]
when [PRODL] = 34000
then [Usage Qty]
when [PRODL] = 36000
then [Usage Qty]
when [PRODL] = 50000
then [Usage Qty]
when [PRODL] = 52000
then [Usage Qty]
when [PRODL] = 54000
then [Usage Qty]
when [PRODL] = 88000
then [Usage Qty]
when [PRODL] = 90000
then [Usage Qty]
end)
as 'Total Usage Quantity'
FROM [LATCUBDAT].[dbo].[RMU]
group by prodl
order by prodl
Result of The Aforesaid Query
Product Line
Product Line Description
Total Usage Quantity
30000
Bearing
2713321
32000
High Strength
4258197
36000
Corrosion Resistant
639492
50000
High Speed
1452153
52000
Die Steel Hot Work
1614727
54000
Die Steel Cold Work
464943
View 5 Replies
View Related
Feb 5, 2006
I am using the following to extract the column names of a table. I would like to do this for the whole database. Currently I am cutting the results into an excel spread. Is there a better way of doing this? Here is the query
SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('tablename')
View 4 Replies
View Related
Sep 21, 2007
I need some help with the following...
My data source has some columns I have to 'translate' first and then insert into my destination table.
Example Source data:
key size height
1 'Small' 'Tall'
2 'Big' 'Short'
has to become
1 'Y' 'P'
2 'N' 'D'
I thought of creating a lookup table (I'm talking about the real table, not a lookup transformation table) that would have these columns: column name, value_source, value_dest
Example:
col_name vl_source vl_dest
size 'Small' 'Y'
size 'Big' 'N'
height 'Tall' 'P'
... and so on, I believe you get the point
How would you extract the needed values? Can I use a select statement in a derived column expression? Any ideas? I'm not really fond of the idea to create n lookups, one for each column I have to translate, is there a slicker solution?
View 10 Replies
View Related
Jun 3, 2008
Hello. I have a somwhat simple question.
I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted.
Iv tried a veriety of querys but none of them have managed to do this.
Could anyone help me construct this query?
View 2 Replies
View Related
Sep 22, 1999
Hello,
I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.
My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.
Sorry this is so long...
Thanks,
Brian R
WESNet Systems, NOC
View 1 Replies
View Related
Dec 4, 2014
I am trying to extract what I believe is called a Node from a column in a table that contains XML. What's the best way to do this? It is pretty straightforward since I'm not even looking to include a WHERE clause.
What I have so far is:
SELECT CCH.OrderID, CCH.CCXML.query('/cc/auth')
FROM tblCCH AS CCH
View 3 Replies
View Related
Dec 1, 2005
Hello,I am currently working on a monthly load process with a datamart. Ioriginally designed the tables in a normalized fashion with the ideathat I would denormalize as needed once I got an idea of what theperformance was like. There were some performance problems, so thedecision was made to denormalize. Now the users are happy with thequery performance, but loading the tables is much more difficult.Specifically...There were two main tables, a header table and a line item table. Thesehave been combined into one table. For my loads I still receive them asseparate files though. The problem is that I might receive a line itemfor a header that began two months ago. When this happens I don't get aheader record in the current month's file - I just get the record inthe line items file. So now I have to join the header and line itemtables in my staging database to get the denormalized rows, but I alsomay have to get header information from my main denormalized table(~150 million rows). For simplicity I will only include the primarykeys and one other column to represent the rest of the row below. Thetables are actually very wide.Staging database:CREATE TABLE dbo.Claims (CLM_ID BIGINT NOT NULL,CLM_DATA VARCHAR(100) NULL )CREATE TABLE dbo.Claim_Lines (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_LN_DATA VARCHAR(100) NULL )Target database:CREATE TABLE dbo.Target (CLM_ID BIGINT NOT NULL,LN_NO SMALLINT NOT NULL,CLM_DATA VARCHAR(100) NULL,CLM_LN_DATA VARCHAR(100) NULL )I can either pull back all of the necessary header rows from the targettable to the claims table and then do one insert using a join betweenclaims and claim lines into the target table OR I can do one insertwith a join between claims and claim lines and then a second insertwith a join between claim lines and target for those lines that weren'talready added.Some things that I've tried:INSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT T.CLM_ID, T.CLM_DATAFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID = CL.CLM_IDINNER JOIN Target.dbo.Target T ON T.CLM_ID = CL.CLM_IDWHERE C.CLM_ID IS NULLGROUP BY T.CLM_ID, T.CLM_DATAINSERT INTO Staging.dbo.Claims (CLM_ID, CLM_DATA)SELECT DISTINCT T.CLM_ID, T.CLM_DATAFROM Target.dbo.Target TINNER JOIN (SELECT CL.CLM_IDFROM Staging.dbo.Claim_Lines CLLEFT OUTER JOIN Staging.dbo.Claims C ON C.CLM_ID =CL.CLM_IDWHERE C.CLM_ID IS NULL) SQ ON SQ.CLM_ID = T.CLM_IDI've also used EXISTS and IN in various queries. No matter which methodI use, the query plans tend to want to do a clustered index scan on thetarget table (actually a partitioned view partitioned by year). Thenumber of headers that were in the target but not the header file thismonth was about 42K out of 1M.So.... any other ideas on how I can set up a query to get the distinctheaders from the denormalized table? Right now I'm considering usingworktables if I can't figure anything else out, but I don't know ifthat will really help me much either.I'm not looking for a definitive answer here, just some ideas that Ican try.Thanks,-Tom.
View 2 Replies
View Related
Jun 11, 2006
hi all,
I need to select the no of records on the basis of specified range of records.
In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..
I have used temporary table,map the table primary key to the next table with identity
but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...
Is there some other best way to extract the specified data
here is the type of query.
select * from customers where rownum between 1000 and 10000
this is in oracle
i am in need to do this in the sql server
waiting for the response...............................
View 5 Replies
View Related
Dec 6, 2007
Hi all.
This is my first SSIS project it's also an utter mare.
I'm a C# developer with reasonable SQL knowledge but I haven't used SSIS projects in BIS before.
What we have (for some insane reason) is an SQL table like so:
Column A |Column B |Column X |
Where Column X contains XML like this:
<Column C></Column C><Column D></Column D><Column ArrgWTF></Column ArrgWTF>
Where <Column ArrgWTF> contains two '-' delimited values.
What I need is some guidence (or just being told if its possible or not) to take this data and arange it like so (see bellow) in a new SQL table:
Column A |Column B |Column C | Column D |Column ArrgWTF1 |Column ArrgWTF2 |
Where A&D are from the original SQL table, C & D are pulled from the XML and ArrgWTF1 & 2 are sourced from the '-' delimited vales within the XML.
Many, many, thanks in advance.
View 6 Replies
View Related
Jan 15, 2007
Hello,I'm creating an audit table and associated triggers to be able to captureany updates and deletes from various tables in the database. I know how tocapture the records that have been updated or deleted, but is there any waythat I can cycle through a changed record, look at the old vs new values andcapture only the values that have changed?To give you a better idea of what I'm trying to do, instead of creating acopy of the original table (some tables have many fields) and creating awhole record if a type or bit field has been changed, I'd like to onlycapture the change in a single audit table that will have the followingfields;AuditID int INDENTITY(1,1)TableName varchar(100)FieldName varchar(100)OldValue varchar(255)NewValue varchar(255)AuditDate datetime DEFAULT(GetDate())Any direction would be greatly appreciated.Thanks!Rick
View 6 Replies
View Related
Nov 20, 2015
I want to calculate the target based on Flag value if Flag value is "Y" ....than MAX(Customer Target) else MAX(SLA target).Flag column contains "Y" , "N" and some blank values . Flag, Customer Target and SLA target are the columns in Table1. I have used the below formulas
Target:=IF('Table1'[ Flag]= "Y",MAX('Table1'[Customer Target]),MAX('Table1'[SLA Target]))
Target:=IFERROR(IF('Table1'[Flag]= "Y",MAX('Table1'[Customer Target]),MAX('KPI'[SLA Target])),BLANK())
View 7 Replies
View Related
Jun 29, 2012
I wanted to know that when I create a table using SQL Server, does the table metadata like schemaname and catalogname is stored in memory or in hard-disc. I am trying to extract schemaname and catalogname when the database is down or correctly when I have no connection string. I know about sysindexes, systables etc, but i am not able to understand whether it stores the data which is useful to me like catalogname.
sql query to extract the column names from sysindexes?
View 2 Replies
View Related
Nov 28, 2006
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
View 1 Replies
View Related
Jul 20, 2015
I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.
Here is the scenario:
I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).
I would like to extract Key_member where key_member_type="PM" and active as of tb1(month_end_date).
Is this possible using DAX ?
View 6 Replies
View Related
Feb 21, 2008
Hi,
In my Sql Sever i have added one Database "Devtools", by restoring it from .bak file. Now In my asp page I want to Display a Datagrid with data from a table "Devices". I want to Generate the Gridview dynamically. The columns in the table are dynamic they are not constant so I want to generate my Gridview dynamically. So I want to get the columns info of the table and based on that I want to dynamically add the columns to the table. So how to get the info of the table first. Is there any query where i can get the table info
View 3 Replies
View Related
Mar 5, 2004
Hello:
I have an app that needs to retrieve the list of tables in an SQL database. I see that there is a way to do this with the OLEdb provider (GetOleDbSchemaTable() ), but I can't find a sibling method in SQL. IS there one/ Any Ideas?
Mike
View 4 Replies
View Related
Jul 20, 2000
In oracle there is a describe command:
describe table_name
This command will tell you the columns names, datatypes, and length of the columns for that particular table. Does anyone know if there is a simliar command in SQL server that would do that? The only way that I have found so far is to do a join of the syscolumns with sysobject by table name.
View 2 Replies
View Related
Aug 18, 2000
Okay, I'll admit it: I am not a Guru. Far from it. So here is one of the zillion tasks I cannot do - unfortunately it is one that I need to have done... :
I need a recordset containing the Name, Datatype, Size, Precision, and Name_of_parent_table of all columns in all non-system tables in a given database.
Most likely this can be accomplished with one or two SPs, but which ones? And how?
Is there any Kind Soul out there, perhaps?
Peter
View 5 Replies
View Related
Jul 30, 2004
i need help printing only certain rows from within a table.. for instance, if my table looks like this :
+-------------------+----------------+----------------------+
| Description | Heading | Link |
+-------------------+----------------+----------------------+
| Google | Heading 4 | www.google.com |
| Toms Hardware | Heading 3 | www.tomshardware.com |
| Anandtech | Heading 2 | www.anandtech.com |
| check this out | Heading 1 | www.henryadams.com |
| another heading 4 | Heading 4 | www.heading4.com |
| test | Heading 3 | www.test3.com |
| heading 4 agagin | Heading 4 | laksjdf;alkf |
| new heading test | newHeadingTest | www.newheadingtest |
+-------------------+----------------+----------------------+
and i want to only print out the results for "Heading 4" under the "Heading" column, and only have the results print out once, using php.. how would i do this?
The Headings are inputed by the user through a web interface i created where the user may either select an exisiting heading or type in a completely new one that has nothing to do with any of the others.
thanks for any and all of your help..
View 1 Replies
View Related
Jan 7, 2006
Hi,
Is it possible to retrieve column information alone in the sp_columns command? I saw that it has an additional option "@column_name". But when I include it in my sql, it doesn't work the way I had expected it to..
I had tried sp_columns @table_name='mytable', @column_name='COLUMN_NAME'
I expect just the column names alone in the resultset. What am I doing wrong here?
Thanks and Regards,
Celia.
View 1 Replies
View Related
Jul 20, 2005
I am new to using sqlserver and have been given the task to get the columnname, data type and the description from a given table and put this intoanother table. Can this be done?Thanks in advance.Jeff Magouirk
View 2 Replies
View Related
Mar 14, 2008
Hi
I have a problem I am trying to ascertain the quality of the data in the table of a system we are migrating from one database to another.
We have already taken the step of converting it into SQL server to make sense of it now I wnat to query the data itself
To do this I set up a query that went through sysobjects and syclumns and listed the tables and their columns
What I want to do now is use this information to query the data elements themselves to determine where we should concentrate our data mapping on.
I started out with a cursor doing this
declare @table varchar(50)
declare @col varchar(50)
declare @result int
declare count_cursor cursor scroll
for select tname,cname from infotemp
open count_cursor
fetch from count_cursor
into @table,@col
while @@fetch_status=0
BEGIN
exec ('select count('+@col+') from '+@table)
Fetch next from count_cursor into @table,@col
END
close count_cursor
deallocate count_cursor
But I am having problems getting the counted value into the result value from the exec statement
Anybody got any ideas
Thanks
View 4 Replies
View Related
Feb 7, 2001
I would like to know how to (if it is at all possible) in SQL (or even ADO) to retrieve all the data concerning database X's
a) Tables
b) Tables column names
c) Tables column's data types
I don't want to use the doa.tabledefs object.. .i would prefer to do it in SQL, but using ADO objects (since I am developing stuff in VB) would be ok too.
Please Help.. i have been going crazy trying to find anything useful.. email me back please!
Thanks,
Matt
View 2 Replies
View Related
Apr 6, 2008
Hi,
I have a database with two handred tables.I want to get a list that have table name and numbers of record in each table.
Would you please give me a idea if you know how to do this with query or build-in stored procedure.
Thanks a lot
Mark
View 1 Replies
View Related
Jan 17, 2007
Hi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.
Is there any easy way to do this in SQL 2005?
Cheers
Gregg
View 8 Replies
View Related
Sep 19, 2007
I remember once using a command in Query Analyser that gave me all theinformation about a table. It showed details about the columns,constraints, keys, indexes etc.I can't remember how I did this? Can anyone help me?It could of been a stored proc that I called or a query on the systemtables.I do know that it was very simple to do and it was executed in QueryAnalyser.Can anyone help me?
View 2 Replies
View Related
