get result that do not have prices

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get result that do not have prices

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-04-18 : 15:08:03

Every featuretypeid can have a category. Some have prices and some don’T. RESULT:
Result this one do not have prices:
FeatureTypeID: 1 - ca_id: 2
FeatureTypeID: 1 - ca_id: 3
FeatureTypeID: 2 - ca_id: 1
FeatureTypeID: 2 - ca_id: 2
FeatureTypeID: 2 - ca_id: 3
FeatureTypeID: 3 - ca_id: 1
FeatureTypeID: 3 - ca_id: 3

So basically I want to get a result that do not have prices. I have a cursor that is getting all the one with prices…but I can’t get the one without prices. Could u help me on this one?

CREATE TABLE [dbo].[Price](
[PriceId] [int] IDENTITY(1,1) not NULL,
[r_id] [int] NULL,
[FeatureTypeId] [int] NULL,
[EffectiveDate] [datetime] NULL,
[Price] [money] NULL,
[LastUpdateUser] [varchar](20) NULL,
[LastUpdateDateTime] [datetime] NULL,
[ca_id] [int] NULL)

CREATE TABLE [dbo].[t_region](
[r_id] [int] NULL,
[r_name] [varchar](255) NULL,
[r_divisionId] [int] NULL,
[r_order] [int] NULL,
[r_hbname] [varchar](255) NULL,
[r_site] [varchar](50) NULL,
[r_provice] [varchar](50) NULL,
[r_proviceShort] [varchar](10) NULL,
[r_charge_pst] [money] NULL,
[r_charge_gst] [money] NULL,
[r_ctregionID] [varchar](10) NULL,
[r_ctregionAbr] [varchar](10) NULL,
[r_nameFR] [varchar](255) NULL,
[AccountingRegionId] [int] NULL,
[r_AVUS] [tinyint] NULL,
[Status] [tinyint] NULL,
[LastUpdateUser] [varchar](100) NULL,
[LastUpdateDateTime] [datetime] NULL)

CREATE TABLE [dbo].[FeatureType](
[FeatureTypeId] [int] NULL,
[ItemPhraseName] [varchar](30) NULL,
[AdSubType] [int] NULL,
[RunLength] [int] NULL,
[StartDayOfWeek] [tinyint] NULL,
[Active] [tinyint] NULL,
[OrdNum] [int] NULL,
[XMLNode] [varchar](50) NULL,
[PhraseHelpText] [varchar](30) NULL,
[ItemPhraseAbbr] [varchar](30) NULL)

CREATE TABLE [dbo].[t_Category](
[ca_id] [int] NULL,
[ca_Phrase] [varchar](30) NULL,
[ca_CategoryParentId] [int] NULL,
[ca_SubCategoryParentId] [int] NULL,
[ca_AllowChildSelection] [tinyint] NULL,
[ca_Sequence] [int] NULL,
[ca_SubTypePhraseList] [varchar](30) NULL,
[ca_LegacySubType] [tinyint] NULL,
[ca_OmitAttribute1Divider] [tinyint] NULL,
[ca_OmitAttribute2Divider] [tinyint] NULL,
[ca_BuySellWhereClause] [varchar](255) NULL,
[ca_SupportSimpleEntry] [tinyint] NULL,
[ca_IsPriceOptional] [tinyint] NULL,
[ca_ManageMakes] [tinyint] NULL,
[ca_MakeSystemCategoryId] [int] NULL,
[ca_MenuShowSubcategories] [tinyint] NULL,
[ca_MakeAdditionalCategories] [varchar](30) NULL,
[Status] [tinyint] NULL,
[LastUpdateUser] [varchar](100) NULL,
[LastUpdateDateTime] [datetime] NULL)

CREATE TABLE [dbo].[PhraseTranslation](
[PhraseTranslationId] [int] IDENTITY(1,1) not NULL,
[PhraseName] [varchar](30) NULL,
[LanguageCode] [char](2) NULL,
[CountryCode] [char](2) NULL,
[Translation] [nvarchar](4000) NULL)

truncate table price
insert price ( r_id, featuretypeid, ca_id, price)
select '30', 1, 1, 59 union all
--select '30', 1, 2, 50 union all
--select '30', 2, 2, 15 union alln
--select '30', 1, 2, 500 union all
--select '30', 1, 3, 99 union all
select '30', 3, 3, 11

truncate table t_category
insert t_category (ca_id, ca_Phrase, status )
select 1, 'Car', 1 union all
select 2, 'Truck', 1 union all
select 3, 'SUV', 1

truncate table FeatureType
insert FeatureType (FeatureTypeid, itemphrasename, active)
select 1, 'online', 1 union all
select 2, 'onlinePhoto', 1 union all
select 3, 'onlineHD', 1

truncate table PhraseTranslation
insert PhraseTranslation ( phrasename, translation)
select 'online', 'OnlineAD' union all
select 'onlinePhoto', 'onlinePhotoAD' union all
select 'onlineHD', 'onlineHDAD'

