Showing posts with label single. Show all posts
Showing posts with label single. Show all posts

Friday, March 30, 2012

Query help

Hi,
I have a single row in a table:

Title Desc Quantity
----------
aaaa bbbbb 4

and I need the query to insert "Qty" number of records into a second table, e.g

Title1 Desc1
------
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb
aaaa bbbbb

I reckon its some sort of self join but any help would be appreciated.

gregFor the example below I use a function, but you can also use any table that contains sequencial numbers with no gaps. A table with IDENTITY field that did not have any deletes would do.

set nocount on
create table t1 (
title char(4) not null,
[desc] varchar(50) not null,
quantity int not null)
go
create table t2 (
title1 char(4) not null,
desc1 varchar(50) not null)
go
insert t1 values ('aaaa', 'bbbbb', 4)
insert t1 values ('bbbb', 'ccccc', 1)
insert t1 values ('cccc', 'ddddd', 3)
insert t1 values ('dddd', 'eeeee', 2)
insert t1 values ('eeee', 'fffff', 5)
go
insert t2
select title, [desc] from dbo.fn_CartesianProduct() f
inner join t1 on f.[id] < t1.quantity order by 1
go
drop table t1, t2
go|||Excellent - I have an Integers table that substitutes nicely.
Thanks.

Query Help

Dear All,
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql

Friday, March 23, 2012

Query for multiple values

I was able to create a query that returns data based on a single order
number. Now I need to create a query that returns data based on
multiple order numbers. Obviously, when I run this query, it prompts me
for an order number. How do I change this so I can enter multiple order
numbers?
SELECT ORDNUMBE, CUSTNAME
FROM SOP10100
WHERE (ORDNUMBE = @.ordnumbe)
Thanks!http://www.sommarskog.se/arrays-in-sql.html
<2retread@.gmail.com> wrote in message
news:1126021459.621452.89010@.z14g2000cwz.googlegroups.com...
>I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||See if this helps:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"2retread@.gmail.com" wrote:

> I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
In SQL Server, the VALUES() table constructor has to be faked with
(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).|||On 6 Sep 2005 20:19:28 -0700, --CELKO-- wrote:

>CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
>INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
>BEGIN
>SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
><more code>;
>END;
>In SQL Server, the VALUES() table constructor has to be faked with
>(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).
Hi Joe,
And how exactly is your query better than
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (p1, p2, .., pN)
AND <more predicates>;
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I usually wind up adding some CAST(), UPPER() and other procedures to
the SELECTs or in theVALUES() list.|||On 7 Sep 2005 12:49:10 -0700, --CELKO-- wrote:

>I usually wind up adding some CAST(), UPPER() and other procedures to
>the SELECTs or in theVALUES() list.
Hi Joe,
Assuming that you meant "functions", not "procedures", there's still no
reason to make it so complicated:
WHERE my_col IN (UPPER(p1), CAST(p2 AS verchar(10), ..., LOWER(pN))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Assuming that you meant "functions", not "procedures", there's still no r
eason to make it so complicated: <<
Opps! I grant that you can do a lot of edit work with CASE epxrssions,
too:
CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
But it is hard to do things that involve another table, such as
translating codes in the parameters to the target table format::
(SELECT shoesize_eur
FROM ShoeSizes.
WHERE UPPER (shoesize_usa) = @.p1)
or multiple-parameter look-ups:
(SELECT address_nbr
FROM StandardAddresseNumbers AS A
WHERE CAST (x_ord AS INTEGER) = @.p1
AND CAST (y_ord AS INTEGER) = @.p2)|||On 8 Sep 2005 07:57:01 -0700, --CELKO-- wrote:

>Opps! I grant that you can do a lot of edit work with CASE epxrssions,
>too:
>CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
>But it is hard to do things that involve another table, such as
>translating codes in the parameters to the target table format::
>(SELECT shoesize_eur
> FROM ShoeSizes.
>WHERE UPPER (shoesize_usa) = @.p1)
>or multiple-parameter look-ups:
>(SELECT address_nbr
> FROM StandardAddresseNumbers AS A
> WHERE CAST (x_ord AS INTEGER) = @.p1
> AND CAST (y_ord AS INTEGER) = @.p2)
Hi Joe,
Assuming that we're still discussing what can and can't be placed in an
IN (expression, expression, ...) string, I still fail to see how CASE
expressions and subqueries like the examples you posted would introduce
the need to move from IN with a list of expressions to IN with a
subquery, like the one you posted at the start of this thread.
I don't have the full SQL-92 standard at my disposal, but Books Online
says this:
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
(snip)
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of
the same type as test_expression.
There are no further requirements for the expressions in the list of
expressions. So you should bne able to just slam the subqueries in
there.
Of course, the end result would be quite complicated - but adding an
extra layer of complexity by using a subquery with SELECT .. FROM VALUES
(...) would make it even more complicated, not less so!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 12, 2012

Query Editor: how to display individual row vertically

In query editor I displayed a single row from a table. The row is so long that I need to scroll horizontally back and forth to check out it's fields. Using t-sql (or otherwise) can I display the row like this: (vertically)

\

Field Name 1: < data value 1>

Field Name 2: < data value 2>

Field Name 3: < data value 3>

Field Name 4: < data value 4>

etc.

TIA,

barkingdog

Dog:

If you are using SQL Server 2005, you ought to be able to use an UNPIVOT


Dave

|||

Dog:

That SQL 2005 code might look something like this:

-- -
-- In order for UNPIVOT to work, the data must be homogeneous.
-- For this reason, all of the fields are transformed into a
-- varchar (40) string.
--
-- Also note the ISNULL function on field 3. If this is not
-- done, this row will not be displayed.
-- -
select convert (varchar (25), FieldName + ':') as FieldName,
FieldValue
from ( select convert (varchar (40), 'This is a test.')
as [Field Name 1],
convert (varchar (40), 'This is the 2nd field.')
as [Field Name 2],
convert (varchar (40), isnull (null, '[Null]'))
as [Field Name 3],
convert (varchar (40), 45.27)
as [Field Name 4]
) x
unpivot (FieldValue for FieldName
in ( [Field Name 1], [Field Name 2],
[Field Name 3], [Field Name 4]
)
) as xx

--
-- Sample Output:
--


-- FieldName FieldValue
-- - -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- Field Name 3: [Null]
-- Field Name 4: 45.27

-- (4 row(s) affected)

|||

If you are using SQL 2000 you might try something like:

select 'Field Name 1:' as FieldName,
'This is a test.' as fieldValue
union all
select 'Field Name 2:',
'This is the 2nd field.'
union all
select '...', ' '
union all
select 'Field Name N: ', 'Nth piece of data'


--
-- Sample Output:
--


-- FieldName fieldValue
-- -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- ...
-- Field Name N: Nth piece of data

-- (4 row(s) affected)

Friday, March 9, 2012

Query Date Type - Somebody help me!p

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.where u have

Select * from Apro where data between Inicio and Final

should data be date ?? --> Date - Datetime
or u did that on purpose

Can u output the record source that is being executed ?

Query Date Type


Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates
with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Leonardo,

There are two problems in your code:

1. Parameters should start with a colon. (e.g. :Inicio and :Final)
2. You have used Parameters[0] and Parameters[2], what about Parameters[1]!?

So this is the correct code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'Select * from Apro where data between Inicio and Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Good luck,
Shervin

"Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
news:3f8d396f$0$199$75868355@.news.frii.net...
>
> Hi,
> I have a table with the follow fields :
> ID - Int
> Date - Datetime
> I need to make a simple query to result the records between to dates
> with a single ID.
> Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> Im using the follow :
> ADOQuery1.Close;
> ADOQuery1.SQL.Clear;
> ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> Final');
> ADOQuery1.Parameters[0].Value:=Inicio;
> ADOQuery1.Parameters[2].Value:=Final;
> ADOQuery1.Open;
> When I open the query it doesnt work cos its result a null set
> How can I solve this?
> Im using SQL Server 2000 and Delphi 6
> Thanks for atention.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Oops! Sorry I forgot to add colons before parameters :-) This is the correct
code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'select * from Apro where data between :Inicio and :Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Shervin

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:voquva8in6re84@.corp.supernews.com...
> Leonardo,
> There are two problems in your code:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;
> Good luck,
> Shervin
>
> "Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
> news:3f8d396f$0$199$75868355@.news.frii.net...
> > Hi,
> > I have a table with the follow fields :
> > ID - Int
> > Date - Datetime
> > I need to make a simple query to result the records between to dates
> > with a single ID.
> > Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> > Im using the follow :
> > ADOQuery1.Close;
> > ADOQuery1.SQL.Clear;
> > ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> > Final');
> > ADOQuery1.Parameters[0].Value:=Inicio;
> > ADOQuery1.Parameters[2].Value:=Final;
> > ADOQuery1.Open;
> > When I open the query it doesnt work cos its result a null set
> > How can I solve this?
> > Im using SQL Server 2000 and Delphi 6
> > Thanks for atention.
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
> Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;

Shervin, did you not for get the colons?

Select * from Apro where data between :Inicio and :Final

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You are right, I forgot them. I fixed it.
Thanks Erland.

Shervin

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9415F34D56661Yazorman@.127.0.0.1...
> Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> > 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> > 2. You have used Parameters[0] and Parameters[2], what about
> > Parameters[1]!?
> > So this is the correct code:
> > with ADOQuery1 do
> > begin
> > Close;
> > SQL.Text := 'Select * from Apro where data between Inicio and Final';
> > Parameters[0].Value := Inicio;
> > Parameters[1].Value := Final;
> > Open;
> > end;
> Shervin, did you not for get the colons?
> Select * from Apro where data between :Inicio and :Final
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp