HELLO FRIEND,
I have accounting app and i have some problem in crystal report formula to get statement of account
for example bank statement .
Dr Cr Bal
0 100 100
0 150 350
50 0 300
20 0 280
0 140 420
PLEASE IF U KNOW ANY FORMULA TO SOLVE THIS PROBLE SEN IT
THANKStry this formula in detail section
whileprintingrecords;
numbervar a;
if dr then
a=a+dr
else
a=a-cr
if u need to reset this formula to zero , make another formula and use it in group section
whileprintingrecords;
numbervar a;
a:=0;
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
please help fix this statement
im using an access project file now and no longer a mdb file.
i used to prompt a user for the first and last name of the patient they were looking for using this statement like [forms]![main]![text0] & "*"
and like [forms]![main]![text2] & "*" then it would promt them to enter in a last name and a first name but if they only entered a last name and left the first name prompt empty it would give them all the last name they had entered .
now im on sql as my backend and i got my query working except
if i dont type anything in on the second prompt i get no records.
how can i fix this because the user doesnt always no the first name and would like to sort through the records by just the last.
my current statement that works as long as you fill in both prompts is a follows.
SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE @.LastName + '%') AND (fname LIKE @.firstname + '%')
ORDER BY chartnumberSELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber|||Originally posted by Enigma
SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber
thank you very much worked great , im just having a hard time with the new syntax at least the vb codes i wrote still work.sql
i used to prompt a user for the first and last name of the patient they were looking for using this statement like [forms]![main]![text0] & "*"
and like [forms]![main]![text2] & "*" then it would promt them to enter in a last name and a first name but if they only entered a last name and left the first name prompt empty it would give them all the last name they had entered .
now im on sql as my backend and i got my query working except
if i dont type anything in on the second prompt i get no records.
how can i fix this because the user doesnt always no the first name and would like to sort through the records by just the last.
my current statement that works as long as you fill in both prompts is a follows.
SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE @.LastName + '%') AND (fname LIKE @.firstname + '%')
ORDER BY chartnumberSELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber|||Originally posted by Enigma
SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber
thank you very much worked great , im just having a hard time with the new syntax at least the vb codes i wrote still work.sql
Please Help Beginners
The followong statement is working
iif ([Measures].[Docsum]>500, "1","0")
But there is a need to count these Docsums
so
iif ([Measures].[Docsum]>500, Count[Docsum],"0")
but not this way
Can somebody help and explains, how to Count these sums
Thank'scan the count or sum wrap around the iif-statement?
iif ([Measures].[Docsum]>500, "1","0")
But there is a need to count these Docsums
so
iif ([Measures].[Docsum]>500, Count[Docsum],"0")
but not this way
Can somebody help and explains, how to Count these sums
Thank'scan the count or sum wrap around the iif-statement?
Friday, March 23, 2012
Please explain this SQL Trigger
I am new to triggers.
I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @.counter = 1
When does this counter change value?
USE Northwind
GO
CREATE TRIGGER PriceCheck
ON [Order Details]
FOR INSERT
AS
DECLARE @.counter int
SET @.counter= @.@.ROWCOUNT
DECLARE @.unitprice money
DECLARE order_details_insert_cursor CURSOR FOR
SELECT Unitprice
FROM inserted
IF @.counter = 1
BEGIN
IF(SELECT UnitPrice FROM inserted) > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
OPEN order_details_insert_cursor
FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.unitprice > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
RETURN
--Exit trigger immediately
END
FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
END
CLOSE order_details_insert_cursor
END
DEALLOCATE order_details_insert_cursor
The select statement checks to see what the inserted value is, and ifit's greater than 300, its rolling back the attempted insert as beingoutside of the range of allowed values. As an aside, this isn'treally a good use of a trigger. A check constraint would be justas good, and would perform better.
The counter is set to the number of rows being inserted, so it'll beequal to the number of rows in the set being inserted into thetable. Theoretically, it could have a different value for everyattempted insert.
|||Thanks - I am trying to follow the logic of it and wondered if it works from top to bottom how does it come back to evaluate the counter? I can't see a looping structure in there.
Also could you explain what is meant by 'inserted' as in SELECT Unitprice
FROM inserted
Is 'inserted' a reserved word. Usually I would expect to see a table name there.|||The counter is set when the trigger is run. It isn't changedduring the execution of the of the trigger, so the value that isinitally set is the only value that it'll have until the next time thatthe trigger is fired.
"inserted" is a table, as you would expect. "inserted" and"deleted" are the names of two special internal tables that SQL Serveruses specifically to expose data being changed by INSERT, UPDATE, andDELETE statements to triggers.
I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @.counter = 1
When does this counter change value?
USE Northwind
GO
CREATE TRIGGER PriceCheck
ON [Order Details]
FOR INSERT
AS
DECLARE @.counter int
SET @.counter= @.@.ROWCOUNT
DECLARE @.unitprice money
DECLARE order_details_insert_cursor CURSOR FOR
SELECT Unitprice
FROM inserted
IF @.counter = 1
BEGIN
IF(SELECT UnitPrice FROM inserted) > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
OPEN order_details_insert_cursor
FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.unitprice > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
RETURN
--Exit trigger immediately
END
FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
END
CLOSE order_details_insert_cursor
END
DEALLOCATE order_details_insert_cursor
The select statement checks to see what the inserted value is, and ifit's greater than 300, its rolling back the attempted insert as beingoutside of the range of allowed values. As an aside, this isn'treally a good use of a trigger. A check constraint would be justas good, and would perform better.
The counter is set to the number of rows being inserted, so it'll beequal to the number of rows in the set being inserted into thetable. Theoretically, it could have a different value for everyattempted insert.
|||Thanks - I am trying to follow the logic of it and wondered if it works from top to bottom how does it come back to evaluate the counter? I can't see a looping structure in there.
Also could you explain what is meant by 'inserted' as in SELECT Unitprice
FROM inserted
Is 'inserted' a reserved word. Usually I would expect to see a table name there.|||The counter is set when the trigger is run. It isn't changedduring the execution of the of the trigger, so the value that isinitally set is the only value that it'll have until the next time thatthe trigger is fired.
"inserted" is a table, as you would expect. "inserted" and"deleted" are the names of two special internal tables that SQL Serveruses specifically to expose data being changed by INSERT, UPDATE, andDELETE statements to triggers.
Wednesday, March 21, 2012
Please assist with a query...
I have a table that can contain several entries for a given item id.
How would my select statement look if I wanted to query for:
- only the 'last' entry for 'each' id that meets certain criteria
I don't want a TOP x query, because I want the dataset to contain all
items whose 'last' entry meets certain criteria.Can you post your DLL? What is/are the key(s)? How can you tell which record
is last one?
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125423719.220718.119790@.g47g2000cwa.googlegroups.com...
>I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||What criteria can we use to spot the last row for each group?
Example:
use northwind
go
-- last orders for each customer with an order
select
customerid, orderid
from
dbo.orders as a
where
orderdate = (select max(orderdate) from dbo.orders as b where
b.customerid = a.customerid)
go
AMB
"indee" wrote:
> I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||The primary key is an autonumber, however, there is an itemid field.
So, for example, here's what I want in plain english:
For each distinct item id, show me all whose last one entry (per the
autonumber) has status <> 1
So, I want the query to return all item ids whose last entry does not
have the status field set to 1.|||Can you try this (assume your autonumber is an Identity column):
select (whatever columns you have)
from YourTable T1
where T1.autonumber = (select max(T2.autonumber)
from YourTable T2
where T2.itemid = T1.itemid)
and T1.Status <> 1
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125429028.896197.68730@.g14g2000cwa.googlegroups.com...
> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||Try,
select
*
from
t1 as a
where
status != 1
and autonumber = (select max(b.autonumber) from t1 as b where b.itemid =
a.itemid)
AMB
"indee" wrote:
> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||We're almost there, however, the query can return only the itemid
field, as it is a subquery...I really appreciate your help...
Here is the full query:
SELECT ID FROM vwAssets WHERE ID NOT IN(SELECT AssetID FROM
vwAssetValuation WHERE ActionEID IN (211, 212, 215) AND Stamp <= '" &
mCommon.FormatSQLDate(AsOf) & "') AND AssetID NOT IN(** QUERY GOES HERE
**)|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
THIS IS THE BASICS!! Have you ever bothered to do ANY studying
first?|||>> The primary key is an autonumber <<
Again, an autonumber cannot be a relationalo key BY DEFINITION. And
you still do not know that a field and columns are TOTALLY different
concepts.|||I see that you also use prefixes and data element names that violate
ISO-11179 data element rules. Does "vw-" mean Volkswagen?
Please stop programming until you learn the foundations. You are
dangerously ingnorant. Also you are going to get in Newsgroup is
kludges that will let you limp along until you have a disaster. Please
post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
How would my select statement look if I wanted to query for:
- only the 'last' entry for 'each' id that meets certain criteria
I don't want a TOP x query, because I want the dataset to contain all
items whose 'last' entry meets certain criteria.Can you post your DLL? What is/are the key(s)? How can you tell which record
is last one?
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125423719.220718.119790@.g47g2000cwa.googlegroups.com...
>I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||What criteria can we use to spot the last row for each group?
Example:
use northwind
go
-- last orders for each customer with an order
select
customerid, orderid
from
dbo.orders as a
where
orderdate = (select max(orderdate) from dbo.orders as b where
b.customerid = a.customerid)
go
AMB
"indee" wrote:
> I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||The primary key is an autonumber, however, there is an itemid field.
So, for example, here's what I want in plain english:
For each distinct item id, show me all whose last one entry (per the
autonumber) has status <> 1
So, I want the query to return all item ids whose last entry does not
have the status field set to 1.|||Can you try this (assume your autonumber is an Identity column):
select (whatever columns you have)
from YourTable T1
where T1.autonumber = (select max(T2.autonumber)
from YourTable T2
where T2.itemid = T1.itemid)
and T1.Status <> 1
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125429028.896197.68730@.g14g2000cwa.googlegroups.com...
> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||Try,
select
*
from
t1 as a
where
status != 1
and autonumber = (select max(b.autonumber) from t1 as b where b.itemid =
a.itemid)
AMB
"indee" wrote:
> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||We're almost there, however, the query can return only the itemid
field, as it is a subquery...I really appreciate your help...
Here is the full query:
SELECT ID FROM vwAssets WHERE ID NOT IN(SELECT AssetID FROM
vwAssetValuation WHERE ActionEID IN (211, 212, 215) AND Stamp <= '" &
mCommon.FormatSQLDate(AsOf) & "') AND AssetID NOT IN(** QUERY GOES HERE
**)|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
THIS IS THE BASICS!! Have you ever bothered to do ANY studying
first?|||>> The primary key is an autonumber <<
Again, an autonumber cannot be a relationalo key BY DEFINITION. And
you still do not know that a field and columns are TOTALLY different
concepts.|||I see that you also use prefixes and data element names that violate
ISO-11179 data element rules. Does "vw-" mean Volkswagen?
Please stop programming until you learn the foundations. You are
dangerously ingnorant. Also you are going to get in Newsgroup is
kludges that will let you limp along until you have a disaster. Please
post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
Pleas help with simple sql statement question
Hello and thanks for looking! I have this sql stament:
Dim strInsertCommand As String = "INSERT INTO CurrentDrawVar (Username, GamesBought, DateLastBuy) VALUES ('" & UserID & "', '" & gamestobuy & "', '" & Now & "')"
It works great and inserts into a database like it should. My question is where can I insert a WHERE stament into it so that I can insert this data over information that already exists in the database. FOr instance. I have a user grogo21 and in this row I want to insert gamestobuy and now. Where can i put this and is the stament below right:
WHERE Username = 'grogo21'
I tried putting it after the parenthases after the values but it doesnt work. Thanks alot!!
Hi, you need an 'UPDATE' rather than 'INERT' in this case. Firstly you should have columns to host the information you want to add to the row (gamestobuy and now), and then you can update the row using 'WHERE' clause, assigning values to the fields:
"UPDATE CurrentDrawVar SET GamesToBuy='" & gamestobuy & "', Now='" & Now & "'
WHERE UserID='"&Username&"'"
Tuesday, March 20, 2012
Placing Notes In Sql Statement
Anyone know how to place not functional "NOTES" in an SQL statements like the authors name or to note functionality?? thanks GRTTry using --
:D|||thanks works great!|||Just as an fyi, you can highlight entire lines in Query Analyzer and use these:
CTRL+SHIFT+C --Comments everything.
CTRL+SHIFT+R --Removes comments from everything.
This is great when you are troubleshooting or want to just type then highlight and comment.|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
and you guys who give a database-specific answer to a generic question should preface your remarks that way
for example, if you're using microsoft access, you can't put comments in the sql at all
if you're using microsoft sql server, the double dash works, and so does /* comment */
guess which database allows //|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
That's right. Repeat after me: "Microsoft SQL Server and SQL are not the same thing"...|||-- supposed to be ansi standard.
:rolleyes:|||PL SQL developer oracle version 9i
:D|||thanks works great!|||Just as an fyi, you can highlight entire lines in Query Analyzer and use these:
CTRL+SHIFT+C --Comments everything.
CTRL+SHIFT+R --Removes comments from everything.
This is great when you are troubleshooting or want to just type then highlight and comment.|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
and you guys who give a database-specific answer to a generic question should preface your remarks that way
for example, if you're using microsoft access, you can't put comments in the sql at all
if you're using microsoft sql server, the double dash works, and so does /* comment */
guess which database allows //|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
That's right. Repeat after me: "Microsoft SQL Server and SQL are not the same thing"...|||-- supposed to be ansi standard.
:rolleyes:|||PL SQL developer oracle version 9i
Monday, March 12, 2012
placeholder for uniqueidentifier
Hi,
I need help with a SQL SELECT statement. I am using a UNION to merge four
tables together. For the tables that did not include a particular field, I
have used a placeholder of 'N/A' in the SELECT statement as follows:
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_lot_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_subdivision_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
current_retail_price, current_expiration_date, plan_availability_pricing_id,
plan_id
FROM hb_view_business_unit_plan
UNION
SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A',
'N/A', 'N/A', plan_number, plan_name, current_retail_price,
current_expiration_date, plan_availability_pricing_id, plan_id
FROM hb_view_organization_plan
I am having a problem with using the 'N/A' placeholder in lieu of a
uniqueidentifier. It works for varchar field types but apparently not
uniqueidentifier type fields. I receive the error message "error converting
from character string into uniqueidentifier". Thanks for your help.
DonYou could cast the top uniqueidentifier to a varchar(36) and it will work.
It seems a bit wierd though, because are you going to display the guid to
the user? Pretty hideous if you are.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
<dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
> I have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A',
> 'N/A', 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error
> converting from character string into uniqueidentifier". Thanks for your
> help.
>
> Don
>
>|||When you use union, first query defines column data types for all other
queries.
So, the problem is (probably, you did not provide DDl, so I cannot tell for
sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id which
can be of GUID data type.
Solution can be to replace 'N/A' with "empty" guid
'{00000000-0000-0000-0000-000000000000}' or with null.
Regards,
Marko Simic
"dbj" wrote:
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Sorry I forgot to write the most important part :)
SQL try to convert 'N/A' to uniqueidentifier data type which is not
possible. you may try this to see what will happen:
select newid()
union
select 'N/A'
Regards,
Marko Simic
"dbj" wrote:
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Oops, no it won't (d'oh!) you will have to cast them all to char:
select cast( 'na' as varchar(36))
union
select cast(newId() as varchar(36))
Sorry,
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:u%238on1uPFHA.532@.TK2MSFTNGP09.phx.gbl...
> You could cast the top uniqueidentifier to a varchar(36) and it will work.
> It seems a bit wierd though, because are you going to display the guid to
> the user? Pretty hideous if you are.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> <dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
>|||Marko,
Thank you very much. Your answer worked on the first try.
Don
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:CA618990-5DFD-4753-B3CC-13FA859F136C@.microsoft.com...
> When you use union, first query defines column data types for all other
> queries.
> So, the problem is (probably, you did not provide DDl, so I cannot tell
> for
> sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id
> which
> can be of GUID data type.
> Solution can be to replace 'N/A' with "empty" guid
> '{00000000-0000-0000-0000-000000000000}' or with null.
> Regards,
> Marko Simic
> "dbj" wrote:
>
I need help with a SQL SELECT statement. I am using a UNION to merge four
tables together. For the tables that did not include a particular field, I
have used a placeholder of 'N/A' in the SELECT statement as follows:
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_lot_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_subdivision_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
current_retail_price, current_expiration_date, plan_availability_pricing_id,
plan_id
FROM hb_view_business_unit_plan
UNION
SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A',
'N/A', 'N/A', plan_number, plan_name, current_retail_price,
current_expiration_date, plan_availability_pricing_id, plan_id
FROM hb_view_organization_plan
I am having a problem with using the 'N/A' placeholder in lieu of a
uniqueidentifier. It works for varchar field types but apparently not
uniqueidentifier type fields. I receive the error message "error converting
from character string into uniqueidentifier". Thanks for your help.
DonYou could cast the top uniqueidentifier to a varchar(36) and it will work.
It seems a bit wierd though, because are you going to display the guid to
the user? Pretty hideous if you are.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
<dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
> I have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A',
> 'N/A', 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error
> converting from character string into uniqueidentifier". Thanks for your
> help.
>
> Don
>
>|||When you use union, first query defines column data types for all other
queries.
So, the problem is (probably, you did not provide DDl, so I cannot tell for
sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id which
can be of GUID data type.
Solution can be to replace 'N/A' with "empty" guid
'{00000000-0000-0000-0000-000000000000}' or with null.
Regards,
Marko Simic
"dbj" wrote:
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Sorry I forgot to write the most important part :)
SQL try to convert 'N/A' to uniqueidentifier data type which is not
possible. you may try this to see what will happen:
select newid()
union
select 'N/A'
Regards,
Marko Simic
"dbj" wrote:
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Oops, no it won't (d'oh!) you will have to cast them all to char:
select cast( 'na' as varchar(36))
union
select cast(newId() as varchar(36))
Sorry,
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:u%238on1uPFHA.532@.TK2MSFTNGP09.phx.gbl...
> You could cast the top uniqueidentifier to a varchar(36) and it will work.
> It seems a bit wierd though, because are you going to display the guid to
> the user? Pretty hideous if you are.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> <dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
>|||Marko,
Thank you very much. Your answer worked on the first try.
Don
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:CA618990-5DFD-4753-B3CC-13FA859F136C@.microsoft.com...
> When you use union, first query defines column data types for all other
> queries.
> So, the problem is (probably, you did not provide DDl, so I cannot tell
> for
> sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id
> which
> can be of GUID data type.
> Solution can be to replace 'N/A' with "empty" guid
> '{00000000-0000-0000-0000-000000000000}' or with null.
> Regards,
> Marko Simic
> "dbj" wrote:
>
Wednesday, March 7, 2012
PK And Index
I have a primary key that comprises 2 columns (lets say ReportDate and
Symbol).
Symbol).
I know that if I submit a statement like SELECT * FROM T1 WHERE
ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
Do I need to create another index on symbol alone?Jason (JayCallas@.hotmail.com) writes:
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?
For best performance, yes.
But the query may use the existing index, if the index is non-clustered.
If SQL Server finds that XYZ is not a very common value, it may opt
scan the index to find the rows. This is faster than scanning the entire
table. If the value is common, however, the bookmark lookups will be
more expensive than scanning.
If the existing index is clustered, it can not help to speed up the
retrieval. Ah, that wasn't completely true, either. Because if the
there is a non-clustered index on the table as well, the keys of the
clustered index appears in the non-clustered index, so SQL Server can
scan that index.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erlands response. You could check the Execution Plan when using
Query Analyzer to see how SQL Server is using your indexes.
BZ
"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0312190912.1c1ea341@.posting.google.c om...
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?
Saturday, February 25, 2012
PIVOT statement whitout knowing values
Just a small issue...
I'm trying the new SQL 2005 (Express) because the PIVOT function was finally added.
I've a table with three columns ID, Height and Width
Now I'd like to have a table with for each height the number of ID for each Width
The easiest way is to use the PIVOT statement.....but..... to use it in SQL2005 I should use:
SELECT Height, [100] AS Width01, [200] AS Width02
FROM (
SELECT ID, Height, Width FROM TestTable) p
PIVOT ( COUNT (ID) FOR Width IN([100], [200]) ) AS pvt
This kind of querry works perfectly in a static situation, but if I add new record in the table referencing the "300" Width to obtain the correct result I have to modify the query.
Is there an options or a technique for having the list of the Width dinamically filled according the table contents.
Thank you very much to anyone how can help me
H
You have to use dynamic SQL to execute the SELECT statement after generating the values for the IN list. There is no other way using static SQL code.|||To be clear, there are good reasons for this restriction.
SQL Server's PIVOT can exist anywhere in the query tree (unlike in Access), supports UNPIVOT (unlike Access), and does not require recompilation for each execution (unlike Access). These are good things for complex queries, as compilation time would be significantly worse if these did not exist.
SQL Server's query optimizer has a requirement that the column list be known before compilation begins. This allows faster compiles because we can identify duplicate alternatives more easily and avoid doing extra work during compilation. This also helps us to determine if we can avoid searching portions of the possible plan space that obviously will not help find a faster plan than what has been found so far during optimization.
I understand the desire to not have to bother specifying a column list, and perhaps that is something we can add in a future release. The reasons above are reasons it was not added in SQL 2005. Even if such a feature were added, it would be likely better if you could specify a column list to speed system throughput.
Conor Cunningham
SQL Server Query Optimization Development Lead
|||Thank you all for the clear answer, now I understood that the restriction is due to performances.
Of course this type of restriction have very few impact over small databases like the ones I working on (~100 MB). So I will keep my application over access where the power of the TRANSFORM-PIVOT scheme will help me reducing the programming effort.
Thanks again
H
Pivot SQL Statement in SQL SERVER 2000
I have a table temp1 with the following data:
a b c d
10 11 888 991
10 11 888 992
How do I pivot this such that the output be
10 11 888 991 992
Thanks
harishWith the extremely limited information you've provided,
SELECT a,b,c,MIN(d),MAX(d)
FROM temp1
GROUP BY a,b,c
If you want a more complete answer, please provide a more complete question.
http://www.aspfaq.com/5006
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131137736.316980.221230@.g49g2000cwa.googlegroups.com...
>I have a table temp1 with the following data:
> a b c d
> 10 11 888 991
> 10 11 888 992
> How do I pivot this such that the output be
> 10 11 888 991 992
> Thanks
> harish
>|||Hey Thanks
But if i have more than 2 rows for one key (a,b,c) the this will not
work.
In that case how do I do it?|||> But if i have more than 2 rows for one key (a,b,c) the this will not
> work.
See how helpful more information would have been?
> In that case how do I do it?
http://www.aspfaq.com/2462
Again, if you want a more useful answer, provide a more detailed question.
See the link I posted in my previous response.|||Hey thanks a lot
that was helpful|||harish (harish.prabhala@.gmail.com) writes:
> But if i have more than 2 rows for one key (a,b,c) the this will not
> work.
> In that case how do I do it?
Books Online has a section on it:
Accessing and Changing Relational Data
Advanced Query Concepts
Transact-SQL Tips
Cross-Tab Reports
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The more things change the more they remain then same:)
Check out THE Rac utility @.
www.rac4sql.net
a b c d
10 11 888 991
10 11 888 992
How do I pivot this such that the output be
10 11 888 991 992
Thanks
harishWith the extremely limited information you've provided,
SELECT a,b,c,MIN(d),MAX(d)
FROM temp1
GROUP BY a,b,c
If you want a more complete answer, please provide a more complete question.
http://www.aspfaq.com/5006
"harish" <harish.prabhala@.gmail.com> wrote in message
news:1131137736.316980.221230@.g49g2000cwa.googlegroups.com...
>I have a table temp1 with the following data:
> a b c d
> 10 11 888 991
> 10 11 888 992
> How do I pivot this such that the output be
> 10 11 888 991 992
> Thanks
> harish
>|||Hey Thanks
But if i have more than 2 rows for one key (a,b,c) the this will not
work.
In that case how do I do it?|||> But if i have more than 2 rows for one key (a,b,c) the this will not
> work.
See how helpful more information would have been?
> In that case how do I do it?
http://www.aspfaq.com/2462
Again, if you want a more useful answer, provide a more detailed question.
See the link I posted in my previous response.|||Hey thanks a lot
that was helpful|||harish (harish.prabhala@.gmail.com) writes:
> But if i have more than 2 rows for one key (a,b,c) the this will not
> work.
> In that case how do I do it?
Books Online has a section on it:
Accessing and Changing Relational Data
Advanced Query Concepts
Transact-SQL Tips
Cross-Tab Reports
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The more things change the more they remain then same:)
Check out THE Rac utility @.
www.rac4sql.net
Monday, February 20, 2012
Pivot misunderstood ?
Hello, i've been looking for some samples on the pivot function, but somewhere i'm making a mistake and can't find where.
This is my statement
SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, 
AS FRIDAY, [7] AS SATURDAY
FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK
PIVOT (SUM(TICKETS)
FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT
ORDER BY MOV_UI
I though to receive a line per mov_ui with the 7 sums, but .. i'm getting a line for each day ....
MOV_UI SUNDAY MONDAY THUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
29 NULL NULL NULL 73 NULL NULL NULL
29 NULL NULL NULL NULL 72 NULL NULL
29 NULL NULL NULL NULL NULL 129 NULL
29 NULL NULL NULL NULL NULL NULL 138
29 104 NULL NULL NULL NULL NULL NULL
29 NULL 68 NULL NULL NULL NULL NULL
29 NULL NULL 58 NULL NULL NULL NULL
What am i missing .... ?
Hmm, this works for me:
CREATE TABLE test
(
movie_ui int,
dayOfWeek int,
tickets int
)
INSERT INTO test
SELECT 29,1,20
UNION ALL
SELECT 29,2,38
UNION ALL
SELECT 30,1,20
UNION ALL
SELECT 30,2,44
GO
SELECT movie_ui, [1] AS SUNDAY, [2] AS MONDAY
FROM test PIVOT (SUM(TICKETS) FOR DAYOFWEEK IN ([1], [2])) AS PVT
ORDER BY movie_ui
movie_ui SUNDAY MONDAY
-- -- --
29 20 38
30 20 44
Thx for the answer, i've found the problem ...
this was my syntax
SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY
FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK
PIVOT (SUM(TICKETS)
FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT
ORDER BY MOV_UI
Problem was that from the view xx.ugent.. there where other columns returned that where not used in the pivot, but they cause this effect, so adding them in the select mov_ui, ... and the order by helpen the problem, or chaning the 'from xx.ugent' to a
from (select mov_ui, dayofweek, tickets from xx_ugent_dailysales_of_movie_first_Week) p' solved the problem too.
Kind Regards
Pivot fields into a single column
I have a row that looks like this
Year 2006 2007 2008 2009.....etc.
I want a select statement that will get me this
Year
2006
2007
2008
2009
etc.
Try:
Code Snippet
createtable #t (
c1 intnotnull,
c2 intnotnull,
c3 intnotnull,
c4 intnotnull
)
insertinto #t values(2006, 2007, 2008, 2009)
select
case t2.c1
when 1 then t1.c1
when 2 then t1.c2
when 3 then t1.c3
when 4 then t1.c4
endas [Year]
from
#t as t1
crossjoin
(select 1 as [c1] union allselect 2 union allselect 3 union allselect 4)as t2
-- 2005
select
[Year]
from
(select c1, c2, c3, c4from #t)as p
unpivot
([Year] for [c] in(c1, c2, c3, c4))as unpvt
droptable #t
AMB
|||Thanks, but I can't use create table or insert. Appreciate the effort though.|||
WVUProgramer wrote:
Thanks, but I can't use create table or insert. Appreciate the effort though.
You don't need to - he used the create table and insert to generate sample data so that his entire code example would run in its entirety.
You just need to use the select part of it (possibly modified to meet your exact requirements)|||Okay, thanks.|||
For the first select, it says there's no from clause in the select statement following the cross join.
In the second one, it says unable to parse text.
I plugged in my field names (which are f1 through f4) and my table name where the t# is
Perhaps i'm confused about the 
and the [Year].
If you copy and paste the script, then execute it, you will see the result is the same you are expecting in your original post.
Can you post the statement you are trying to execute?
AMB
|||select
case t2.F1
when 1 then t1.F1
when 2 then t1.F2
when 3 then t1.F3
when 4 then t1.F4
end as [Year]
from
table1 as t1
cross join
(select 1 as [F1] union all select 2 union all select 3 union all select 4) as t2
My table name is table1 my fields are F1, F2, F3, F4
Error Message: Error in SELECT clause: expression near 'UNION'.
Missing FROM clause.
Unable to parse query text.
Where are you testing the script: (which application)?
AMB
Subscribe to:
Posts (Atom)