truncate table t_region
insert t_region (r_id, r_name, r_provice)
select 30, 'Quebec', 'QC'

set nocount on

DECLARE @REGION VARCHAR(50)
SET @REGION = 30 -- CHANGE VALUE

declare @demotable table ( REGION VARCHAR(100),
[FEATURETYPEID - UPSELL] VARCHAR(100),
[PHRASENAME] VARCHAR(100),
[TRANSLATION] VARCHAR(100),
CATEGORYID VARCHAR(100),
CATEGORY VARCHAR(100),
PRICE MONEY)

DECLARE @FeatureTypeID VARCHAR(10)
DECLARE @SQL VARCHAR(4000)

DECLARE products CURSOR FOR

SELECT FeatureTypeID from FeatureType

OPEN products
FETCH NEXT FROM products INTO @FeatureTypeID;

WHILE @@FETCH_STATUS = 0
BEGIN
-------------------------------------------------------------------------------------------------
DECLARE @ca_id INT

DECLARE product_cursor CURSOR FOR

select ca_id from t_Category

OPEN product_cursor;
FETCH NEXT FROM product_cursor INTO @ca_id;

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT @demotable
SELECT price.r_id [REGION],
price.FeatureTypeId [FEATURETYPEID - UPSELL],
phrasename [PHRASENAME],
translation [TRANSLATION],
price.ca_id [CATEGORY],
t_category.ca_Phrase [CATEGORYPHRASE] ,
price [PRICE]

FROM dbo.price with (READUNCOMMITTED)
JOIN dbo.t_category with (READUNCOMMITTED)
ON price.ca_id = t_category.ca_id
JOIN FeatureType
ON FeatureType.FeatureTypeId = price.FeatureTypeId
JOIN PhraseTranslation a
ON a.PhraseName = FeatureType.ItemPhraseName
--AND LanguageCode = 'en'
AND price.r_id = @REGION
AND price.FeatureTypeId = @FeatureTypeId
AND t_category.Status = 1
AND t_category.ca_id = @ca_id
AND FeatureType.Active=1

print 'FeatureTypeID: ' + cast(@FeatureTypeId as varchar(30)) + ' - ca_id: '+ cast(@ca_id as varchar(30))

FETCH NEXT FROM product_cursor INTO @ca_id;
END;

CLOSE product_cursor;
DEALLOCATE product_cursor;

-------------------------------------------------------------------------------------------------
FETCH NEXT FROM products INTO @FeatureTypeID
END

CLOSE products
DEALLOCATE products

SELECT * FROM @demotable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:03:05

What are columns by which tables are related?
also why do you use (READUNCOMMITTED) ?
do you mean purpose of that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-04-19 : 03:46:06

tomorrow i will paste all the tables with contraints and keys if that helps.
readuncommitted no i dont know...i thought it was like dirty read...i didnt put it in the cursor...i copied the query from somehwere and didnt delete it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 08:47:50

quote:
Originally posted by basicconfiguration

tomorrow i will paste all the tables with contraints and keys if that helps.
readuncommitted no i dont know...i thought it was like dirty read...i didnt put it in the cursor...i copied the query from somehwere and didnt delete it.

exactly
it will cause dirty reads...dont know why you want to include it...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-04-23 : 02:44:33

I solved this. I used nested cursors (3 cursors)

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-23 : 08:21:14

Why do you have to use Cursors??...There is an equivalent or better Set based approach than using Cursors. Using one Cursor hampers performance. Think about nesting three cursors.
Maybe it works fine now because the amount of data must be less. But, when the Database grows and the amount of data your procedure needs to handle increases, you will have a lot of performance issues.

Avoid RBAR and Cursors. Use only Set Based solutions.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 11:37:18

quote:
Originally posted by basicconfiguration

I solved this. I used nested cursors (3 cursors)

I definitely hope there would be much better of implementing your logic in a set based way.
But to suggest anything we would require some sample data long with output you're trying to achieve. If you can post it,someone will be able to help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mhorseman
Starting Member

44 Posts

Posted - 2012-04-24 : 04:25:18

If it helps, using the data you provided, the following seems to get the original list of prices without using a cursor: (I used temp tables for my testing rather than permanent ones - change the table names as appropriate)

SELECT p.r_id [REGION],
p.FeatureTypeId [FEATURETYPEID - UPSELL],
phrasename [PHRASENAME],
translation [TRANSLATION],
p.ca_id [CATEGORY],
c.ca_Phrase [CATEGORYPHRASE] ,
price [PRICE]

FROM #price p
JOIN #t_category c
ON p.ca_id = c.ca_id
JOIN #FeatureType f
ON F.FeatureTypeId = p.FeatureTypeId
JOIN #PhraseTranslation a
ON a.PhraseName = F.ItemPhraseName

join #t_region r on p.r_id = r.r_id
where p.r_id = @REGION
AND c.Status = 1
AND F.Active = 1

As for the opposite list (those without prices), I'm still thinking about it.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources