|
Thank very much, i knew the one that you mentioned, but this way is not what i want, coz it is not feasible for me since i have a very long select statement.
|
|
|
|
|
It look to me that your database is a total mess. Why don't you have separate columns for region and date in one and the same table.
Then you could make the query as simple as
Select * from Comersus where sInvoice = 'B_IN000051' and Country = 'UK' and Date = '28Jul2010'
where Date naturally shouldn't be a string but an actual Date
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Mate, first of all, thanks for your reply, but if i can go the way that you mentioned, i wouldn't jump on internet, coz be realistic, a perfect database is a db that has no table. Hope you got my drift.
|
|
|
|
|
Well, I am curious why you need to have your database modeled that way.
The best reason I can think of at the moment is: "It's always been like that and now it's to much work to change"
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
No matter how you work it you are not going to get what you want.
Select * from @TableName
The sames as this will not work
Select @ColumnName from Table
You are going to have to construct a string and execute it, it is called dynamic SQL and this is one of the few reason it is a valid solution.
BTW
I agree that your db looks ugly (I presume you are aware of that). Have you looked into partitioning, as I assume you have split the orders by country/date for performance reasons.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks All for your help, and i will find an alternative way to do it then.
|
|
|
|
|
hello
you can use the exec function.
like this:
exec('select * from ' + @TableName)
that execute a query and return the result set
good luck
|
|
|
|
|
your alternatives are limited
1. create seperate SQL queries for all possibilities
2. Create an external application to overcome it
3. use dynamic SQL, Erlands Dynamic SQL article
others might chip in other alternatives for you
As barmey as a sack of badgers
|
|
|
|
|
You will have to use dynamic queries or a series of if/else blocks to do this. Dynamic queries are not a good way to do things. If/else, will look ugly if you have even 10 of them. So, either live with dynamic queries or redesign things.
|
|
|
|
|
Please look into below MDX query it is resulting in system.outofmemoryexception when i try to run in MDX query analyzer
Details
MRCODE has 1797 rows
DIV has 6 rows
STKCD has 4434 rows
Product Name has 507 rows
when crossjoin this .....it is giving problems..
Please let me know in case any modifications on it i cannot try NONEMPTYCROSSJOIN on it
SELECT ALL ( Hierarchize(
CrossJoin([Dim SM].[MRCODE].[MRCODE].members,
CrossJoin([Dim SM].[DIV].[DIV].members,
CrossJoin(
[Dim SM].[STKCD].[STKCD].members,
[D Im Prod].[Product Name].members
)))))
ON ROWS,[Measures].[QTYCHRG]ON COLUMNS from [ABCDW2]
thanks in advance
|
|
|
|
|
Hi All,
I'm trying to create a table that has one primary key with multiple foreign keys which reference different tables. The following is how I declared my table:
CREATE TABLE COURSE_SECTION
(
Csecid NUMBER(8),
Secnum NUMBER(2) NOT NULL,
Maxenrl NUMBER(4) NOT NULL,
Currenrl NUMBER(4) NOT NULL,
Cid NUMBER(6) NOT NULL,
Termid NUMBER(5) NOT NULL,
Day VARCHAR2(10),
Locid NUMBER(5),
Fid NUMBER(4),
primary Key (Csecid),
foreign Key (Cid) references COURSE(Cid),
foreign Key (Termid) references TERM(Termid),
foreign Key (Locid) references LOCATION(Locid),
foreign Key (Fid) references FACULTY(Fid)
);
I've receive the error message "unknown command beginning ........ - rest of line ignored" for many of the lines when I execute the above script in Oracle SQL Plus. I have tried many different things like rewording the lines or capitalize the letters of the attributes but my attempts were futile. Can you please point out what I have done wrong? Thank you in advance for your help.
|
|
|
|
|
Hi,
How can I create a foreign key column which is referencing the same tables another column. And if I have already created the table then how can I alter the table to enforce the same.
Any link or any sort of help will be very much usefull.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
|
I got the answer just create in normal way how we create table in SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Exactly. A very handy technique.
|
|
|
|
|
Learn something new every day.
|
|
|
|
|
when we use OPENXML then we have to mention table structure like below one.
DECLARE @xml_text VARCHAR(4000), @i INT
/* put xml structure here */
EXEC sp_xml_preparedocument @i OUTPUT, @xml_text
SELECT au_id AS author_id,
au_lname AS last_name,
au_fname AS first_name,
a.title_id,
title,
royaltyper AS royalty
FROM
OPENXML(@i, '/root/authors/titles', 1)
WITH (
au_id VARCHAR(11),
au_lname VARCHAR(20) '../@au_lname',
au_fname VARCHAR(30) '../@au_fname',
title_id VARCHAR(15),
royaltyper INT) a
INNER JOIN titles b ON a.title_id = b.title_id
WITH (
au_id VARCHAR(11),
au_lname VARCHAR(20) '../@au_lname',
au_fname VARCHAR(30) '../@au_fname',
title_id VARCHAR(15),
royaltyper INT
)
suppose if i dont want to mention the table structure manually or hard coded rather dynamically fetch table structure,datatype and length then how could i proceed.
please help me with sample code.
thanks in advance
tbhattacharjee
|
|
|
|
|
Hallo,
I have a database copy of a live database for development. On the live database is running a replication, but in the dev database it is not. However there are replication contraints and trigger in the dev database, and I can`t, for example, add columns to tables.
How can I delete all this replication artefacts?
Thank you for your help!
|
|
|
|
|
|
I have select query and I am getting results like
2
4
6
If I Pass 6 as Parameter then i need to get previous number of 6 i,e 4. If 9 then it has to give 0.
How can I write the query for this in Sql Server 2005
|
|
|
|
|
DECLARE
@ID INT
SET @ID = 4
SELECT TOP 1 ID
FROM tablename
WHERE ID < @ID
ORDER BY ID DESC
Test the result for null and replace with 0
OR
Put the results into a variable in the procedure and test the variable and return the results.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello there...
I have a query which spends a lot of time calculating my CASE WHEN -statements.
There are around 16 CASE WHEN statements, which takes a huge time to calculate.
Is there any way to rewrite query?
My query looks like this
SELECT DISTINCT Product_Master.Product_Kid, Product_Master.Product_Name, convert nvarchar,Product_Master.Product_RegDate,103) as Product_RegDate, Product_Master.Product_RegDate as RegDate,
CASE WHEN (Product_Master.Product_Code = '' OR Product_Master.Product_Code IS NULL) THEN '-' ELSE Product_Master.Product_Code END AS Product_Code,
CASE WHEN (Product_Master.Product_ModelNo = '' OR Product_Master.Product_ModelNo IS NULL) THEN '-' ELSE Product_Master.Product_ModelNo END AS Product_ModelNo,
CASE WHEN (Product_Master.Product_Image = '' OR Product_Master.Product_Image IS NULL) THEN 'N' ELSE 'Y' END AS Product_Image,
CASE WHEN (Product_Master.Product_BrandId = '' OR Product_Master.Product_BrandId IS NULL) THEN 'N' ELSE 'Y' END AS Brand_Name FROM Product_Master ORDER BY RegDate DESC
|
|
|
|
|
case when (isnull(Product_Master.Product_Code, '') = '' then '-' else Product_Master.Product_Code end as Product_Code
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Personally, I find it hard to believe that the CASE statements are what's taking time since they don't look complex. How do you know that the CASE statements are what take the most time? Have you tried the same query without some of the CASE statements, just returning the actual column data instead, and comparing the performance? Is the DISTINCT required? Is RegDate indexed?
Scott
|
|
|
|
|
Thanks for reply Scott,
Yes, I have tried the same query without CASE WHEN statements, and it works faster comperatively.
Moreover, The query is not this much only, there are many more CASE WHEN and Left Joins(which are necessary).
Left Joins are essential for the result, so I can't move it. The only options is to find alternate for CASE WHEN statements.
Prachi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
